Creating a clustered index on existing table

Question

Creating a clustered index on existing table

BobMcC

SSCrazy

Points: 2260
More actions
January 17, 2007 at 2:45 pm

#62522

OK, I have a table with a 3 column primary key.
There is only one index currently on the table: a non-clustered index on the three columns that are the primary key.
There are performance problems accessing this table. Based on the queries that hit this table, I would like to replace the current index with a clustered index on the same columns (the composite primary key).
What is the best way to handle this? What considerations should I take into account?
rows reserved data index_size unused
260502760 63501024 KB 32700064 KB 17274232 KB 13526728 KB
Thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

You can use the following sample script to do it but your table will be offline during this operation. If it is large table it will take good amount of time..

CREATE UNIQUE CLUSTERED

INDEX [UPKCL_testpk] ON [dbo].[testpk] ([au_id])

WITH

DROP_EXISTING

ON [PRIMARY]

MohammedU
Microsoft SQL Server MVP

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

The quickest way is to check the clustered box in EM , index properties and save.

However, this isn't good in a controlled environment, it may also take a while.

other than that script the table, extract the drop and create statements for the PK and just remove the non clustered statement - run to drop and recreate. The table will not be available during the index rebuild.

Don't assume changing a non clustered to a clustered pk will make a difference, it may not. I sometimes add non clustered indexes to cover clustered indexes to gain performance - but that's a different thread.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

BobMcC SSCrazy Points: 2260 More actions · Answer 3

Thanks for the replies. Colin, what do you mean when you say "this isn't good in a controlled environment"?

Regarding it taking a while... if I use EM or the SQL code will there really be an appreciable difference in the overall time it takes to complete? I figure both ways are going to take a while. I was going to kick it off tonight.