As it relates to the following question:
SQL query failing because of commented part of T-SQL query
I posted an answer (the only answer) and worked with the OP over several days on narrowing down the issue and coming up with a work-around, and my answer was accepted. Then, the OP, in wanting an...
'The oid column are avaliable in all tables' - well, that's not true unless you create your tables with WITH OIDS. At least not true in recent (last 15 years or so ;) versions. Also, the OP has all these options in there command already, so what does your answer add that they don't already know? — dezso2 mins ago
@MikaelEriksson It's a rather complicated set of data with hundreds of classes but usually only a few are used. For now the pure XML approach saves me a lot of development time, so I think I'll go with it until I see problems.
It'll help a lot when I can finally get my hands on a larger dataset. I'm currently limited to 4 entries. I could make some mock data I guess, just for testing. I'm not sure how common that is?
Well, as it is, I don't have a say in how the data classes are structured, so at best, this'll work without too much work, at worst I'll have to bite the bullet and setup the db to work properly with the classes.
Would adding an XML Schema help? I have the XSD definitions I used to generate the data classes.
Schemas in the database will guarantee the structure of the XML you store, called typed XML. Some say it is faster to query typed XML some say it is slower. Both may be correct. There is of course some overhead storing the XML since it has to be verified against the schema. If you ever need to modify a schema that is used there is a bit of work that needs to be done.
1. Change all columns using the schema to untyped xml. 2. Drop the schema collection. 3. Create a schema collection with the new schema. 4. Change columns back to typed xml using the schema.
@AaronBertrand No problem, happy to help in some small way.
@AaronBertrand One thing I don't think has come across clearly to everyone is that int -> bigint conversions are less disruptive on a heap vs in a btree.
Disruptive in the sense of the amount of logging and duration of the change operation.
In a recent change, it took me about 30 minutes to drop all nonclustered indexes, drop clustered index and constraints, alter column to a bigint, recreated clustered index, create clustered index. Compared with 1 hr 45 min for building a new table w/ clustered index, copying the data, and then recreating the nonclustereds.
(The part about less logging depends on a few different things, granted.)
Kendra's recent post advocates the 'make another table' method. But I'm no longer convinced this is the best way if you can tolerate some amount of downtime.
Your method in P4 is still the best I know of (and probably will be for a long time) if you really need to keep downtime to near-zero.
I am trying to partition a table using a column that stores dates which already exists in the database. Would I need to re-create the table in order to do this?
As it relates to the following question:
SQL query failing because of commented part of T-SQL query
I posted an answer (the only answer) and worked with the OP over several days on narrowing down the issue and coming up with a work-around, and my answer was accepted. Then, the OP, in wanting an...
I am trying to partition a table using a column that stores dates which already exists in the database. Would I need to re-create the table in order to do this?
