Search code examples
indexingsql-server-2000clustered-indexcomposite-index

Why would you have a clustered composite index when none of the fields are used together?

In a legacy database (SQL Server 2000), we have a clustered index that looks like this:

CREATE CLUSTERED INDEX [IX_usr] ON [dbo].[usr] 
(
    [uid] ASC,
    [ssn] ASC,
    [lname] ASC
)

The thing is, as far as I know none of these fields are used together in a WHERE clause. Nor is there really any reason to use any of them together. Is there any reason to have a clustered index like this?

Solution

  • One reason I could think of is if you are using just those fields in many select statements (not necessarily in the where clause), it could serve as a covering index.

    For example, if you have lots of queries like this:

    SELECT uid, ssn, lname FROM usr WHER uid = x

    The query would never actually have to hit the table as all required fields are in the index.