SQL Server

SQL Server Indexes: A Practical Guide for Access Developers

Indexes in SQL Server work like indexes in Access — but with more types, more control, and bigger performance implications. Here is what you need to know after migrating.

A
Anthony Passeri, MBA
••8 min read
SQL Server Indexes: A Practical Guide for Access Developers

If you have read the post on Access query optimization, you already understand the basic concept: indexes let the database engine find rows quickly instead of scanning every row in a table. SQL Server takes that concept much further.

SQL Server has more index types, more configuration options, and more sophisticated query optimization than Access. Understanding indexes is one of the most important skills for any developer working with SQL Server — and one of the biggest performance levers available to you.

Clustered vs. Non-Clustered Indexes

This is the first concept that surprises Access developers. Access has one type of index. SQL Server has two fundamentally different types.

Clustered Index

A clustered index determines the physical order of rows in the table. The table's data is stored sorted by the clustered index key.

Every table can have exactly one clustered index. When you create a primary key in SQL Server, it creates a clustered index on that key by default.

Think of a clustered index like a phone book: the data itself is sorted by last name. Finding "Smith" is fast because you can go directly to the S section. The data and the index are the same thing.

Implications:

  • Range queries on the clustered index key are very fast (find all orders between two dates)
  • Inserts into the middle of the sort order require moving data — for high-insert tables, a sequential key (like an identity integer) is better than a date or alphabetical key
  • There can only be one — choose it carefully

Non-Clustered Index

A non-clustered index is a separate structure that stores the index key values and pointers to the actual rows. The table data is not reordered.

Think of it like the index at the back of a book: it lists terms alphabetically with page numbers, but the book's content is not rearranged.

A table can have up to 999 non-clustered indexes (though in practice, more than 10–15 is unusual and can hurt write performance).

-- Create a non-clustered index on LastName
CREATE NONCLUSTERED INDEX IX_Customers_LastName
ON Customers (LastName);

-- Create a composite non-clustered index
CREATE NONCLUSTERED INDEX IX_Orders_CustomerDate
ON Orders (CustomerID, OrderDate DESC);

Creating Indexes in SQL Server

-- Basic syntax
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON table_name (column1 [ASC | DESC], column2 [ASC | DESC], ...);

-- Examples
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON Orders (CustomerID);

CREATE NONCLUSTERED INDEX IX_Orders_Status_Date
ON Orders (Status, OrderDate DESC);

-- Unique index (enforces uniqueness like a unique constraint)
CREATE UNIQUE NONCLUSTERED INDEX UX_Customers_Email
ON Customers (Email)
WHERE Email IS NOT NULL;  -- filtered index: only index non-null emails

Covering Indexes: Eliminating Key Lookups

When SQL Server uses a non-clustered index to find rows, it then has to go back to the clustered index (the table data) to retrieve any columns not in the index. This second lookup is called a key lookup, and it adds overhead.

A covering index includes all the columns a query needs, eliminating the key lookup:

-- Query that retrieves CustomerID, OrderDate, and TotalAmount
SELECT CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE Status = 'Pending';

-- Non-covering index: SQL Server finds matching rows, then does key lookups for OrderDate and TotalAmount
CREATE NONCLUSTERED INDEX IX_Orders_Status
ON Orders (Status);

-- Covering index: all needed columns are in the index, no key lookup needed
CREATE NONCLUSTERED INDEX IX_Orders_Status_Covering
ON Orders (Status)
INCLUDE (CustomerID, OrderDate, TotalAmount);

The INCLUDE clause adds columns to the index leaf level without making them part of the sort key. This is the most common way to create covering indexes.

Filtered Indexes

A filtered index covers only a subset of rows — those matching a WHERE condition. For tables where queries typically focus on a specific subset of data, filtered indexes are smaller and faster than full-table indexes.

-- Index only active customers (assuming most queries filter on IsActive = 1)
CREATE NONCLUSTERED INDEX IX_Customers_Active_LastName
ON Customers (LastName, FirstName)
WHERE IsActive = 1;

