What Is Index in SQL Server?

When I first started working with SQL Server, one of the things that confused me was performance. Why do some queries return results almost instantly, while others take forever? The answer, in many cases, comes down to indexes.

In this post, I’ll explain what is index in SQL Server, why it matters, the different types of indexes, how you can get a list of existing indexes, and how to create your own, using the Northwind database as an example.

What Is Index in SQL Server?

Think of an index like the index of a book. If you’re looking for a topic in a 500-page book, you don’t flip through every single page. You go to the back of the book, check the index, and jump straight to the page number you need.

SQL Server works in a similar way. Without an index, SQL has to scan the entire table to find matching rows. With an index, SQL can locate rows much faster. So, an index in SQL Server is a database object that improves the speed of data retrieval.

How Many Types of Indexes Are in SQL Server?

SQL Server supports several types of indexes, but let’s focus on the most common ones you’ll actually use:

1. Clustered Index
   • Determines the physical order of data in the table.
   • A table can have only one clustered index.
   • By default, primary keys automatically create a clustered index.
2. Non-Clustered Index
   • Creates a separate structure that points back to the table rows.
   • A table can have many non-clustered indexes.
   • Best used on columns often used in WHERE, JOIN, or ORDER BY.

There are other types (like unique, filtered, XML, columnstore, etc.), but if you’re just starting out, focus on clustered and nonclustered indexes, since they are used the most.

How to Get List of Indexes in SQL Server

If you’re curious about what indexes already exist in the Northwind database, you can use this query:

select 
    t.name as table_name,
    ind.name as index_name,
    ind.type_desc as index_type
from sys.indexes ind
join sys.tables t 
    on ind.object_id = t.object_id
where ind.is_primary_key = 0
and ind.is_unique = 0
and ind.name is not null
order by t.name, ind.name

This will show you all non-primary key indexes in your database, along with their type.

NOTE: When running the above query, make sure you select the proper database:

How to Create Index in SQL Server

Let’s say you’re running queries on the Orders table in Northwind, and you often filter by ShipCity. Without an index, SQL has to scan the entire Orders table each time.

Here’s how you can use the SQL create index statement on ShipCity column:

create nonclustered index ix_orders_shipcity
on orders (shipcity)

This is what it looks like in SSMS:

Now, when you run a query like this:

select *
from orders
where shipcity = 'Graz'

SQL Server will use the index to quickly find rows for that customer:

OK…our database is not that big, so you will probably not notice the difference, but you will definitely see it when you are working with a table that contains millions of records.

Managing Indexes

Over time, indexes can become fragmented. SQL Server provides commands to rebuild or reorganize them:

-- Rebuild all indexes on a table
alter index all on orders
rebuild

-- Or reorganize (lighter operation)
alter index all on orders
reorganize

Regular index maintenance keeps your queries running smoothly.

Final Thoughts

Indexes are one of the easiest ways to speed up your SQL Server queries.

• They work like the index of a book, helping SQL jump directly to the data you need.
• The two main types you’ll use are clustered and non-clustered indexes.
• You can check existing indexes with a query provided above.
• Creating an index on frequently queried columns (like CustomerID in Orders table) can dramatically improve performance.

Make sure you visit Microsoft’s official web page on indexes, so you can learn about other types of indexes.

If want to learn more about SQL, make sure you check my previous blog post on How to Do Mathematical Operations in SQL.

If you would like to learn more about programming SQL queries, make sure you buy this book: T-SQL Fundamentals (Developer Reference)