Understanding SQL Server 2000 index types

Posted: (EET/GMT+2)

 

I have been working with SQL Server 2000 lately, and have looked into indexing in more detail. Indexes are one of the simplest ways to improve query performance. This post is a quick overview of the most common index types.

Shortly put, an index helps SQL Server find rows faster without scanning the whole table. For larger tables (thousands of rows), this can make a big difference in the speed in which results are returned.

One common distinction in indexes is the one between unique and non-unique indexes. Let's see how these differ:

UNIQUE INDEX:

  • Ensures all values in the indexed column(s) are different
  • Fails on insert or update if duplicates are attempted.

NON-UNIQUE INDEX:

  • Allows duplicate values
  • Used mainly for performance, not constraints.

You can create a unique index like this: 

CREATE UNIQUE INDEX IX_Customers_Email
ON dbo.Customers (Email);

Another important distinction is clustered vs. non-clustered indexes.

CLUSTERED INDEX:

  • Defines the physical order of data in the table
  • Only one per table
  • Often created on the primary key.

NON-CLUSTERED INDEX:

  • Separate structure from the table data
  • Can have multiple per table
  • Contains pointers to the actual data rows.

Here is an example: 

CREATE CLUSTERED INDEX IX_Customers_Id
ON dbo.Customers (CustomerId);

CREATE NONCLUSTERED INDEX IX_Customers_Name
ON dbo.Customers (Name);

In practice, most tables have:

  • One clustered index (often the primary key)
  • A few non-clustered indexes for common queries

A few practical tips:

  • Choose clustered indexes carefully. They affect how data is stored physically on disk.
  • Use unique indexes when the data must be unique anyway.
  • Avoid creating too many indexes. Each index adds overhead for inserts and updates.
  • Create indexes based on actual query patterns, not guesses. Reviewing your queries helps.

Also keep in mind that indexes improve read performance, but they can slow down write operations because SQL Server needs to update the index structures as well.

So the practical takeaway is simple: use a clustered index to define data order, and add a few targeted non-clustered indexes for your most important queries.