SQL Server two columns in index but query on only one

41zrol4v 于 2023-08-02 发布在 SQL Server

关注(0)|答案(1)|浏览(132)

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'

sql-server

来源：https://stackoverflow.com/questions/61679996/sql-server-two-columns-in-index-but-query-on-only-one

1条答案

按热度按时间

xzv2uavs1#

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;

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.

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server two columns in index but query on only one

1条答案

相关问题

热门标签

最新问答