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 |
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
 Posted - 2012-06-13 : 19:30:34
|
A colleague of mine (not me, I swear! :) created clustered indexes (CI) on 10 largish tables (about 10^9 rows each) but he used the wrong columns in the CI key. So the index he created is useless and needs to be dropped and recreated.-- here's the step done by mistake, this has occurred alreadycreate clustered index CI on MyTable(WrongColumn asc)go-- drop useless CI on WrongColumn drop index CI on MyTablego-- recreate CI on the correct columncreate clustered index CI on MyTable(RightColumn asc)go My question is, is there any way to do the drop/create in a single step? If you have a large table with a CI, just dropping the CI can take some time because it's got to turn the CI into a heap. Then wait again while it builds the CI over again. I want to skip the intermediate step of laying out all the rows in pages of a heap, since what I really want is a CI, just with different keys than the CI I currently have.I'm hoping for something like this but it's a small hope:alter index CI on MyTable (RightColumn asc) -- this doesn't work obviously
elsasoft.org |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-06-13 : 20:19:57
|
| create clustered index CI on MyTable(RightColumn asc) with (drop_existing = on)Note that this will also rebuild all the nonclustered indexes on the table. You may want to drop them first before re-creating the clustered index and then add them back one at a time.If you have Enterprise Edition you can also do an ONLINE index build, however you need to have about 120% of the size of the table as free space in order to do so. |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2012-06-14 : 00:20:05
|
perfect thanks! no other indexes besides the CI so don't need to drop/recreate NCI.
elsasoft.org |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-14 : 04:30:32
|
| You will also need to look at all the non-clustered indexes.At the moment they all include WrongColumn - after the change they will include RightColumn instead. Could mean that some queries no longer have a covering index and could table scan.You might want to include WrongColumn is the non-clustered indexes and change them one at a time later.Have you checked there wasn't a reason for making WrongColumn the clustered index column?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2012-06-14 : 09:57:19
|
yep, checked all that. there are no NCI, and there should not be. this table has only one index, the CI, and that's as it should be.
elsasoft.org |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-06-14 : 10:42:11
|
quote:
Originally posted by jezemine
A colleague of mine (not me, I swear! :) created clustered indexes (CI) on 10 largish tables (about 10^9 rows each) but he used the wrong columns in the CI key. So the index he created is useless and needs to be dropped and recreated.
10^9, so.... Dramatic...... Can't you say a billion rows like a normal person? an american billion obviously, not a *proper* one.Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2012-06-14 : 12:01:10
|
that's exactly why I wrote 10^9. if I say billion, then people in EU will think i mean 10^12. 10^12 rows would truly be big data though.
elsasoft.org |
|
|
|
|
|
|
|
|
change columns in clustered index