How do I automate converting heaps into clustered indexes?

Question

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.

Answer 1

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.

Answer 2

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;