[Prior Post in Series] [Next Post in Series]

In this part, I will deal with the fact that many remaining tables do not have clustered indexes on them. In my humble opinion, ANY reasonable order of the disk will perform better than a random order, so putting a clustered index on every table is a must-do.

The pattern is simple, if a table does not have a clustered index, then put a clustered index on the first column.  The typical behavior of database designers is to put the key indexes at the start of each table, so we are assuming this is true in this solution.

To obtain the list of tables lacking indexes, use this code snippet.

 

SELECT O.NAME FROM Sys.Objects O WHERE O.type='U' AND O.Name NOT IN ( SELECT O.Name FROM Sys.Objects O JOIN Sys.Indexes I ON I.Object_Id=O.Object_Id AND O.type='U' AND I.type_desc='CLUSTERED')

You may wish to manually add some indexes (based on your domain knowledge) before proceeding further.

Code Solution

The solution below grabs the first column only. A more advanced approach would be to walk each table and build the index from the leading columns that are integer values.

SET NOCOUNT ON DECLARE @CMD nvarchar(max) DECLARE @Schema nvarchar(max) DECLARE @Table nvarchar(max) DECLARE @ColName nvarchar(max) DECLARE PK_Cursor CURSOR FOR Select o.Table_Schema, O.Table_Name, O.Column_Name FROM Information_Schema.Columns O JOIN Information_Schema.Tables T ON O.Table_Name=T.Table_Name AND O.Table_Schema=T.Table_Schema WHERE O.Ordinal_Position=1 AND T.Table_Type='BASE TABLE' AND O.Table_Name NOT IN ( SELECT O.Name FROM Sys.Objects O JOIN Sys.Indexes I ON I.Object_Id=O.Object_Id AND O.type='U' AND I.type_desc='CLUSTERED') ORDER BY O.Column_NAME OPEN PK_Cursor; FETCH NEXT FROM PK_Cursor INTO @Schema,@Table,@ColName WHILE @@FETCH_STATUS = 0 BEGIN SET @CMD='CREATE CLUSTERED INDEX [PK_' +@TABLE+'] ON ['+@Schema+'].['+@Table+'](['+@ColName+'] ASC)' IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name='PK_'+@Table) BEGIN TRY EXEC (@CMD) END TRY BEGIN CATCH SET @CMD='Error: '+@CMD Print @CMD END CATCH FETCH NEXT FROM PK_Cursor INTO @Schema,@Table,@ColName END; CLOSE PK_Cursor; DEALLOCATE PK_Cursor;

Summary

In this post we looked at loose-ends: tables lacking any clustered indexes.  We solved this problem by adding a clustered index using the first column.  A lot more analysis could be done on this database, but I ran out of time (after an apparent doubling of performance).