SQL Server
Indexes in SQL Server: A Beginner Introduction
Learn how SQL Server indexes help find rows, how clustered and nonclustered indexes differ, and why every index has a cost.
On this page
An index is a database structure that helps SQL Server locate rows using selected columns. Without a useful access path, the engine may need to examine many rows to answer a query. An index can reduce that search work for some queries, but it consumes storage and must be maintained when data changes. Indexes are helpful tools, not automatic fixes for every slow query.
This introduction uses dbo.Customers and dbo.Orders from the synthetic JaviBuildsLab database. It explains the basic vocabulary, shows how to create and inspect a simple index, and describes the tradeoffs you should understand before adding one. For a broader view of keys, revisit primary and foreign keys. This lesson deliberately avoids advanced tuning details such as fragmentation, statistics internals, key lookups, missing-index DMVs, and execution-plan analysis.
What an index does
Imagine a book with an index at the back. If you want a topic, you look up its entry and go to the listed pages rather than reading every page. A database index offers a related idea: it organizes key values with information that helps SQL Server find corresponding rows. The analogy has limits. A book index is static and human-directed; SQL Server chooses whether an available database index fits a query and maintains database structures as rows change.
For example, a query that finds one customer’s orders can use CustomerId as a search condition:
SELECT OrderId, CustomerId, OrderDate, Status, Amount
FROM dbo.Orders
WHERE CustomerId = 11
ORDER BY OrderDate DESC;An index whose leading key is CustomerId may help SQL Server locate those rows without examining every order. Whether it helps depends on the table size, the query, the distribution of values, and other available access paths. A tiny table can be faster to scan than to navigate through an index. The important beginner idea is that an index gives the engine another way to find data; it does not guarantee a performance improvement.
Clustered and nonclustered indexes
A clustered index organizes the table’s data rows by the clustered key. A table can have at most one clustered index because its rows can have only one clustered organization. The key is commonly chosen to support important access patterns, but there is no universal rule that it must be a particular column. “Clustered” does not mean that query results are guaranteed to appear in that order; add ORDER BY whenever output order matters.
A nonclustered index is a separate structure containing its key columns and a reference to the underlying row. A table can have multiple nonclustered indexes, each designed around different useful ways to locate rows. For example, a customer lookup and a date-range lookup may benefit from different keys. More indexes also mean more structures to store and maintain.
Some nonclustered indexes can include additional columns. Included columns are stored with index entries but are not part of the key order. They can help an index provide data needed by a query, but choosing them well requires understanding workload and storage tradeoffs. Treat included columns as a later design option, not a default checklist item.
Create a basic index
In the practice database, this example creates a nonclustered index on dbo.Orders.CustomerId:
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders (CustomerId);The name IX_Orders_CustomerId is a convention that communicates the table and key. ON dbo.Orders identifies the table, and (CustomerId) is the index key. This is a straightforward starting example for the earlier customer-orders relationship. In a real database, first check whether an equivalent index already exists and discuss schema changes with the database owner. Re-running CREATE INDEX against an existing name will fail rather than silently replace it.
Do not create an index simply because a column appears in a query. Consider how the query filters or joins, how much data exists, how frequently it runs, and whether an existing index already supports it. Index design belongs to the workload as a whole, not to one isolated statement.
Inspect indexes in SSMS
In Object Explorer, expand the database, Tables, and dbo.Orders. Expand the table’s Indexes folder to see its indexes. The primary-key index may appear there along with any nonclustered indexes. You can right-click an index or open table properties to inspect its definition and key columns. The exact SSMS labels can vary by version, but the table’s indexes are available from its object tree.
You can also use the table designer or object properties to understand which constraints and indexes exist. Before adding another index, compare its key columns with the existing definitions. A similarly named index is not necessarily equivalent; check the actual table and key definition. Make a note of which index you added in the practice database so it is easy to recognize during the exercise.
A primary key and an index are related, but different
A primary key is a constraint that enforces unique, non-NULL row identity. An index is a data structure that supports access to rows. SQL Server commonly creates an index to implement a primary key, depending on how the key is defined and what indexes already exist. The constraint is the rule; the index is a structure that may support that rule and help queries.
A unique index can enforce uniqueness, but it does not by itself declare that a column is the table’s primary key. Likewise, a foreign-key constraint expresses a relationship but does not automatically mean the referencing column has an index. See primary keys and foreign keys for the integrity rules. Keep the vocabulary separate when discussing schema design.
When a primary key is added, SQL Server often creates an index to support it. Depending on the table definition and existing indexes, that index may be clustered or nonclustered. The choice is part of the table’s design and can be specified; do not assume every primary key is clustered or that every table must use its primary key as the clustered key. Inspect the actual table in SSMS before describing its layout.
Every index has tradeoffs
Indexes take disk space and memory when used. They also add work to data modifications. When a row is inserted, deleted, or an indexed value changes, SQL Server may need to update the relevant index structures. This means writes can become more expensive as indexes are added. An index that rarely helps a read may still add ongoing storage and maintenance cost.
Too many indexes can make changes slower and complicate schema maintenance. A poorly chosen key may not help the queries you care about. A highly selective index can be useful for a narrow search, while an index on a column with only a few common values might not be useful for every query. These are workload-dependent decisions; there is no universal number of indexes that every table should have. Before adding one, write down the query pattern it is intended to help and the write activity it may affect. That makes the design easier to review later.
Indexes are not magic. They cannot correct an incorrect join, a missing filter, or a request for unnecessary columns. They do not remove the need to test with representative data. Start with clear query logic, then learn about tuning when a real workload shows that locating rows is a problem.
When to add an index
An index may be worth investigating when an important query repeatedly filters, joins, or sorts by a column and the table has enough rows for the access path to matter. Look at the table’s existing indexes first. Ask what workload will use the index and how often data changes. Test a proposed index in a safe environment with representative data, then observe whether the intended query benefits and whether writes or other queries regress.
For a beginner exercise, the CustomerId index illustrates how an index might support the filter used by a customer-orders query. It is not a guarantee that SQL Server will choose it in every environment. The optimizer evaluates available options; production decisions should be based on evidence from the workload. The broader SQL Server performance tuning workflow explains how to move from a symptom to evidence and validation. For a production example of indexing considerations, see how joins, updates, and deletes interact with indexes.
Practice and next step
Inspect the indexes on dbo.Orders in SSMS. Identify the primary key and any existing nonclustered indexes. In a disposable copy of JaviBuildsLab, run the CREATE NONCLUSTERED INDEX example once, refresh the Indexes folder, and inspect its key column. Compare what the index is meant to support with the query that filters on CustomerId. Do not assume that creating it makes every query faster or that a practice index should automatically be deployed elsewhere.
- Query needs rows
- Index locates matching keys
- Reads improve; writes cost
Keys protect identity and relationships; indexes provide access paths with tradeoffs. The capstone lesson connects these Foundations topics and points toward operational work such as running queries, blocking, monitoring, Query Store, and performance tuning: From SQL Basics to SQL Server Performance Tuning. You can revisit the SQL Server Foundations roadmap at any point.
Check what you learned
2 quick questions
SQL Server Foundations
Lesson 24 of 25
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetFind Missing IndexesUse SQL Server missing-index DMVs as a triage signal, rank candidates by estimated impact, and validate each recommendation before creating an index.Open cheat sheet
- RoadmapSQL Server FoundationsLearn how SQL Server indexes help find rows, how clustered and nonclustered indexes differ, and why every index has a cost.View roadmap
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.