--- title: "INDEXPROPERTY (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "03/06/2017" ms.prod: "sql-non-specified" ms.reviewer: "" ms.suite: "" ms.technology: - "database-engine" ms.tgt_pltfrm: "" 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 caps.latest.revision: 56 author: "BYHAM" ms.author: "rickbyh" manager: "jhubbard" --- # 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 ``` -- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse 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)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)].

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)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)].

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)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)].

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)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)].

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)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)].

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)