Optimizing SQL Performance with Indexing Strategies for Faster Queries

A library book story to begin

Imagine you walk into a huge library in your town. There are lakhs of books on the shelves. You want one book called "The Story of Mango Trees."

There are two ways to find it.

The first way: you start at the very first shelf and read the title of every single book, one by one, until you finally reach yours. With lakhs of books, this could take you the whole day. You might even faint from tiredness.

The second way: you go to the small card drawer near the door. The cards are sorted neatly by title in alphabetical order. You flip to "M," find "Mango," and the card says "Shelf 42, Row 3." You walk straight there and pick up your book in one minute.

That little sorted card drawer is an index. The books on the shelves are your table full of rows. Reading every book is a full table scan. Using the card drawer is an index seek.

This whole article is about teaching the database to use card drawers instead of reading every book. When you do this well, queries that took 30 seconds can finish in a blink. Let us learn how, step by step, with simple pictures and C# code.

What an index really is

An index is a separate, sorted helper structure that the database builds and keeps for you. It usually stores a copy of one or more columns, kept in sorted order, plus a pointer to where the full row lives.

Because the index is sorted, the database can use a clever trick. Instead of checking rows one by one, it can jump to the middle, see if your value is higher or lower, and keep cutting the search in half. This is called a B-tree search, and it is incredibly fast even on millions of rows.

Here is the difference between scanning a table and seeking through an index.

The card drawer in the library does not hold the actual books. It holds sorted cards that point to shelves. In the same way, an index does not always hold all your data. It holds sorted keys that point back to your rows. Keep that picture in your head and the rest will be easy.

The two main kinds of indexes

SQL Server, PostgreSQL, and most databases give you two big families of indexes. Knowing the difference is the single most useful thing in this article.

Clustered index: the sorted shelves themselves

A clustered index decides the actual physical order of the rows in the table. The table rows are stored on disk sorted by this key. Think of it as arranging the real books on the shelves in title order.

Because the books can only be in one order on the shelves, a table can have only one clustered index. In SQL Server, when you set a primary key, it usually becomes the clustered index automatically.

Nonclustered index: the extra card drawers

A nonclustered index is a separate sorted structure. It does not move the table rows. It is a copy of the chosen column or columns, kept sorted, with a pointer back to the real row. You can have many card drawers: one sorted by author, one by year, one by subject. So a table can have many nonclustered indexes.

Here is a quick side-by-side comparison so you can choose the right one.

A good default is this: make the clustered index your primary key (a small, ever-increasing number), and add nonclustered indexes for the other columns your queries search on.

How to create indexes

Let us create a simple table and add indexes to it. Here is plain T-SQL for SQL Server.

// This is T-SQL you run against SQL Server.
// Create a table. The primary key becomes the clustered index.
CREATE TABLE Orders
(
    Id          INT IDENTITY PRIMARY KEY,   // clustered index here
    CustomerId  INT NOT NULL,
    Status      NVARCHAR(20) NOT NULL,
    OrderDate   DATETIME2 NOT NULL,
    Total       DECIMAL(10,2) NOT NULL
);
 
// A nonclustered index on CustomerId.
// This helps queries that filter by CustomerId.
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
    ON Orders (CustomerId);

Now a query like the one below can use the new card drawer instead of reading the whole table.

// T-SQL: this query can now seek using IX_Orders_CustomerId.
SELECT Id, Status, OrderDate, Total
FROM Orders
WHERE CustomerId = 42;

If you use Entity Framework Core in .NET, you usually do not write CREATE INDEX by hand. You declare the index in your model and a migration generates the SQL for you.

// EF Core: declare indexes in OnModelCreating.
// .NET 10 and EF Core 10 syntax shown here.
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>(entity =>
    {
        // A simple nonclustered index on one column.
        entity.HasIndex(o => o.CustomerId)
              .HasDatabaseName("IX_Orders_CustomerId");
 
        // A composite index on two columns, in order.
        entity.HasIndex(o => new { o.CustomerId, o.OrderDate })
              .HasDatabaseName("IX_Orders_Customer_Date");
    });
}

EF Core then writes the matching CREATE INDEX statements into your migration. You get the same indexes, but kept in your C# model where they are easy to see and review.

Composite indexes: order of columns matters

A composite index is an index on more than one column. The order you list the columns is very important. Think of a telephone directory sorted first by last name, then by first name. It is brilliant for finding "Sharma, Aarav." It is useless for finding everyone whose first name is "Aarav," because the book is not sorted by first name at all.

So the rule is: a composite index helps most when your query filters on the leftmost columns first.

For our Orders table, the index IX_Orders_Customer_Date on (CustomerId, OrderDate) is excellent for this query:

// T-SQL: filters on the leftmost column, then sorts by the next one.
SELECT Id, Total
FROM Orders
WHERE CustomerId = 42
ORDER BY OrderDate DESC;

It filters by CustomerId (the leftmost column) and then needs OrderDate in order, which the index already provides. The database does almost no extra work.

But a query that filters only by OrderDate and ignores CustomerId cannot seek efficiently with this index, because OrderDate is the second column, not the first. For that you would need a different index that leads with OrderDate.

