Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
suhasckanade
Starting Member
8 Posts |
 Posted - 2008-06-17 : 01:32:26
|
| We have a table which has about 2,500,000 rows & daily in grows by about 10,000. This table already has 25 columns in it. Now for specific reason we need to add 15 columns to it. Now my question is that...1. Will performance of queries (Insert,Update,Delete,Select) affect if I will add these 15 columns to such large table??2. When we are adding any columns to such large table, where those columns actually get added in backend?? Means when we add new columns then will those columns gets added to existing data pages or creates new datapages with link to those pages?? Because I have observed that while using alter table command to add 15 columns to such large table(during testing) it takes few seconds only, so I think table doesn't get rearranged it's rows (data pages) to incorporate new columns. Is it so??3 Is there any differences in structure of table with 35 columns(in my e.g.) created at the beginning and the structure of table having 25 columns with 2,500,000 rows & then 15 columns added to it??Thanks in advance |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-21 : 16:00:18
|
| 1. yes, will be blocked by alert table process. By the way, table with 2.5m rows is not that big.2. if you don't assign any default value, it just updates meta data. 3. columns' order maybe different. |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-21 : 22:00:44
|
| 1)Yes, It looks it is denormalized and usually for OLTP environment,it causes fragmentation of index pages as lots of DML operation occurs. |
|
|
|
|
|
|
|
|
Altering large table