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 |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2002-06-07 : 08:12:43
|
| Is there a better way than EM (I.e. a scripted way) to take a nonclustered index, and make it clustered ?We have some "performance" indexes that were added to a system, and are testing the rollback of these indexes. Is there no way to do a T-SQL alter index, or would I have to drop the PK indexes and create them again ? |
|
|
dataphile
Yak Posting Veteran
71 Posts |
Posted - 2002-06-07 : 08:55:01
|
| A primary key doesn't have to be clustered, which is irelavant in this case, but it means that you don't have to drop the pk to create a clustered index on the field.Just create the clustered indexcreate index cx_indexname on tablename(fieldname[, fieldname, n]) |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2002-06-07 : 09:18:46
|
| Our situation is that were trying to get back to the "previous" state of the database, which was a case of having the PK indexes clustered. I was hoping to there might be a method to "alter" and existing index (i.e. those PK indexes) rather than have to drop and re-create them. EM allows you to take the index, and "set" the clustered tickbox ... when you save it does some work (hour glass ) , but eventually changes the index. I need to do this in a controlled manner, via scripts, but I was hoping that perhaps EM was using some unknown "alter index" functionality. So far, it looks like my course of action must, unfortunately, be to drop the new performance indexes, drop the pk indexes, re-create the pk indexes.Thoughts, comments , corrections, suggestions ? |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-06-07 : 09:23:48
|
| AFAIK, EM does the same thing (drop/create index) behind the scenes. You can easily verify it if you run a profiler trace. Edited by - izaltsman on 06/07/2002 09:24:39 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-07 : 09:27:19
|
quote:
...via scripts, but I was hoping that perhaps EM was using some unknown "alter index" functionality....
There is no magic to enterprise manager. Infact, in a lot of cases where you could use an ALTER object, em (behind the scenes) will do a DROP/CREATE . . . Use SQL Profiler to monitor the DDL that EM is doing and you will see exactly what is going on . . .You are going to have to DROP/CREATE to change and index from clustered to non-clustered or vice-versa. Think about the difference between the two index types. It requires a resorting of the physical datapages, no matter how you slice it.<O> |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-07 : 09:27:53
|
| The thing is, since you're creating a clustered index, it will physically move the data around according to the key expression you use. In this instance, anything like ALTER INDEX would most likely have to drop and recreate the index anyway. There's no shortcut for creating a clustered index because of the need to rearrange data and index pages.Not to mention that if you have a clustered index, all of the non-clustered indexes use it as their baseline. Therefore, if you change the clustered index all of the others must be rebuilt too. You can use DBCC DBREINDEX to simplify this, but it won't alter an existing index's clustering. |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2002-06-07 : 10:07:23
|
| Thanx for helping clearing this in my mind. I should have known better than to hope that EM had some secret answers ... wishful thinking got the better of me I'm afraid. |
|
|
|
|
|
|
|
|
Clustered index creation