A good general rule for creating tables is to always have a clustered index. The clustered index defines how data is physically ordered on the disk – and that’s why there can only be one clustered index on a table (a table without a clustered index is a HEAP). Even though it’s a good rule to have a clustered index there are common scenarios when you won’t have one on a table. A typical example is a load or import table, but even then it may have a clustered index applied when the load is complete.
When investigating a database it is helpful to understand the clustered indexes. The chosen clustered index can have a large impact on performance. Understanding the clustered indexes is especially crucial since every non-clustered index on a table relies on the clustered index.
SQL Server stores object information, including indexes, in system views. Table information is available in sys.objects and the associated indexes are in sys.indexes. This script list all of the tables in the current database and their associated clustered index. If the table doesn’t have a clustered index it will have NULL for the IndexName. You can use SSMS to look at indexes individually, but listing them via SQL makes it easy to see all of them at once.
SELECT @@SERVERNAME ServerName ,DB_NAME() DatabaseName ,ss.name SchemaName ,so.name TableName ,si.name IndexName ,sc.name ColumnName ,st.name ColumnType ,CASE WHEN st.is_user_defined = 0 THEN st.name WHEN st.is_table_type = 1 THEN AT.name WHEN st.is_user_defined = 1 THEN st2.name END ColumnBaseType ,CASE WHEN sc.max_length = -1 THEN 0 WHEN st.name IN ('nchar','nvarchar') THEN sc.max_length / 2 WHEN st2.name IN ('nchar','nvarchar') THEN sc.max_length / 2 ELSE sc.max_length END ColumnMaxLength FROM sys.objects so INNER JOIN sys.schemas SS ON SO.schema_id = SS.schema_id LEFT JOIN sys.indexes si ON si.object_id = so.object_id AND si.type = 1 --clustered index LEFT JOIN sys.index_columns sic ON si.object_id = sic.object_id AND si.index_id = sic.index_id LEFT JOIN sys.columns sc ON sic.object_id = sc.object_id AND sic.column_id = sc.column_id LEFT JOIN sys.types st ON sc.user_type_id = st.user_type_id LEFT JOIN sys.types st2 ON st.system_type_id = st2.system_type_id AND st2.system_type_id = st2.user_type_id AND st.is_user_defined = 1 LEFT JOIN sys.assembly_types AT ON ST.system_type_id = AT.system_type_id AND ST.user_type_id = AT.user_type_id WHERE so.type = 'u' ORDER BY so.name ,sic.Index_Column_ID
The script is located here – INDEXES.Clustered.Indexes
It is also located on our script page – I-Tech SQL Server Scripts