April 20, 2009 at 10:42 am
For a new project I have to add four columns to over 100 tables. Obviously this is no problem, but the information stored in these columns completes the information stored in 3 other columns, already part of these tables: I know this is not necessary, but I would like to keep the new columns adjacent to the old ones to make thing clearer for developers.
Possible solutions are:
1) Move the old columns to the end of the table and then add the new columns
2) Create the new columns in the middle of the table, near to the old ones
3) Leave the old columns alone and then add the new columns at the end of the table
Solutions 1) and 2) need to drop and recreate the table, with dependencies issues for triggers, indexes etc and also would require long time to move data between the newly created table and its original copy... (= hell).
Solution 3) doesn't keep my columns together and makes it harder to code for developers.
I also own a licensed copy of SQLRefactor, but this tool lacks a feature for performing such kind of operations. Do you know any kind of tool or smart script that could help me in this task?
Any advice would be greatly appreciated.
Regards
Gianluca
-- Gianluca Sartori
April 20, 2009 at 11:11 am
Personally, I'd go with option 3. The location of the columns in the table is not relevent. If you want to keep things close together for the developeres, then I'd do that in the documentation for the tables provided to them. If they are using SSMS or VS, then they just need to look for the column names they need. I don't think it is going to hurt them to spend a few extra moments to locate the columns in the column list.
April 20, 2009 at 11:32 am
Thanks for the prompt reply, Lynn.
I agree with your choice, and I was going with it when I started to think about how developers here work. The information stored in the columns is hierarchical and now there's the need to add new levels to the hierarchy. Let's say it was A/B/C, now it has to turn to A/D/B/E/C/F, with each column in a higher level to the one on its right (I hope my poor English is enough to make this concept clear enough!). Developers are often asked that information by users, so they will end up doing some sort of SELECT * FROM Table and they would probably give back the wrong information if it is stored in a different column order in each table.
I know SELECT * is bad and I know that data information; I also know that storage has nothing to do with information: I don't want to turn this thread to a religion war, as I've seen in other forums on the same topic, I just would like to give developers a chance to make no mistakes.
If this means altering more than 100 tables with millions rows inside by hand, dealing with the risk of losing dependent objects, obviously I stop here and I will go with new columns in the end of the table, but if there's some tool to help me I will take the time to consider alternative ways.
Thanks again
Gianluca
-- Gianluca Sartori
April 20, 2009 at 11:47 am
You are thinking human readable here. If that is the issue, then I'd build views over the tables with the columns in the "proper human readable order" and have the developers use the views instead of the underlying tables. There is no real reason that you "must" put the column names together. For all you know SQL Server isn't actually "honoring" the order of the columns when it physically stores the data in the data pages anyway. Bit data types, for instance, will be consolidated into individual bytes even if declared randomly throughout a record.
I am not an expert in SQL Server internals, but I would not be surprised if SQL Server stored all fixed length columns at the front of each record followed by the variable length columns even if they were declared all mixed together in the table declaration.
April 20, 2009 at 2:26 pm
Lynn Pettis (4/20/2009)
I am not an expert in SQL Server internals, but I would not be surprised if SQL Server stored all fixed length columns at the front of each record followed by the variable length columns even if they were declared all mixed together in the table declaration.
That's exactly what it does do, with one small caveat. Nullable columns are considered, for the purposes of location on in the record structure, to be variable length (iirc)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 20, 2009 at 2:35 pm
Gianluca Sartori (4/20/2009)
I also own a licensed copy of SQLRefactor, but this tool lacks a feature for performing such kind of operations. Do you know any kind of tool or smart script that could help me in this task?
The only way to do that is to either create a new table, copy the data, constraints, indexes, etc over and then drop the old or to create new colums, update the new columns with the value of the old and then drop the old columns.
Either way, it's not worth it. Teach the developers not to use SELECT * and to select the columns in the order that they want to see them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2009 at 1:03 am
Thanks Lynn and Gail, I always get good advice from you.
I will try to find the best solution that fits my needs and I'll be back with some feedback on my choice.
Thanks again
Gianluca
-- Gianluca Sartori
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply