Have Geek Brain

Why do you have a grouped composite index when none of the fields are used together?

In an old 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 point is, as far as I know, none of these fields are used together in the WHERE clause. And there is no reason to use any of them together. Is there a reason to have a clustered index like this?

+1


a source to share


1 answer


One reason I could think of is to use these fields in many select operations (not necessarily in the where clause), this can serve as a coverage index.

For example, if you have a lot of queries like:



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

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

+1


a source






More articles:


All Articles