当WHERE子句中只有一列时，SQL Server会使用复合索引吗? [英] Will SQL Server use a compound index when only a single column is in the WHERE clause?

问题描述

说我有一张桌子:

CREATE TABLE Users (
    Id INT IDENTITY (1, 1),
    FirstName VARCHAR(40),
    LastName VARCHAR(40)
)

查询通常在名字或姓氏上，但在名字 和 姓氏上em>.

Queries are usually on FirstName or LastName, but also on FirstName and LastName.

如果我在 FirstName 上创建了一个非聚集索引，而在 LastName 上创建了一个非聚集索引，则可以满足我的前两个查询.显然，SQL Server将对其他查询使用索引交点.

If I create a non-clustered index on FirstName and another on LastName, then my first two queries are catered for. Apparently, SQL Server will use index intersection for the other query.

或者，如果我在(FirstName)和(LastName，FirstName)上有索引者，SQL Server是否可以/仅将第二个索引用于 LastName 上的查询?

Alternatively, if I have indexees on (FirstName) and on (LastName, FirstName), can/does SQL Server use the second index for queries on just LastName as well as queries on both?

SQL Server是否从左到右或从右到左存储复合索引部分?换句话说:它将把密钥构建为LastNameFirstName或FirstNameLastName吗?还是可以随意选择一个?

Does SQL Server store compound index parts left-to-right or right-to-left? In other words: will it build the key as LastNameFirstName or FirstNameLastName? Or is it free to choose one arbitrarily?

推荐答案

SQL Server是否可以将索引(姓氏，名字)用于仅姓氏的查询以及两者的查询?

can/does SQL Server use the index (LastName, FirstName) for queries on just LastName as well as queries on both?

是的，数据库将使用索引(姓，名)来查询姓氏.但是，它将不仅将此索引用于FirstName查询.

Yes, the database will use the index (LastName, FirstName) for queries on LastName. It will not use this index for queries only on FirstName though.

它是从左到右还是从右到左存储复合索引部分?

Does it store compound index parts left-to-right or right-to-left?

存储位于 B树中.无论您认为它是从右到左还是从左到右存储，都只是一种有用的可视化工具，与实际的数据存储无关.

Storage is in a B-Tree. Whether you think of it as being stored right-to-left or left-to-right is just a useful visualization aid, and not related to the actual data storage.

这篇关于当WHERE子句中只有一列时，SQL Server会使用复合索引吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！