--- title: "INDEXPROPERTY (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: 06/26/2019 ms.prod: sql ms.prod_service: "database-engine, sql-database, sql-data-warehouse, pdw" ms.reviewer: "" ms.technology: t-sql ms.topic: "language-reference" f1_keywords: - "INDEXPROPERTY" - "INDEXPROPERTY_TSQL" dev_langs: - "TSQL" helpviewer_keywords: - "INDEXPROPERTY function" - "indexes [SQL Server], viewing" - "indexes [SQL Server], properties" ms.assetid: 998d5788-4871-44a8-8125-0d9390868b84 author: MikeRayMSFT ms.author: mikeray monikerRange: ">=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current" --- # INDEXPROPERTY (Transact-SQL) [!INCLUDE[tsql-appliesto-ss2008-all-md](../../includes/tsql-appliesto-ss2008-all-md.md)] 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. ![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif "Topic link icon") [Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md) ## Syntax ``` INDEXPROPERTY ( object_ID , index_or_statistics_name , property ) ``` ## Arguments *object_ID* Is 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* Is 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* Is an expression that contains the name of the database property to return. *property* is **varchar(128)**, and can be one of these values. > [!NOTE] > Unless noted otherwise, NULL is returned when *property* is not a valid property name, *object_ID* is not a valid object ID, *object_ID* is an unsupported object type for the specified property, or the caller does not 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 is not 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 is not valid.| |**IsFulltextKey**|Index is the full-text and semantic indexing key for a table.|**Applies to**: [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)] and later.

1 = True

0 = False or XML index.

NULL = Input is not valid.| |**IsHypothetical**|Index is hypothetical and cannot 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 is not valid.| |**IsPadIndex**|Index specifies space to leave open on each interior node.|**Applies to**: [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)] and later.

1 = True

0 = False or XML index.| |**IsPageLockDisallowed**|Page-locking value set by the ALLOW_PAGE_LOCKS option of ALTER INDEX.|**Applies to**: [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)] and later.

1 = Page locking is disallowed.

0 = Page locking is allowed.

NULL = Input is not valid.| |**IsRowLockDisallowed**|Row-locking value set by the ALLOW_ROW_LOCKS option of ALTER INDEX.|**Applies to**: [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)] and later.

1 = Row locking is disallowed.

0 = Row locking is allowed.

NULL = Input is not 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 an xVelocity memory optimized columnstore index.|**Applies to**: [!INCLUDE[ssSQL11](../../includes/sssql11-md.md)] and later.

1 = True

0 = False| |**IsOptimizedForSequentialKey**|Index has optimization for last-page inserts enabled.|**Applies to**: [!INCLUDE[sql-server-2019](../../includes/sssqlv15-md.md)] and later.

1 = True

0 = False| ## Return Types **int** ## Exceptions Returns NULL on error or if a caller does not have permission to view the object. A user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as INDEXPROPERTY may return NULL if the user does not have any permission on the object. For more information, see [Metadata Visibility Configuration](../../relational-databases/security/metadata-visibility-configuration.md). ## Examples 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](../../includes/sssampledbnormal-md.md)] 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]; ``` Here is the result set: ``` Is Clustered Index Depth Fill Factor ------------ ----------- ----------- 1 2 0 (1 row(s) affected) ``` ## Examples: [!INCLUDE[ssSDWfull](../../includes/sssdwfull-md.md)] and [!INCLUDE[ssPDW](../../includes/sspdw-md.md)] The following example examines the properties of one of the indexes on the `FactResellerSales` table. ``` -- Uses AdventureWorks 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 ``` ## See Also [CREATE INDEX (Transact-SQL)](../../t-sql/statements/create-index-transact-sql.md) [Statistics](../../relational-databases/statistics/statistics.md) [sys.indexes (Transact-SQL)](../../relational-databases/system-catalog-views/sys-indexes-transact-sql.md) [sys.index_columns (Transact-SQL)](../../relational-databases/system-catalog-views/sys-index-columns-transact-sql.md) [sys.stats (Transact-SQL)](../../relational-databases/system-catalog-views/sys-stats-transact-sql.md) [sys.stats_columns (Transact-SQL)](../../relational-databases/system-catalog-views/sys-stats-columns-transact-sql.md)