Skip to content

indexproperty-transact-sql.md

Latest commit

 

History

History
127 lines (95 loc) · 7.24 KB

indexproperty-transact-sql.md

File metadata and controls

127 lines (95 loc) · 7.24 KB
title INDEXPROPERTY (Transact-SQL)
description Returns the named property value of a specified table identification number, index or statistics name, and property name.
author markingmyname
ms.author maghan
ms.reviewer randolphwest
ms.date 10/31/2025
ms.service sql
ms.subservice t-sql
ms.topic reference
ms.custom
ignite-2025
f1_keywords
INDEXPROPERTY
INDEXPROPERTY_TSQL
helpviewer_keywords
INDEXPROPERTY function
indexes [SQL Server], viewing
indexes [SQL Server], properties
dev_langs
TSQL
monikerRange >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

INDEXPROPERTY (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricsqldb]

Returns the named index or statistics property value of a specified table identification number, index or statistics name, and property name. Returns NULL for XML indexes.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

INDEXPROPERTY ( object_ID , index_or_statistics_name , property )

Arguments

object_ID

An expression that contains the object identification number of the table or indexed view for which to provide index property information. object_ID is int.

index_or_statistics_name

An expression that contains the name of the index or statistics for which to return property information. index_or_statistics_name is nvarchar(128).

property

An expression that contains the name of the index or statistics property to return. property is varchar(128), and can be one of these values.

Note

Unless noted otherwise, NULL is returned when property isn't a valid property name, object_ID isn't a valid object ID, object_ID is an unsupported object type for the specified property, or the caller doesn't have permission to view the object's metadata.

Property Description Value
IndexDepth Depth of the index. Number of index levels.

NULL = XML index or input isn't valid.
IndexFillFactor Fill factor value used when the index was created or last rebuilt. Fill factor
IndexID Index ID of the index on a specified table or indexed view. Index ID
IsAutoStatistics Statistics were generated by the AUTO_CREATE_STATISTICS option of ALTER DATABASE. 1 = True
0 = False or XML index.
IsClustered Index is clustered. 1 = True
0 = False or XML index.
IsDisabled Index is disabled. 1 = True
0 = False
NULL = Input isn't valid.
IsFulltextKey Index is the full-text and semantic indexing key for a table. 1 = True
0 = False or XML index.
NULL = Input isn't valid.
IsHypothetical Index is hypothetical and can't be used directly as a data access path. Hypothetical indexes hold column-level statistics and are maintained and used by Database Engine Tuning Advisor. 1 = True
0 = False or XML index
NULL = Input isn't valid.
IsPadIndex Index specifies space to leave open on each interior node. 1 = True
0 = False or XML index.
IsPageLockDisallowed Page-locking value set by the ALLOW_PAGE_LOCKS option of ALTER INDEX. 1 = Page locking is disallowed.
0 = Page locking is allowed.
NULL = Input isn't valid.
IsRowLockDisallowed Row-locking value set by the ALLOW_ROW_LOCKS option of ALTER INDEX. 1 = Row locking is disallowed.
0 = Row locking is allowed.
NULL = Input isn't valid.
IsStatistics index_or_statistics_name is statistics created by the CREATE STATISTICS statement or by the AUTO_CREATE_STATISTICS option of ALTER DATABASE. 1 = True
0 = False or XML index.
IsUnique Index is unique. 1 = True
0 = False or XML index.
IsColumnstore Index is a memory optimized columnstore index. 1 = True
0 = False

Applies to: [!INCLUDE ssSQL11] and later versions.
IsOptimizedForSequentialKey Index has optimization for last-page inserts enabled. 1 = True
0 = False

Applies to: [!INCLUDE sql-server-2019] and later versions.

Return types

int

Exceptions

Returns NULL on error or if a caller doesn't have permission to view the object.

A user can only view the metadata of securables that the user owns or on which the user is granted permission. This means that metadata-emitting, built-in functions such as INDEXPROPERTY might return NULL if the user doesn't have any permission on the object. For more information, see Metadata visibility configuration.

Examples

[!INCLUDE article-uses-adventureworks]

A. Return properties for index on Employee table

The following example returns the values for the IsClustered, IndexDepth, and IndexFillFactor properties for the PK_Employee_BusinessEntityID index of the Employee table in the [!INCLUDE ssSampleDBnormal] database.

SELECT
    INDEXPROPERTY(OBJECT_ID('HumanResources.Employee'), 'PK_Employee_BusinessEntityID', 'IsClustered') AS [Is Clustered],
    INDEXPROPERTY(OBJECT_ID('HumanResources.Employee'), 'PK_Employee_BusinessEntityID', 'IndexDepth') AS [Index Depth],
    INDEXPROPERTY(OBJECT_ID('HumanResources.Employee'), 'PK_Employee_BusinessEntityID', 'IndexFillFactor') AS [Fill Factor];

[!INCLUDE ssresult-md]

Is Clustered Index Depth Fill Factor
------------ ----------- -----------
1            2           0

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

B. Return properties for index on FactResellerSales table

The following example examines the properties of one of the indexes on the FactResellerSales table.

SELECT
    INDEXPROPERTY(OBJECT_ID('dbo.FactResellerSales'), 'ClusteredIndex_6d10fa223e5e4c1fbba087e29e16a7a2','IsClustered') AS [Is Clustered],
    INDEXPROPERTY(OBJECT_ID('dbo.FactResellerSales'), 'ClusteredIndex_6d10fa223e5e4c1fbba087e29e16a7a2','IsColumnstore') AS [Is Columnstore Index],
    INDEXPROPERTY(OBJECT_ID('dbo.FactResellerSales'), 'ClusteredIndex_6d10fa223e5e4c1fbba087e29e16a7a2','IndexFillFactor') AS [Fill Factor];
GO

Related content