6

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.

2
  • 5
    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. Oct 3, 2012 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. Oct 3, 2012 at 13:36

2 Answers 2

8

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'';

select
    @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.

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

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


select
    @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;

select
    @create_PK = @create_PK + 
        'ALTER TABLE [dbo].' + quotename(object_name(i.object_id)) +' ADD  CONSTRAINT '
         + quotename('PK_' + object_name(i.object_id)) + ' PRIMARY KEY CLUSTERED '
         + '(' + quotename(c.name) 
         + ' ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];'  
         + 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;

print @create_PK;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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