IndexProperty

Recently I had problem of slow insert on tables
During optimization we changed FillFactor of indexes and that improved the performance of insert.
We change the fill factor to 50%.


Lets see how to check different propery of indexes

There is a function INDEXPROPERTY which will give us various index properties.

INDEXPROPERTY ( object_ID , index_or_statistics_name , property )


We can get the details from BOL or http://msdn.microsoft.com/en-us/library/ms187729.aspx



We will see some important properties here

--FillFactor

SELECT Indexproperty (Object_id('fact_view'), 'Ix_FACT_VIEW_url_key', 'IndexFillFactor')

--Clustered Index

SELECT Indexproperty (Object_id('fact_view'), 'Ix_FACT_VIEW_url_key', 'IsClustered')

--Unique Index

SELECT Indexproperty (Object_id('fact_view'), 'Ix_FACT_VIEW_url_key', 'IsUnique')

Other  available properties are as below

IndexDepth
IndexID
IsAutoStatistics
IsDisabled
IsFulltextKey
IsHypothetical
IsPadIndex
IsPageLockDisallowed
IsRowLockDisallowed
IsStatistics