Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
 Posted - 2012-03-15 : 14:19:34
|
| SQL 2008R2 SP1The execution plan of the SELECT statement produces an index seek (nonclustered)Why the query optimizer takes ix_LName_FName as the prefered index to produce an index seek?If I drop ix_LName_FName then it picks ix_FName_LName_EAddress?If I drop ix_FName_LName_EAddress then it picks ix_LName?I thought the optimizer will use ix_LName to produce an index seek because the SELECT statement has only one column (Last_Name) after the WHERE clause. I'm confussed. Could someone explain to me what is going on?----------------------------------------------------------------------------------------------------CREATE TABLE [dbo].[Customers]( [Customer_ID] [int] IDENTITY(1,1) NOT NULL, [Last_Name] [varchar](20) NOT NULL, [First_Name] [varchar](20) NOT NULL, [Email_Address] [varchar](50) NULL) ON [PRIMARY]drop index Customers.ix_CustomerIDCREATE CLUSTERED INDEX ix_CustomerID ON Customers(Customer_ID) drop index Customers.ix_LName_FNameCREATE INDEX ix_LName_FName ON Customers(Last_Name, First_Name) drop index Customers.ix_FName_LName_EAddress CREATE INDEX ix_FName_LName_EAddress ON Customers(Last_Name, First_Name, email_address)drop index Customers.ix_LName CREATE INDEX ix_LName ON Customers(Last_Name)select customer_id from customers WHERE Last_Name = 'smith' |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
Index seek question