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 |
|
sql_server_dba
Posting Yak Master
167 Posts |
 Posted - 2011-01-24 : 16:37:25
|
| All,I have a table with 300 million records and want to modify the index for it. Currently i have an index on two columns A and B and now i want to modify the existing one by adding column C. So my question is, when modifying the existing index, what script do i need to use? Do i need to drop the existing one and then create a new one or is there a way to add a column to existing index? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-24 : 17:41:51
|
| You should be able to do CREATE INDEX ... WITH DROP_EXISTING. There's no alter index to add a column.--Gail ShawSQL Server MVP |
 |
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2011-01-24 : 17:48:32
|
| So when we do DROP_Existing, does it 1st drops and then recreate it or does it first creates and then drops it? If the process takes long time and if i want to kill the query... what would be the rollback process? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-25 : 00:28:12
|
| Not sure. If you roll it back you'll be left with the original index.--Gail ShawSQL Server MVP |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-01-25 : 00:47:36
|
| I may be missing something here.Somebody please correct me if I am wrong.With 300 million records I think it will take a hell lot of time to drop and recreate the index.What you can do is you can disable the existing index and create a new index on the columns you want and make sure that you set the option SORT_IN_TEMPDB to ON in the create index syntax.Also make sure that there is enough space in tempdb before creating the index and if possible restart the SQL service to free the space from tempdb after creation of the new index.PBUH |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-25 : 02:49:25
|
| Drop, no. Like drop table it'll be a quick operation with the actual deallocation of the pages done in the background after, same thing if the index is disabled. (both drop and disable remove the index's b-tree, drop also takes out the metadata, disable does not)Creating it is going to be time and resource intensive.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
Script for modifying an index?