view indexes in sql server
Indexes are very important part of SQL Server database, indexes help database engine to find relevent rows faster. In SQL Server each table can have up to 249 nonclustered indexes. Up to 16 columns can be combined into a single index. All the columns in an index must be in the same table or view. The maximum allowable size of the combined index value can not exceed from 900 bytes. You can have only one clustered index on each table.
In SQL Server 2000 and 2005 you can query sysindexes and sys.sysindexes table and table view (in 2005 for backward compatibility) to get the list of all the indexes. Index keys are stored in sysindexkeys, many times you want to view indexes in your database. Here is a handy T-SQL scripts to find available indexes on each table.
Here is an easy way to find all the user defined indexes in a database.
SELECT
OBJECT_NAME( i.id ) AS [Table Name],
i.name AS [Index Name],
i.keycnt as [Total Keys],
i.rows as [Total Rows],
CASE INDEXPROPERTY( i.id , i.name , 'IsClustered') WHEN 1 THEN 'YES' ELSE 'NO' END AS [Clustered Index],
CASE INDEXPROPERTY(i.id,i.name,'IsUnique') WHEN 1 THEN 'YES' ELSE 'NO' END AS [Unique Index],
STATS_DATE( i.id , i.indid ) AS [Date Last Updated]
FROM sysindexes AS i
WHERE OBJECTPROPERTY( i.id, 'IsMSShipped' ) = 0 AND
INDEXPROPERTY( i.id , i.name , 'IsStatistics') = 0 AND
INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' ) = 0 AND
INDEXPROPERTY( i.id , i.name , 'IsHypothetical') = 0 AND
i.indid BETWEEN 1 And 250
ORDER BY [Table Name], [Index Name]
To view clustered indexes or non-clustered indexes only you can change the query by adding another filter in WHERE clause. The following query will only display clustered indexes.
SELECT
OBJECT_NAME( i.id ) AS [Table Name],
i.name AS [Index Name],
i.keycnt as [Total Keys],
i.rows as [Total Rows],
CASE INDEXPROPERTY( i.id , i.name , 'IsClustered') WHEN 1 THEN 'YES' ELSE 'NO' END AS [Clustered Index],
CASE INDEXPROPERTY(i.id,i.name,'IsUnique') WHEN 1 THEN 'YES' ELSE 'NO' END AS [Unique Index],
STATS_DATE( i.id , i.indid ) AS [Date Last Updated]
FROM sysindexes AS i
WHERE
INDEXPROPERTY( i.id , i.name , 'IsClustered') = 1 AND
OBJECTPROPERTY( i.id, 'IsMSShipped' ) = 0 And
INDEXPROPERTY( i.id , i.name , 'IsStatistics') = 0 AND
INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' ) = 0 AND
INDEXPROPERTY( i.id , i.name , 'IsHypothetical') = 0 And
i.indid BETWEEN 1 And 250
ORDER BY [Table Name], [Index Name]
Clustered Index:
An index in which the logical order of the key values determines the physical order of the corresponding rows in a table. Thats why we cant have more than one clustered index on table.
Non Clustered Index:
A nonclustered index, the physical order of the data rows is independent of their indexed order, you can create 249 non clustered indexes with different combination of keys depending on your needs.
Unique Index:
A unique index is one in which rows are not permitted to have the same index key value.
Check books online for more information on indexes.