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