Covering indexes: the photocopy trick

Here is a problem. A nonclustered index holds only the key columns. If your query asks for more columns, the database finds the row in the index, then has to jump back to the main table to fetch the rest. This extra jump is called a key lookup, and doing it for thousands of rows is slow.

The fix is a covering index. You add the extra columns the query returns as included columns. Now the index holds everything the query needs. The database never jumps back to the table. It is like a card drawer where each card already has a photocopy of the whole book page you wanted.

// T-SQL: a covering index.
// Key column = what we search by.
// INCLUDE = extra columns the query returns.
CREATE NONCLUSTERED INDEX IX_Orders_Status_Covering
    ON Orders (Status)
    INCLUDE (CustomerId, OrderDate, Total);

Now this query is fully "covered" and needs no key lookup at all:

// T-SQL: every column here is in the index above, so no trip to the table.
SELECT CustomerId, OrderDate, Total
FROM Orders
WHERE Status = 'Pending';

In EF Core you express the included columns with IncludeProperties:

// EF Core: a covering index with included columns.
entity.HasIndex(o => o.Status)
      .IncludeProperties(o => new { o.CustomerId, o.OrderDate, o.Total })
      .HasDatabaseName("IX_Orders_Status_Covering");

This single trick is often the biggest, easiest win in query tuning. When you see "key lookup" in an execution plan and it is slow, a covering index usually fixes it.

Reading the execution plan

How do you know if your index is even being used? You ask the database to show its execution plan. The plan is the step-by-step recipe the engine chose to run your query. It tells you whether it did a fast seek or a slow scan.

In SQL Server Management Studio, click "Include Actual Execution Plan" and run your query. Look for these words:

The flow of tuning a slow query usually looks like this.

Always measure before and after. Never add an index just because it "feels" right. Let the execution plan and the timing be your judge.

When indexes hurt you

Indexes are not free. This is the part many beginners miss, so read it twice.

Every time you INSERT, UPDATE, or DELETE a row, the database must update the table and every index that touches the changed columns. Going back to the library: every time a new book arrives, you must add a new card to every card drawer it belongs in. Ten drawers means ten new cards for one book.

So indexes make reads faster but make writes a little slower and use more disk space. The art is balance.

Here are simple habits that keep you out of trouble:

• Index the columns you actually filter, join, and sort on. Not every column.
• Avoid duplicate indexes. If you already have one on (CustomerId, OrderDate), you usually do not also need one on just (CustomerId).
• Watch out for low-variety columns. An index on a column that is only "Yes" or "No" rarely helps, because half the table still matches.
• Drop indexes nobody uses. Most databases can tell you which indexes are never touched.
• Remember that very wide keys (like long text) make every index bigger and slower. Prefer small, simple keys.

A real before-and-after

Picture a table with 5 million orders and this query running all day:

// T-SQL: the everyday "show me this customer's recent orders" query.
SELECT TOP 20 Id, Status, OrderDate, Total
FROM Orders
WHERE CustomerId = 42
ORDER BY OrderDate DESC;

With no helpful index, the database does a clustered index scan: it reads all 5 million rows, throws most away, then sorts. That can take seconds and hammer the disk.

Add this one index:

// T-SQL: leads with the filter column, then the sort column,
// and covers the returned columns.
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date_Cover
    ON Orders (CustomerId, OrderDate DESC)
    INCLUDE (Status, Total);

Now the engine seeks straight to CustomerId = 42, the rows are already in OrderDate order, and every returned column is inside the index. No scan, no sort, no key lookup. The same query finishes in a few milliseconds. That is the power of one well-chosen index.

A short checklist before you ship

Before you call an index "done," walk through these questions:

• Does the index lead with the column my WHERE clause filters on?
• If I sort, does the index already provide that order?
• Are the columns my SELECT returns covered, so there is no key lookup?
• Did I check the execution plan and see an Index Seek?
• Did I avoid creating a duplicate of an index that already exists?
• Did I confirm writes are still fast enough after adding it?

If you can answer yes to these, your query is in great shape.

Quick recap

• An index is a sorted helper, like the card drawer in a library, that lets the database jump straight to rows instead of reading the whole table.
• A clustered index sorts the actual table rows, and you get only one per table. It is usually your primary key.
• A nonclustered index is a separate sorted copy with a pointer back to the row, and you can have many.
• In a composite index, column order matters: put the column you filter on first.
• A covering index uses INCLUDE to hold every column the query needs, which removes the slow key lookup.
• Always read the execution plan. Aim for an Index Seek, not a Table Scan.
• Indexes speed up reads but slow down writes and use storage, so add only the ones your important queries need and drop the rest.
• In EF Core, declare indexes with HasIndex, IncludeProperties, and migrations instead of hand-written SQL.

References and further reading

• Clustered and Nonclustered Indexes Described — Microsoft Learn
• SQL Server Index Architecture and Design Guide — Microsoft Learn
• Reorganize and Rebuild Indexes — Microsoft Learn
• Columnstore Indexes Design Guidance — Microsoft Learn
• SQL101: Indexing Strategies for SQL Server Performance — Paul S. Randal (SQLskills)