Non clustered PKs to be converted into Clustered ones
-
Hi Gurus,
In my production environment a few non clustered PKs are to converted into clustered index (PK), and there are child tables (FKs ) are exists, so i can not drop and recreate this PKs. Can anybody suggest me a way without dropping PK can we convert (rebuild) index from non clustered to clustered ?
TIA,
Sheilesh
-
You can convert a non-clustered PK to Clustered PK even though there are chilled tables referencing the parent table. It might take longer time depends on how big is your parent table. You might want to schedule a time to implement in prod
Shas3
-
Hi,
Thanks, I can not drop and re-create as dependent FK are there. The conventional way is drop all the FK's for the PK then drop PK and re-create it as clustered.
It will be great if anybody can suggest a way other then this conventional way (without dropping FKs).
TIA,
Sheilesh
TIA,
Sheilesh
-
Ah. I think what you are looking for is:
CREATE CLUSTERED .. INDEX index_name ...... WITH DROP_EXISTING
The index name specified must be the same as a currently existing index. SQL Server then creates the clusertered index without unlinking the foreign keys, and doesn't cause nonclustered keys to be rebuilt! So much faster!
If you've got your tempdb on a separate i/o channel, you can use the SORT_IN_TEMPDB option for added speed.
Julian Kuiters
juliankuiters.id.au -
Thanks Julian, Yes i am searchig for the same. I will get back to u. If i find any problem in using it.
Thanks again,
Cheers,
Sheilesh
-
CREATE CLUSTERED INDEX does cause the non-clustered index(es) to be rebuilt.
Refer to the BOL, use the Index tab, enter CREATE INDEX. Choose the option for Transact SQL. Scroll down to the section titled CLUSTERED. Read the 3rd paragraph.
It says.....Existing non-clustered indexes on a table are rebuilt when a clustered index is created.
-SQLBill
BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
-
You don’t need to drop and recreate. Simple check the box “Clustered” it will automatically converts to clustered PK. I did this a while back in EM for a large take. Took long time
Shas3
-
Shas3,
The reason it took a long time was that the indexes WERE dropped and recreated. EM does a lot of stuff 'behind the scenes'.
For example, if you want to change the order of the columns in a table, EM lets you just choose the order, but then it creates a new table with the columns in the new order, copies the data from the original table into the new one, drops the original table and then renames the new table. But EM doesn't tell you it's doing that.
EM makes things look easy, but all it really does is do the coding for you so you don't have to figure it out.
-SQLBill
-
Thanks Bill. I agree with you
Shas3
-
I think that Sheilesh is trying to convert the non-clustered primary keys to clustered, and that they cannot be dropped because of foreign keys.
Yes, other non-clustered indexes need to be rebuild, because of the way sql server then points these non-clustered index keys to a primary key to a row for faster table access.
Sheilesh didn't mention that speed was the issue here, more that they want to convert to clustered primary keys, without dropping the existing. Assuming also that this is a production server, you want to keep the index "online" as much as possible. With DROP_EXISTING, you are doing two steps (drop,create) in one.
Quote from BOL with added highlights:
- DROP_EXISTING
- Specifies that the named, preexisting clustered or nonclustered index should be dropped and rebuilt. The index name specified must be the same as a currently existing index. Because nonclustered indexes contain the clustering keys, the nonclustered indexes must be rebuilt when a clustered index is dropped. If a clustered index is recreated, the nonclustered indexes must be rebuilt to take the new set of keys into account.
- The DROP_EXISTING clause enhances performance when re-creating a clustered index (with either the same or a different set of keys) on a table that also has nonclustered indexes. The DROP_EXISTING clause replaces the execution of a DROP INDEX statement on the old clustered index followed by the execution of a CREATE INDEX statement for the new clustered index. The nonclustered indexes are rebuilt once, and only if the keys are different.
- If the keys do not change (the same index name and columns as the original index are provided), the DROP_EXISTING clause does not sort the data again. This can be useful if the index must be compacted.
- A clustered index cannot be converted to a nonclustered index using the DROP_EXISTING clause; however, a unique clustered index can be changed to a non-unique index, and vice versa.
- Note When executing a CREATE INDEX statement with the DROP_EXISTING clause, SQL Server assumes that the index is consistent, that is, there is no corruption in the index. The rows in the specified index should be sorted by the specified key referenced in the CREATE INDEX statement.
Julian Kuiters
juliankuiters.id.au -
Lets assume speed is the issue and the FK-cannot be touched over here and
Sheilesh Paliwal cannot wait until the normal maintenance time.
Maybe creating another index as clustered indes, that matches the PK exact is an option.
- # rows ?
- # inputs, updates, deletes ?
- datavolume ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply