indexproperty-transact-sql.md

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)

[!INCLUDEtsql-appliesto-ss2008-all_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.

Transact-SQL Syntax Conventions

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: [!INCLUDEssKatmai] through [!INCLUDEssCurrent]. 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: [!INCLUDEssKatmai] through [!INCLUDEssCurrent]. 1 = True 0 = False or XML index.
IsPageLockDisallowed	Page-locking value set by the ALLOW_PAGE_LOCKS option of ALTER INDEX.	Applies to: [!INCLUDEssKatmai] through [!INCLUDEssCurrent]. 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: [!INCLUDEssKatmai] through [!INCLUDEssCurrent]. 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: [!INCLUDEssSQL11] through [!INCLUDEssCurrent]. 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.

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 [!INCLUDEssSampleDBnormal] 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: [!INCLUDEssSDWfull] and [!INCLUDEssPDW]

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)
Statistics
sys.indexes (Transact-SQL)
sys.index_columns (Transact-SQL)
sys.stats (Transact-SQL)
sys.stats_columns (Transact-SQL)