Indexes play a crucial role in database performance for every relational database such as Microsoft SQL Server. They can be clustered, non-clustered, or XML Columnstore indexes. When it comes to performance tuning or investigation, it is often necessary to know whether a table has an index defined and what its properties are. This is where the INDEXPROPERTY() function in SQL Server comes in handy.

The INDEXPROPERTY() function allows us to gather index and statistics information for a table. It takes three arguments: the object identification number of the table or index, the index or statistics name, and the property that we wish to retrieve. The function returns various properties such as index depth, fill factor, index ID, whether the index is clustered or non-clustered, whether it is enabled or disabled, and more.

Let’s explore some examples to understand how the INDEXPROPERTY() function works:

Example 1: Checking the Existence of an Index

In this example, we want to check whether two indexes, [IX_FirstName] and [PK_Customer_CustomerID], exist on the [SalesLT].[Customer] table. We can use the INDEXPROPERTY() function with the ‘IndexId’ property to achieve this:

SELECT IndexProperty(Object_Id('SalesLT.Customer'), 'IX_FirstName', 'IndexId') AS IndexID;
SELECT IndexProperty(Object_Id('SalesLT.Customer'), 'PK_Customer_CustomerID', 'IndexId') AS IndexID;

If the index exists, the function will return the index ID. Otherwise, it will return NULL.

Example 2: Retrieving Index Properties

In this example, we will fetch various properties from a specified index, such as whether it is clustered or non-clustered, whether it is enabled or disabled, the index depth, and the fill factor:

SELECT INDEXPROPERTY(OBJECT_ID('SalesLT.Customer'), 'IX_FirstName', 'IsClustered') AS IsClustered,
       INDEXPROPERTY(OBJECT_ID('SalesLT.Customer'), 'IX_FirstName', 'IsDisabled') AS IsDisabled,
       INDEXPROPERTY(OBJECT_ID('SalesLT.Customer'), 'IX_FirstName', 'IndexDepth') AS [Index Depth],
       INDEXPROPERTY(OBJECT_ID('SalesLT.Customer'), 'IX_FirstName', 'IndexFillFactor') AS [Index Fill Factor];

The function will return the corresponding values for the specified index.

Example 3: Checking Auto-Created Statistics

We can also use the INDEXPROPERTY() function to check whether statistics are auto-created for a specified index:

SELECT INDEXPROPERTY(OBJECT_ID('SalesLT.Customer'), '_WA_Sys_00000003_5DCAEF64', 'IsAutoStatistics') AS [Auto Statistics],
       INDEXPROPERTY(OBJECT_ID('SalesLT.Customer'), '_WA_Sys_00000003_5DCAEF64', 'IsStatistics') AS [Statistics];

If the specified values belong to statistics, the function will return that they are auto-created by SQL Server.

Example 4: Checking Locking Configuration

The INDEXPROPERTY() function can also provide information about the row-level and page-level locking configuration for an index. For example, we can check whether row-level locking is disallowed for an index:

SELECT INDEXPROPERTY(OBJECT_ID('SalesLT.Customer'), 'IX_FirstName', 'IsPageLockDisallowed') AS [Page level Locking],
       INDEXPROPERTY(OBJECT_ID('SalesLT.Customer'), 'IX_FirstName', 'IsRowLockDisallowed') AS [Row level Locking];

By altering the index and disabling row-level locking, we can verify that the function returns a value of 1 for the row-level locking column.

It is important to note that you should not disable page or row-level locking unless you have a specific need to do so. Always test changes in non-production environments.

Conclusion

The INDEXPROPERTY() function in SQL Server is a valuable tool for retrieving various index properties. By using this function, you can gather important information about indexes and statistics, which can help in performance tuning and investigation.