INDEXPROPERTY Function in SQL Server

By: Manvendra Singh   |   Updated: 2021-05-13   |   Comments   |   Related: > Indexing

Problem

Indexes and statistics are very important for SQL Server database performance. There are various settings and properties to get these objects to work as intended. As a SQL Server DBA, there are times when we need to know what the settings and properties are for an index. One simple requirement is to check whether a specific index is present for a table and if it is enabled. In this tutorial, I will explain how to use the system function INDEXPROPERTY to easily get index and statistics information.

Solution

SQL Server has a useful system function INDEXPROPERTY() to get various property information about an index or statistics for any table in the database. The function will return a NULL value if you are fetching properties about XML indexes. Below is the syntax to use this system function.

--Syntax to use this function
INDEXPROPERTY ( object_ID , index_or_statistics_name , property ) 

If you do not have permission to the object that you are checking, then you will receive a NULL value for any property you are fetching, so only someone that has access to the object being checked can fetch the properties using this function.

Below is the list of all properties that can be accessed for an index or statistics using INDEXPROPERTY.

• IndexDepth
  • This property tells the total number of index levels. If your input is not correct or you are fetching the details for an XML index, then you will be get a NULL value for this index property.
• IndexFillFactor
  • This index property is used during index rebuild or index creation. You can check the configured value of fill factor.
• IndexID
  • This returns the Index id of the index.
• IsAutoStatistics
  • You can find out if statistics are automatically created using the AUTO_CREATE_STATISTICS with the ALTER command or if it was manually created using CREATE STATISTICS. Below are the values.
    • 1 - statistics automatically created using ALTER statement
    • 0 - not automatically created or it is an XML index
• IsClustered
  • Whether index is a cluster index or not.
    • 1 - then it means it is a cluster index
    • 0 - it means either it is not a cluster index, or it is an XML index
• IsDisabled
  • Whether index is disabled or not.
    • 1 - it means it is disabled
    • 0 - it means it is not disabled
    • NULL - it means either input is invalid, or it is XML index
• IsFulltextKey
  • This property tells us about index is full-text and semantic indexing key for a table.
    • 1 - it means this index is full text and semantic indexing key for a table
    • 0 - it means it is not full text key or it is XML index
    • NULL - it means either input is invalid
• IsHypothetical
  • Tells about whether index is hypothetical or not.
    • 1 - it means index is hypothetical
    • 0 - it means it is not hypothetical index or it is an XML index
    • NULL - value shows input is not valid
• IsPadIndex
  • Whether index is pad index or not.
    • 1 - means index is pad index
    • 0 - means either it is not pad index or it is XML index
• IsPageLockDisallowed
  • This This is important index property that tells about page locking value set by the ALLOW_PAGE_LOCKS option of ALTER INDEX statement.
    • 1 - means page locking is disallowed
    • 0 - means page locking is allowed
    • NULL - value means input is not valid
• IsRowLockDisallowed
  • This is similar property as I described above. This property will give information about row locking value set by the ALLOW_ROW_LOCKS option of ALTER INDEX.
    • 1 - it means row locking is disallowed
    • 0 - it means row locking is allowed
    • NULL - value means input is not valid
• IsStatistics
  • This is very much like "IsAutoStatistics" as I described above. IsStatistics property tells whether the index or statistics are created by CREATE STATISTICS statement or by the AUTO_CREATE_STATISTICS option of ALTER DATABASE.
    • 1 - means it is created using CREATE STATISTICS statement
    • 0 - means it is not created using above statement or it is XML index
• IsUnique
  • Whether Index is unique or not.
    • 1 sho1 - shows it is unique index
    • 0 - shows it is not unique index or it is XML index
• IsColumnstore
  • This property information indicates whether an index is an xVelocity memory optimized columnstore index or not.
    • 1 - means it is memory optimized columnstore index
    • 0 - means it is not memory optimized columnstore index
• IsOptimizedForSequentialKey
  • This property is used to get information about option optimization of last page inserts is enabled or not.
    • 1 - enabled
    • 0 - not enabled

Use Cases of INDEXPROPERTY function

I will show various use cases of this system function to get several index and statistics properties.

I would like to demonstrate first how to tell if an index is present on the table. Most of the time we need to find whether a specific index is present in a given table. We can get this information using this system function. If output returned is NULL it means the index is not present in the table.

Run the below T-SQL statement to check and validate whether index "PK_Person_BusinessEntityID" is present for table "Person.Person".

If IndexProperty(Object_Id('Person.Person'), 'PK_Person_BusinessEntityID', 'IndexId') Is NULL
PRINT 'Index does not exist' 

