Top 10 Index T-SQL Statements That SQL Server DBAs Should Know

SQL Server DBAs know that indexes in databases are very similar to indexes in libraries. An index in a database is a structure associated with a table or view that speeds retrieval of rows from the table or view.

This article lists the top index-related T-SQL statements that are useful for SQL Server database administrators. The T-SQL statements covered in this article are classified into three categories: Index Definition or Create, Query — Query index related information and Maintenance.

Definition – Create Index

1. Clustered Index

Clustered indexes store the data rows in sorted order in the table based on their key values. Only one clustered index can be created per table, because the data rows themselves can only be sorted in one order.

A clustered index can be created while creating constraints like primary key on an existing table. Example:

ALTER TABLE [MyAddress] ADD  CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED (	[AddressID] ASC) ON [PRIMARY]GO

A clustered index can also be created on a column with no constraints related clause. Example:

CREATE CLUSTERED INDEX [MyAddress_id_CIX] ON [MyAddress1] (	[ID] ASC)ON [PRIMARY]GO

2. Non Clustered Index

Generally, nonclustered indexes are created to improve the performance of frequently used queries not covered by the clustered index. In a nonclustered index, the logical order of the index does not match the physical stored order of the rows on disk.

A nonclustered Index can be created on an existing table covering columns not covered by clustered index. Example:

CREATE UNIQUE NONCLUSTERED INDEX [NIX_col5_col2_col3_col4_col6] ON [MyAddress] (	[AddressLine1] ASC,	[AddressLine2] ASC,	[City] ASC,	[StateProvinceID] ASC,	[PostalCode] ASC)ON [PRIMARY]GO

A nonclustered index can also be created while creating constraints on the existing table. Example:

ALTER TABLE [MyAddressType] ADD  CONSTRAINT [DEFF_MyAddressType_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]GO

3. XML Index

An XML index can be created on an XML column and the table must have a clustered index on the primary key. The XML index can be primary or secondary.

A primary XML index can be created as shown below:

CREATE PRIMARY XML INDEX idx_xCol_MyTable on MyTable (xCol)

A secondary XML index can be created as shown below:

CREATE TABLE MyTable (Col1 INT PRIMARY KEY, XmlCol XML)GO-- Create primary index.CREATE PRIMARY XML INDEX PIdx_MyTable_XmlCol ON T(XmlCol)GO-- Create secondary indexes (PATH, VALUE, PROPERTY).CREATE XML INDEX PIdx_MyTable_XmlCol_PATH ON MyTable(XmlCol)USING XML INDEX PIdx_MyTable_XmlColFOR PATHGOCREATE XML INDEX PIdx_MyTable_XmlCol_VALUE ON T(XmlCol)USING XML INDEX PIdx_MyTable_XmlColFOR VALUEGO

4. Spatial Index

SQL Server 2008 provided a special type of column called a spatial column, which is a table column that contains data of a spatial data type, such as geometry or geography.

A spatial index can be created using the following syntax:

CREATE TABLE MySpatialTable(id int primary key, geometry_col geometry);CREATE SPATIAL INDEX SIndx_MySpatialTable_geometry_col1    ON MySpatialTable(geometry_col)   WITH ( BOUNDING_BOX = ( 0, 0, 500, 200 ) );

Query Index related metadata

5. Find all Indexes

The following query can be used to query all the tables, columns and indexes on the current database:

SELECT OBJECT_SCHEMA_NAME(BaseT.[object_id],DB_ID()) AS [Schema],    BaseT.[name] AS [table_name], I.[name] AS [index_name], AC.[name] AS [column_name],    I.[type_desc]FROM sys.[tables] AS BaseT    INNER JOIN sys.[indexes] I ON BaseT.[object_id] = I.[object_id]    INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]   INNER JOIN sys.[all_columns] AC ON BaseT.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id] WHERE BaseT.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP' ORDER BY BaseT.[name], I.[index_id], IC.[key_ordinal]

6. Fragmentation

The following query can be used to find the index fragmentation on all the tables in the current database:

SELECT object_name(IPS.object_id) AS [TableName],    SI.name AS [IndexName],    IPS.Index_type_desc,    IPS.avg_fragmentation_in_percent,    IPS.avg_fragment_size_in_pages,    IPS.avg_page_space_used_in_percent,    IPS.record_count,    IPS.ghost_record_count,   IPS.fragment_count,    IPS.avg_fragment_size_in_pagesFROM sys.dm_db_index_physical_stats(db_id(DB_NAME()), NULL, NULL, NULL , 'DETAILED') IPS   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_idWHERE ST.is_ms_shipped = 0order by IPS.avg_fragment_size_in_pages desc

7. Missing index

SQL Server keeps track of the indexes that it thinks you should create that will help in improving the performance of queries. The following query list all missing indexes.

SELECT  sys.objects.name, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact,  'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NULL                 THEN ''      ELSE CASE WHEN mid.equality_columns IS NULL                     THEN ''          ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL                 THEN ''      ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement, mid.equality_columns, mid.inequality_columns, mid.included_columns     FROM sys.dm_db_missing_index_group_stats AS migs             INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle             INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID()             INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID     WHERE     (migs.group_handle IN         (         SELECT     TOP (500) group_handle             FROM          sys.dm_db_missing_index_group_stats WITH (nolock)             ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))          AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1     ORDER BY 2 DESC , 3 DESC 

8. Unused index

The following statement lists all the indexes that have not been used. This also generates the DROP index statement which can come handy when deleting the indexes.

SELECT o.name, indexname=i.name, i.index_id   , reads=user_seeks + user_scans + user_lookups   , writes =  user_updates   , rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id), CASE	WHEN s.user_updates < 1 THEN 100	ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates  END AS reads_per_write, 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'FROM sys.dm_db_index_usage_stats s  INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id   INNER JOIN sys.objects o on s.object_id = o.object_idINNER JOIN sys.schemas c on o.schema_id = c.schema_idWHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1AND s.database_id = DB_ID()   AND i.type_desc = 'nonclustered'AND i.is_primary_key = 0AND i.is_unique_constraint = 0AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000ORDER BY reads

Index Maintainenance

9. Rebuild index

When an index gets fragmented, it requires defragmentation. Defragmentation can be done using the rebuild clause when altering a table. This command is equivalent to DBCC DBREINDEX in SQL Server versions prior to 2005. The command that can be used to rebuild the index is as follows:

USE AdventureWorks2008R2;GOALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.EmployeeREBUILD;GO

If ALL is not specified in rebuild, it will not rebuild a nonclustered index.

10. REORGANIZE index

Specifies that the index leaf level will be reorganized. The REORGANIZE statement is always performed online. This command is equivalent to DBCC INDEXDEFRAG in SQL Server versions prior to 2005.

USE AdventureWorks2008R2;GOALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhotoREORGANIZE ;GO

Conclusion

I hope you’ve found useful this list of the top Index-related T-SQL statements that are useful for SQL Server database administrators.

For more on T-SQL programming, see our T-SQL programming series

See all articles by MAK