How to View and Understand Indexes on a SQL Server Table

The fastest way to check indexes on a table

To see all indexes on a specific table in SQL Server, run this query in SQL Server Management Studio (SSMS):

SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('YourTableName')

Replace 'YourTableName' with the actual name of your table. This returns every index attached to that table, including the clustered index and any non-clustered indexes. The results show the index name, type, and whether it is disabled or filtered.

If you need more detail — like which columns make up each index — add this query instead:

SELECT i.name AS IndexName, c.name AS ColumnName, ic.key_ordinal FROM sys.indexes i JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE i.object_id = OBJECT_ID('YourTableName') ORDER BY i.name, ic.key_ordinal

This shows you the index name, the column names in order, and their position within the index.

Key Takeaways

The sys.indexes system view shows all indexes on a table, including their names and whether they are active or disabled.
Joining sys.indexes with sys.index_columns and sys.columns reveals which specific columns belong to each index and in what order.
SQL Server Management Studio's Object Explorer also displays indexes visually under each table's Indexes folder.
A clustered index determines the physical order of rows; a table can have only one, but many non-clustered indexes.
The query results include system-generated indexes that SQL Server creates automatically, not just ones you defined yourself.

Using SQL Server Management Studio's graphical view

If you prefer not to write a query, SSMS shows indexes visually. In Object Explorer on the left, expand your database, then expand Tables, then right-click the table name and select Properties. Click the Indexes and Keys page to see a list of all indexes on that table.

This view is useful for a quick look, but the query method gives you more control over what information you see and how you filter it. The graphical view also becomes slow if your table has many indexes.

Understanding index types and what they mean

SQL Server reports each index with a type number. Type 0 is a heap (no index at all), type 1 is a clustered index, and type 2 is a non-clustered index. A table can have one clustered index and up to 999 non-clustered indexes, though in practice most tables have far fewer.

The clustered index is the primary sort order of the table itself. Every other index is non-clustered and points back to the clustered index to retrieve the full row. If a table has no clustered index, it is called a heap, and SQL Server stores rows in no particular order.

When you check indexes, pay attention to the is_disabled column. A disabled index still takes up disk space but is not used by queries. Rebuilding or dropping a disabled index is often the right move if you are trying to improve performance or free up space.

Checking index statistics and fragmentation

Knowing an index exists is one thing; knowing whether it is fragmented is another. Fragmentation slows down queries because SQL Server has to read scattered pages instead of contiguous ones. To check fragmentation, use this query:

SELECT i.name AS IndexName, ips.avg_fragmentation_in_percent FROM sys.indexes i JOIN sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('YourTableName'), NULL, NULL, 'LIMITED') ips ON i.object_id = ips.object_id AND i.index_id = ips.index_id WHERE ips.avg_fragmentation_in_percent > 0 ORDER BY ips.avg_fragmentation_in_percent DESC

Fragmentation above 10 percent usually means rebuilding the index. Between 10 and 30 percent, reorganizing is often enough. Below 10 percent, the index is healthy and does not need maintenance.

Finding indexes by column name

Sometimes you need to know which indexes use a specific column. This query finds all indexes that include a given column:

SELECT i.name AS IndexName, c.name AS ColumnName FROM sys.indexes i JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE i.object_id = OBJECT_ID('YourTableName') AND c.name = 'YourColumnName'

Replace 'YourColumnName' with the column you are looking for. This is useful when you are deciding whether to drop a column or when you want to understand which indexes might be affected by a schema change.

Viewing index properties and constraints

Beyond the basic index list, you may want to know whether an index is unique, whether it filters rows, or whether it includes extra columns. This expanded query shows more detail:

SELECT i.name AS IndexName, i.type_desc AS IndexType, i.is_unique AS IsUnique, i.filter_definition AS FilterDefinition, i.is_disabled AS IsDisabled FROM sys.indexes i WHERE i.object_id = OBJECT_ID('YourTableName') AND i.index_id > 0

The is_unique column shows whether the index enforces uniqueness. The filter_definition column shows any WHERE clause the index uses to index only certain rows. The is_disabled column flags indexes that exist but are not being used.

A filtered index can save space and improve performance by indexing only the rows you actually query. For example, an index on an "archived" column might filter to WHERE archived = 0, so it does not waste space on rows you never search.

Frequently Asked Questions

Can I see indexes on a table without writing a query?

Yes. In SQL Server Management Studio, expand your database in Object Explorer, expand Tables, find your table, and expand the Indexes folder beneath it. Each index appears as a separate item. Right-click any index to view its properties, including which columns it contains and whether it is unique or filtered.

What is the difference between a clustered and non-clustered index?

A clustered index determines the physical order in which rows are stored on disk. A table can have only one. A non-clustered index is a separate structure that points to rows; a table can have many. When you query a non-clustered index, SQL Server uses it to find the row, then follows the clustered index to retrieve the full data.

How do I know if an index is actually being used?

Query sys.dm_db_index_usage_stats to see how many times each index has been read or written. If an index shows zero reads over time, it is a candidate for deletion. Keep in mind that this view resets when SQL Server restarts, so check it over a representative time period.

What does it mean if an index is disabled?

A disabled index still exists and takes up disk space, but SQL Server does not use it for queries. Indexes are often disabled before deletion to test whether removing them hurts performance. If performance stays the same, you can drop the index permanently. If it gets worse, you can re-enable it.

Can I check indexes on multiple tables at once?

Yes. Remove the WHERE clause that filters by table name, or modify it to search for multiple tables. For example, WHERE object_id IN (OBJECT_ID('Table1'), OBJECT_ID('Table2')) will show indexes on both tables in one result set. This is useful for comparing index strategies across related tables.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.