Index columns order and Scan instead of seek

Question

Please consider Customer table in Northwind Database:

I add an index on 3 columns:

CREATE NONCLUSTERED INDEX [idx_1] ON [dbo].[Customers]
    ([CompanyName] ASC, [City] ASC, [Country] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
      ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

and I tested these queries:

1) SELECT CompanyName,city,country FROM [Northwind].[dbo].[Customers] where CompanyName='a' and city='b' and country='c'

2) SELECT CompanyName,city,country FROM [Northwind].[dbo].[Customers] where country='c' and CompanyName='a' and city='b'

3) SELECT CompanyName,city,country FROM [Northwind].[dbo].[Customers] where CompanyName='a' and country='c'

4) SELECT CompanyName,city,country FROM [Northwind].[dbo].[Customers] where CompanyName='a' and city='b'

all of above queries use Index Seek.

Is there these predicates True?

A) If my index has N columns then all permutation of these N columns with equality to their values are using Index Seek and so columns order in Index does not matters when all columns participate on Where clause.

B) If my index has N columns (for example: Col1, Col2, Col3,...) then all these combinations inWhereclause useIndex Seek`:

B-1) Col1, Col2,
B-2) Col1, Col2, Col3
B-3) Col1, Col3, Col4
B-4) Col1, Col4, Col7

So Col1 is important to use Index Seek.

Please consider this query:

5) SELECT CompanyName,city,country, PostalCode FROM [Northwind].[dbo].[Customers] where CompanyName='a' and city='b' and country='c'

C) I added PostalCode in Select List and why does Index Seek convert to Index Scan? Why the Seeknot converted to Seek + Key Lookup?

Answer 1

1) The column order does matter because the index key is constructed using the column ordering specified. So an index seek (rather than scan) could only be efficiently made if the CompanyName column is included in your predicate (as it is in all your WHERE clauses`). The order in which the columns are listed in the predicate does not matter - the query optimiser will ascertain whether the requisite columns are specified in order to perform a seek.

2) Again, Col1 needs to be included as that is the first column specified for the index and thus forms the start of the index key.

3) Adding PostalCode means the index no longer covers all the columns needed in order to furnish the query. It is then up to the query optimiser to assess whether an index seek or an index scan is more efficient. This will depend on a number of factors, but an important one will be how many rows it estimates will be returned by the predicate and thus how many (expensive) key lookups it would have to perform relative to the expense of scanning the table (clustered index). It has clearly decided that in this case a scan of the clustered index is more efficient.