March 18, 2009 at 7:30 am
What affect does adding a new column to a populated table with a Clustered Index in a certain position within the table have?
I have read if you add a column into the middle of a table via Management Studio that in the background SQL creates a temporary table with the same schema as the existing table plus the new column in it's proper position, then does a SELECT out of the current table into the temporary, then replaces the original with the temporary.
So I take it the Clustered Index would be rebuilt?
If you the column to the end of the table do you not have to rebuild the Clustered Index?
I have read that ordering new columns in a table is cosmetic and isn't really needed unless you use SELECT * from which is bad practice?
March 18, 2009 at 7:56 am
What you have read is correct... Adding columns into the middle of the table is quite fuzzy and out of database technical view not needed. However, many people tend to 'read' database tables from it's logical order: first ID's included in the PK, then codes, then measures logically grouped (for fact tables that is). Just throwing an id and code for example at the end of a large table, would be bad practice from my point of view.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
March 18, 2009 at 7:58 am
Yes, the clustered index and all non-clustered indexes would be rebuilt when you use SSMS to insert a column in a specific position as it is a new table and they are really new indexes, so on a large table this will take quite a bit of time and likely will timeout in SSMS unless you changed the timeout settings in SSMS.
If you add a column to the end of the table the clustered index should not need to be rebuilt unless you are adding that column to the clustered index as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 18, 2009 at 8:49 am
Cheers guys.
I'm going to suggest we add the columns to the end of the table as it is not worth the hassle adding them in a given order in the table and having to rebuild the indexes etc..
Regards
Carl
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply