May 7, 2009 at 3:05 pm
Say that I've got a table with (pick a number) 10 columns and a million rows. (If you don't say it, that's ok..I won't know either way, really)
Now I alter the table and add 10 more columns and update the table so we have data
Where are the new columns stored? Are they in different pages that the columns of the same given row?
The root question is: What's the performance difference (for selects and updates) between the table above and an exact copy of the table, but with foresight enough to have 20 columns to start with?
Thanks guys!
May 8, 2009 at 8:46 am
To my knowledge... No, this won't change performance. Let's also add a clustered index to the table. Assuming it's the same clustered index before and after the alter, all the data is stored at the leaf level of the cluster... before & after the alter. I'm pretty sure you'll get a bit of restructuring and possibly some page splits... You'd probably want to rebuild the cluster after you were done, but It's because of all the extra data, not the extra columns. Let's say you had the table with 20 columns at the start, but 10 columns had no data. If you add the data later, same issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 8, 2009 at 8:54 am
wonderful. Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply