Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have around 40 tables in one of our production databases that, for varying reasons, where not created with a clustered index.

What is the best automated method for converting these heaps?

Since I'm a developer by nature, I really really don't want to do this manually.

I started creating a procedure for this, as documented in Why does this cursor produce results in the incorrect order?, however the responses I got to that post made me question what I was doing.

share|improve this question
What exactly are you trying to automate? The decision as to which column(s) to make as the CI keys seems best to left to a human to me except if you have a truly representative workload in which case maybe you can let the DTA have a go at it. –  Martin Smith Oct 3 '12 at 11:50
Do non-clustered indexes exist on the heap tables? One of those index's columns might be good for a clustered index. How big are the tables? If they are "too large" relative to the power of the server, you might want to drop the existing indexes, add the cluster index and then add back those indexes that you dropped. As Martin points out, it's hard to write code that will make a decision that would be pretty easy for a human. For 40 tables, I'd just write it out, maybe use some kind of templating for the rote parts. Maybe it would take a day to write, and I'd know more about my db. –  darin strait Oct 3 '12 at 13:36
add comment

1 Answer

up vote 7 down vote accepted

I would say that I agree with @MartinSmith. Determining the clustered index key is something that requires some thought and planning.

But if you were looking to get a head start and generate some T-SQL code that, say, creates the CREATE INDEX commands on heaps that have an IDENTITY column (a generally accepted clustered index key), then you could do something like this:

declare @create_indexes nvarchar(max);
set @create_indexes = N'';

    @create_indexes = @create_indexes + 
        'create clustered index ' + quotename('IX_' + object_name(i.object_id)) + 
        char(13) + char(10) +
        'on ' + quotename(object_name(i.object_id)) + '(' + quotename(c.name) + ');' +
        char(13) + char(10) + 
        'go' + 
        char(13) + char(10)
from sys.indexes i
inner join sys.columns c
on i.object_id = c.object_id
where i.type = 0
and c.is_identity = 1;

print @create_indexes;

Again, I will restate that like @MartinSmith said, I wouldn't just blindly execute any DDL that will impact performance and design like this. But the above is a start, and will give you the T-SQL for heaps that have an IDENTITY column.

share|improve this answer
+1 agreed - clustered index should be very carefully chosen - it's the most replicated structure inside SQL Server and needs to be as good as possible! –  marc_s Oct 3 '12 at 14:13
Note that the inline assignment and += syntax are not valid in 2005. –  Aaron Bertrand Oct 3 '12 at 15:04
@AaronBertrand Arg, I didn't see the 2005 tag. I've edited my answer to reflect the correction. Thanks, Aaron! –  Thomas Stringer Oct 3 '12 at 15:10
I fixed the inline assignment too. And changed it to NVARCHAR since object/index names can be Unicode (and nvarchar will be required if passing this to sp_executesql). –  Aaron Bertrand Oct 3 '12 at 15:22
@AaronBertrand Thanks, Aaron!!! –  Thomas Stringer Oct 3 '12 at 15:24
show 2 more comments

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.