SQL Optimisation :: Convert Primary Keys to Clustered Indexes

We recently spotted that one of the systems we use did not make use of clustered indexes on any tables out of the box. As a result performance was not as good as it could have been. The below script allows for an easy win optimisation by finding all tables which do not include a clustered index, and converting the table’s primary key to be a clustered index.

In a future post I’ll put up more details on what clustered indexes are, why you should always (pretty much) use them and other useful info for anyone playing with databases.

--This script is designed for MS SQL Server
use DbNameToOptimise
go

--disable all constraints on all tables (to avoid these causing errors when altering the indexes)
sp_msforeachtable 'alter table ? nocheck constraint all'
go
 
declare @sqls table(object_id bigint, sort int, sql nvarchar(max))
 
insert @sqls
select t.object_id, ic.key_ordinal, case when ic.key_ordinal=1 then  'CREATE UNIQUE CLUSTERED INDEX [' + i.name + '] ON [' + t.name + ']([' else ',[' end + c.name + case when ic.key_ordinal=icagg.maxko then ']) WITH DROP_EXISTING' else ']' end sql
from sys.tables t
inner join sys.indexes i on t.object_id = i.object_id
inner join sys.index_columns ic on i.object_id=ic.object_id and  i.index_id = ic.index_id
inner join sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
inner join (
      select object_id, index_id, MAX(key_ordinal) maxko from sys.index_columns group by object_id,index_id
) icagg on i.object_id = icagg.object_id and i.index_id = icagg.index_id
where t.is_ms_shipped=0
and i.is_primary_key=1
and not exists (
      --ignore tables which already have a clustered index
      select 1
      from sys.indexes i2
      where t.object_id = i2.object_id
      and i2.type = 1
)
order by t.name, i.name, ic.key_ordinal
 
declare @objid bigint
, @sql nvarchar(max)
 
while exists (select top 1 1 from @sqls)
begin
      set @sql=''
      select top 1 @objid=object_id from @sqls
      select @sql = @sql + sql from @sqls where object_id=@objid order by sort
      delete from @sqls where object_id = @objid
      exec (@sql)
end
 
go

--reenable constraints to leave the db as we found it (aside from the fix)
sp_msforeachtable 'alter table ? check constraint all'
go

About these ads