By: Greg Robidoux | Comments | Related: > Indexing
Problem
In a previous tip we talked about the built-in function DATABASEPROPERTYEX. With this function you were able to find out information about the various database properties. SQL Server has other built-in functions that allow you to retrieve other data such as information about indexes. In prior tip there was a stored procedure that returns information about all of your indexes, but there is other index data that may be helpful to have when analyzing your indexes.
Solution
SQL Server has a built-in function called INDEXPROPERTY that allows you to return specific information about an index. This function can be called from a SELECT statement to return the results of one or more indexes. So to find out the Index Fill Factor for all of your indexes in one of your databases you can run the following query. The second table below shows a sample output from this query when issued against the Northwind database.
SELECT sysobjects.name,
sysindexes.name,
INDEXPROPERTY(OBJECT_ID(sysobjects.name),sysindexes.name,'IndexFillFactor')
FROM sysobjects INNER JOIN
sysindexes ON sysobjects.id = sysindexes.id
WHERE xtype = 'U'
| Table | Index | IndexFillFactor |
|---|---|---|
| Orders | CustomerID | 80 |
| Orders | CustomersOrders | 80 |
| Orders | EmployeeID | 80 |
| Orders | EmployeesOrders | 80 |
| Orders | OrderDate | 80 |
| Orders | ShippedDate | 80 |
| Orders | ShippersOrders | 80 |
| Orders | ShipPostalCode | 80 |
| Products | PK_Products | 80 |
| Products | CategoriesProducts | 80 |
| Products | CategoryID | 80 |
| Products | ProductName | 80 |
| Products | SupplierID | 80 |
| Products | SuppliersProducts | 80 |
Some of the things you can find out about your databases include the following information.
| Property | Description |
|---|---|
| IndexDepth | Depth of the index. Returns the number of levels the index has. |
| IndexFillFactor | Index specifies its own fill factor. Returns the fill factor used when the index was created or last rebuilt. |
| IndexID | Index ID of the index on a specified table or indexed view. |
| IsAutoStatistics | Index was generated by the auto create statistics option of sp_dboption.
1 = True 0 = False NULL = Invalid input |
| IsClustered | Index is clustered.
1 = True 0 = False NULL = Invalid input |
| IsFulltextKey | Index is the full-text key for a table.
1 = True 0 = False NULL = Invalid input |
| IsHypothetical | Index is hypothetical and cannot be used directly as a data access
path. Hypothetical indexes hold column level statistics.
1 = True 0 = False NULL = Invalid input |
| IsPadIndex | Index specifies space to leave open on each interior node.
1 = True 0 = False NULL = Invalid input |
| IsPageLockDisallowed | 1 = Page locking is disallowed through sp_indexoption. 0 = Page locking is allowed. NULL = Invalid input |
| IsRowLockDisallowed | 1 = Row locking is disallowed through sp_indexoption. 0 = Row locking is allowed. NULL = Invalid input. |
| IsStatistics | Index was created by the CREATE STATISTICS statement or by the auto
create statistics option of sp_dboption. Statistics indexes are used
as a placeholder for column-level statistics.
1 = True 0 = False NULL = Invalid input |
| IsUnique | Index is unique.
1 = True 0 = False NULL = Invalid input |
Next Steps
- Take a look at this built-in index property function and how you can use it to document your databases or easily find out the index settings in your databases
- Use this function to audit your databases to see what the index settings are
- Use this along with this stored procedure to fully document your database indexes
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