-- Index only open orders
CREATE NONCLUSTERED INDEX IX_Orders_Open
ON Orders (CustomerID, OrderDate)
WHERE Status IN ('Pending', 'Processing', 'Shipped');

Filtered indexes are a SQL Server feature with no Access equivalent. They are particularly useful for tables with a large proportion of "inactive" or "historical" rows that are rarely queried.

When to Add an Index

Add an index when:

  • A column appears frequently in WHERE clauses
  • A column is used in JOIN conditions (especially foreign keys)
  • A column is used in ORDER BY clauses for large result sets
  • A query is slow and the execution plan shows a table scan on a large table

Do not index everything. Every index adds overhead to INSERT, UPDATE, and DELETE operations — the index must be updated whenever the data changes. A table with 20 indexes will have fast reads but slow writes.

A practical rule: start with indexes on primary keys (automatic), foreign keys, and the most frequently queried filter columns. Add more only when query analysis shows they are needed.

Reading Execution Plans

SQL Server Management Studio can show you the execution plan for any query — a visual diagram of how SQL Server will execute it. This is the most powerful tool for index optimization.

To see the execution plan: in SSMS, click "Include Actual Execution Plan" (Ctrl+M), then run your query. The plan appears in a tab below the results.

Look for:

  • Table Scan or Clustered Index Scan — reading every row. Usually indicates a missing index.
  • Key Lookup — going back to the table after using a non-clustered index. Consider a covering index.
  • Sort — sorting a large result set. An index with the right sort order can eliminate this.
  • Thick arrows — wide arrows indicate large row counts moving between operations. These are often the bottleneck.

SSMS also shows "Missing Index" suggestions directly in the execution plan. These are not always correct, but they are a useful starting point.

Index Maintenance

Indexes fragment over time as rows are inserted, updated, and deleted. Fragmented indexes are slower than fresh ones.

SQL Server provides two maintenance operations:

Reorganize — defragments the index online (the table remains accessible). Use for indexes with 10–30% fragmentation.

Rebuild — drops and recreates the index. More thorough than reorganize. Can be done online (Enterprise edition) or offline (Standard edition, which locks the table during rebuild). Use for indexes with >30% fragmentation.

-- Check fragmentation
SELECT
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Reorganize a specific index
ALTER INDEX IX_Orders_CustomerID ON Orders REORGANIZE;

-- Rebuild a specific index
ALTER INDEX IX_Orders_CustomerID ON Orders REBUILD;

-- Rebuild all indexes on a table
ALTER INDEX ALL ON Orders REBUILD;

For production databases, schedule index maintenance as a SQL Server Agent job — typically weekly for reorganize, monthly for rebuild, depending on data volume and insert/update frequency.

Migrating Access Indexes to SQL Server

When you migrate from Access to SQL Server (using SSMA or manually), your Access indexes are typically migrated automatically. But it is worth reviewing them:

  • Access indexes on primary keys become clustered indexes in SQL Server
  • Access indexes on other fields become non-clustered indexes
  • Access does not have covering indexes or filtered indexes — these are opportunities to add after migration

After migrating, run your most important queries with execution plans enabled. Look for table scans on large tables and key lookups that could be eliminated with covering indexes. The performance improvements from targeted index additions are often dramatic.

The Bottom Line

Indexes are the single most impactful performance tool in SQL Server. A well-indexed database handles large tables and complex queries efficiently. A poorly indexed database struggles with queries that should be fast.

The good news: you do not need to get indexing perfect on day one. Start with the basics — primary keys, foreign keys, common filter columns — and add more as query analysis reveals bottlenecks. SQL Server's execution plan tools make it straightforward to identify exactly where indexes are needed.

Work With Access Evolved

If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.

Migrating to SQL Server and want to get your indexing strategy right from the start? Schedule a free architecture call.

Schedule a Free Call →

Thinking about migrating?

Ready to move beyond Microsoft Access?

Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.

A

Written by

Anthony Passeri, MBA

Access Expert and Editor

Bridging the gap between Access and the Future

Explore Topics

#SQL Server#indexes#performance#T-SQL#Access migration

Keep reading

More on SQL Server