This blog post first appeared on SQLMaestros
Before we start fine tuning SQL Server, it is good to make sure all basic best practices are in place. One such best practice is to have a clustered index on every table in the database (well, not always but most of the times, having clustered index per table yields better performance, especially if you are searching on clustered key column).
So how to find out whether a table has clustered index or not. It is easy to find out for single table (Yeah, SSMS shows that for you). But if you want to find all the user tables in a database that does not have clustered indexes, then this script will become handy for you.
USE SQLMaestros GO SELECT DB_NAME() As DatabaseName ,SCHEMA_NAME(ST.schema_id) + '.' + ST.[name] AS TableName FROM sys.tables st WHERE ST.[Type] = 'U' AND OBJECTPROPERTY(ST.object_id, 'TableHasClustIndex') = 0
OBJECTPROPERTY is a metadata function in SQL Server that returns info about schema-scoped objects. In our above example, if the user table has ‘TableHasClustIndex’ property as ‘0’ then that table does not have clustered index defined.
After finding tables without clustered indexes, now you may want to consider creating one. Before that, lets also find out how many rows that each table contains. If a table has less than 1000 rows, having an index on top it, does not have considerable impact on performance, so you may choose to ignore those tables.
;WITH TableNotHasClustIndex AS ( SELECT DB_NAME() As DatabaseName ,SCHEMA_NAME(ST.schema_id) + '.' + ST.[name] AS TableName ,( SELECT SUM(SP.[rows]) FROM sys.partitions SP WHERE ST.object_id = SP.object_id ) NoOfRows FROM sys.tables st WHERE OBJECTPROPERTY(ST.object_id, 'TableHasClustIndex') = 0 ) SELECT * FROM TableNotHasClustIndex WHERE NoOfRows > 1000
See you soon with another script.
This post is part of The Scripts Garage. You can find all scripts at The Scripts Garage.