This did not print out "index does not exist", so this means the index does exist.

We can do a test to make sure this is working.  I added an extra "S" below to the index name for testing.

If IndexProperty(Object_Id('Person.Person'), 'PK_Person_BusinessEntityIDS', 'IndexId') Is NULL
PRINT 'Index does not exist' 

When we run this, we can see we get the message "index does not exist".

Another use case of this function is to check various index property details. If you want to know details about your index then you can use this function to get your answer:

• What is the Index ID for supplied index?
• Is this a cluster index or non-cluster index?
• How to check depth level of index B-Tree?
• What is the Fill Factor for the index?
• Is my index disabled?

Run the below command to answer these questions.

If IndexProperty(Object_Id('Person.Person'), 'PK_Person_BusinessEntityID', 'IndexId') Is NULL
PRINT 'Index does not exist'
ELSE
SELECT   
      INDEXPROPERTY(OBJECT_ID('Person.Person'), 'PK_Person_BusinessEntityID', 'IndexID')         AS [Index Id],
      INDEXPROPERTY(OBJECT_ID('Person.Person'), 'PK_Person_BusinessEntityID', 'IsClustered')     AS [Is Clustered],  
      INDEXPROPERTY(OBJECT_ID('Person.Person'), 'PK_Person_BusinessEntityID', 'IndexDepth')      AS [Index Depth],   
      INDEXPROPERTY(OBJECT_ID('Person.Person'), 'PK_Person_BusinessEntityID', 'IndexFillFactor') AS [Fill Factor],
      INDEXPROPERTY(OBJECT_ID('Person.Person'), 'PK_Person_BusinessEntityID', 'IsDisabled')      AS [Disabled]

Let's see the answers to the above questions:

• The Index ID for supplied index is 1
• The index is a clustered index
• The index depth is 3
• The Fill Factor for this index is configured as 0
• This index is not disabled

I have changed the index name with statistics name and executed it to check the output.

 If IndexProperty(Object_Id('Person.Person'), 'AK_Person_rowguid', 'IndexId') Is NULL
PRINT 'Index does not exist'
SELECT   
      INDEXPROPERTY(OBJECT_ID('Person.Person'), 'AK_Person_rowguid',' IndexID')         AS [Index Id],
      INDEXPROPERTY(OBJECT_ID('Person.Person'), 'AK_Person_rowguid', 'IsClustered')     AS [Is Clustered],  
      INDEXPROPERTY(OBJECT_ID('Person.Person'), 'AK_Person_rowguid', 'IndexDepth')      AS [Index Depth],  
      INDEXPROPERTY(OBJECT_ID('Person.Person'), 'AK_Person_rowguid', 'IndexFillFactor') AS [Fill Factor],
      INDEXPROPERTY(OBJECT_ID('Person.Person'), 'AK_Person_rowguid', 'IsDisabled')      AS [Disabled]

You can see the output is a little different.

Here is another example for statistics. As the object we are looking at is statistics and not an index we can change what we are looking for as shown below.

If IndexProperty(Object_Id('Person.Person'), 'AK_Person_rowguid', 'IndexId') Is NULL
PRINT 'Index does not exist'
ELSE
SELECT   
      INDEXPROPERTY(OBJECT_ID('Person.Person'), 'AK_Person_rowguid', 'IndexID')          AS [Index Id],
      INDEXPROPERTY(OBJECT_ID('Person.Person'), 'AK_Person_rowguid', 'IsClustered')      AS [Is Clustered],
      INDEXPROPERTY(OBJECT_ID('Person.Person'), 'AK_Person_rowguid', 'IsDisabled')       AS [Disabled],
      INDEXPROPERTY(OBJECT_ID('Person.Person'), 'AK_Person_rowguid', 'IsAutoStatistics') AS [Auto Statistics],  
      INDEXPROPERTY(OBJECT_ID('Person.Person'), 'AK_Person_rowguid', 'IsStatistics')     AS [Statistics]

The above T-SQL statement has been executed and here is the output.

The last example I want to show is getting information about page and row level locking for the specified object.

SELECT 
      INDEXPROPERTY(OBJECT_ID('Person.Person'), 'AK_Person_rowguid', 'IsPageLockDisallowed') AS [Page Locking],  
      INDEXPROPERTY(OBJECT_ID('Person.Person'), 'AK_Person_rowguid', 'IsRowLockDisallowed')  AS [Row Locking]

You can see below the output says that neither page or row locking is enabled for the database object.

Next Steps

• Use this system function to get index or statistics property information as needed or you can also use this function in your scripts to get the output.
• Check out this related article Retrieving SQL Server Index Properties with INDEXPROPERTY

About the author

Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips