SQL Server two columns in index but query on only one

user_4270611 0
Viewed 981

In legacy code I found an index as follows:

CREATE CLUSTERED INDEX ix_MyTable_foo ON MyTable
(
    id ASC,
    name ASC
)

If I understand correctly, this index would be useful for querying on column id alone, or id and name. Do I have that correct?

So it would possibly improve retrieval of records by doing:

select someColumn from MyTable where id = 4

But it would do nothing for this query:

select someColumn from MyTable where name = 'test'
indices sql-server sql
1 Answers

Yes, you are right. But in case when you have table with many columns:

A
B
C
D
..
F

where your primary key index is for example (A), if you have second index like (B,C), the engine may decide to use it if you are using query like this:

CREATE TABLE dbo.StackOverflow
(
    A INT
   ,B INT 
   ,C INT 
   ,D INT 
   ,E INT
   ,PRIMARY KEY (A)
   ,CONSTRAINT IX UNIQUE(B,C)
)

SELECT A     
      ,C 
FROM dbo.StackOverflow
WHERE C = 0;

enter image description here

So, if an index can be used as covering, the engine may use it even you are not interested in some of the columns, because it will decide that less work (less reads) will be performed.

In your case, it seems that a PK on id column is a better choice with combination with second index on the name column.

user_10803540
Related