Rajendra Gupta
Indexproperty()

INDEXPROPERTY() function in SQL Database

November 22, 2021 by

This article will give an insight into the INDEXPROPERTY() function in SQL Databases.

Introduction

Indexes play a crucial role in database performance for every relational database such as Microsoft SQL Server. You can create clustered, non-clustered, XML Columnstore index on a table. The required index depends on the query workload, predicates.

While performing the performance tuning or investigation, we often want to know whether the table has an index defined. Similarly, if an index is there, what are its properties. A few common questions are as below.

  • Is this index clustered or non-clustered?
  • What is the index depth?
  • Whether the index is disabled or enabled?
  • Is it a hypothetical index?
  • Whether the statistics created by SQL Server automatically or manually?

SQL Database has a built-in function INDEXPROPERTY() to gather the index and SQL Server statistics information to answer these questions. In this article, we will learn this function using various examples.

Syntax of the INDEXPROPERTY()

INDEXPROPERTY ( object_ID , index_or_statistics_name , property )

This function has the following arguments:

  • Object_Id: It contains the object identification number of the table or the index for which we want to gather index property information
  • index_or_statistics_name: It is the index or statistics name
  • Property: In this argument, we enter the property that we wish to retrieve

The list of properties returned by the INDEXPROPERTY() function in SQL Database is as below:

  • IndexDepth: It tells the depth of the index or index levels. It returns NULL for an XML index or invalid input
  • IndexFillFactor: It gives the fill factor used when the index was created or last rebuilt
  • IndexID: It returns the Index ID of the specified index on the particular table
  • IsAutoStatistics: It tells whether the statistics are created manually or automatically. The automatic indexes are created using the AUTO_CREATE_STATISTICS option in the Alter SQL Database
    • Value 1: Auto statistics
    • Value 0: Manual or invalid input
  • IsClustered: Broadly, SQL Server has clustered and non-clustered indexes. You can use this field to check whether the specified index is clustered or not
    • Value 1: Clustered
    • Value 0: XML index or false
  • IsDisabled: SQL Database provides feasibility to enable or disable the index as well. Therefore, use this column to identify if the specified index is disabled
    • Value 1: Index is disabled
    • Value 0: Index is enabled
    • NULL: Invalid input
  • IsFulltextKey: It tells whether the specified index is the full-text and semantic indexing key for a table
    • Value 1: True
    • Value 0: False
    • NULL: Invalid input
  • IsHypothetical: SQL Server database engine tuning advisor uses the hypothetical index to hold column level statistics. These indexes cannot be used directly. Therefore, it is called a hypothetical index
    • Value 1: index is hypothetical
    • Value 0: index is not hypothetical
    • NULL: Invalid input
  • IsPadIndex: The pad index specifies space that needs to leave open on the interior nodes
    • Value 1: True
    • Value 0: False
    • NULL: Invalid input or XML index
  • IsPageLockDisallowed: This property gives information about the page locking value configured in the ALLOW_PAGE_LOCKS of ALTER INDEX statement of a SQL Database
    • Value 1: Page locking is disallowed
    • Value 0: Page locking is allowed
    • NULL: Invalid input
  • IsRowLockDisallowed: This property gives information about the page locking value configured in the ALLOW_ROW_LOCKS of ALTER INDEX statement
    • Value 1: Row locking is disallowed
    • Value 0: Row locking is allowed
    • NULL:s Invalid input
  • IsStatistics It specifies whether the index or statistics are created by CREATE STATISTICS statement or by the AUTO_CREATE_STATISTICS
    • Value 1: True
    • Value 0: False or XML index
  • IsUnique: This property tells whether the index is unique or not
    • Value 1: unique index
    • Value 0: Not unique or XML index
  • IsColumnstore: It indicates whether the index is a memory-optimized Columnstore index or not
    • Value 1: memory-optimized Columnstore index
    • Value 0: not memory-optimized Columnstore index
  • IsOptimizedForSequentialKey: This property is helpful to highlight whether an Index has optimization for last-page inserts enabled
    • Value 1: True
    • Value 0: False

Let’s explore the INDEXPROPERTY() function using various examples.

Example 1: Check whether the specified index exists on a table or not in a SQL Database

In this example, we check the exists of two indexes [IX_FirstName] and [PK_Customer_CustomerID] on the [SalesLT].[Customer] table. It uses the INDEXPROPERTY() function and [IndexID] property.

The queries return the index ids if it exists. For example, the first query returns Index ID 9.

SQL Database index property

If the index does not exist, the function returns NULL, as shown below.

NULL value

Example 2: Get Index properties

In this example, we will fetch the following properties from the index specified:

  • The index is clustered or non-clustered
  • Whether the index is enabled or disabled
  • What is the index depth?
  • What is the index fill factor?

Indexproperty()

The script returns the following output for our index in this example 2.

  • IsClustered: 0 defines index is not clustered
  • IsDisabled:0 defines index is enabled

In another example, it returns value 1 for the clustered index.

  • Note: The clustered index always has Index ID 1

Clustered Index

Example 3: Check Statistics are auto-created or not

You can also specify the statistics name in the 2nd argument and use the properties [Statistics].[Auto Statistics).

As shown below, the specified values belong to statistics, and SQL Server automatically creates them.

  • FYI: SQL Server creates the statistics automatically based on the query predicates and uses the naming convention _WS_Sys_*

Statistics

Let’s check another example of Index statistics. As shown below, under the statistics folder, we have statistics for the index. If we check its properties, it returns the following values.

Index Statistics

Example 4: Check Row level and page level locking configuration for an index in SQL Database

The INDEXPROPERTY() function can return the row-level and page-level locking configuration in an index. By default, these configurations are disabled in SQL Server.

Therefore, before we use this function, let’s alter an index using the below script and disable row-level locking.

Now, if we check the INDEXPROPERTY, we get value 1 for the row-level locking column, as shown below.

Indexproperty()

Similarly, we can disable page-level locking in an index and verify it using the INDEXPROPERTY() function.

Page-Level locking using INDEXPROPERTY()

  • Note: Do not disable either page or row-level locking until you have a specific need to do so. Always test changes at non-prod environments

Conclusion

This article explored SQL Database function INDEXPROPERTY() to retrieve various index properties. It is a valuable function, and you should be familiar with it.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. Designing effective SQL Server non-clustered indexes
  2. A complete guide to T-SQL Metadata Functions in SQL Server
  3. Top 25 SQL interview questions and answers about indexes
  4. Designing effective SQL Server clustered indexes
  5. Top 10 questions and answers about SQL Server Indexes
168 Views