SQL Interview Question – Which index will be used?

In a recent technical interview I was asked a question about multicolumn indexes. The question says that we have table with
3 columns say column a, column b and column c. Now we create Nonclustered index on b,c & a,b. Now there a query like
select name from testIndexes WHERE b LIKE ‘Pawan’ , so which index it will use. I gave him the correct answer. Lets create a sample
table with some data and indexes.

--

CREATE TABLE testIndexes
(
a INT
,b VARCHAR(10)
,c VARCHAR(10)
)
GO

INSERT INTO testIndexes VALUES (1,'Pawan','Nisha'),(2,'Nisha','Isha'),(3,'Isha','Pawan')

CREATE NONCLUSTERED INDEX Ix_testIndexes1 ON testIndexes (b,c)
CREATE NONCLUSTERED INDEX Ix_testIndexes2 ON testIndexes (a,b)

SELECT b FROM testIndexes WHERE b LIKE 'Pawan'

--

Execution plan

Index – Seek Predicate

Now it is clear that it is using the first index, now the question is why the optimizer is using the first index. This is because the histogram builds on the first column. Now lets force the second and check out the execution plan

SELECT b FROM testIndexes
WHERE b LIKE 'Pawan'
GO

SELECT b FROM testIndexes WITH(INDEX(Ix_testIndexes2))
WHERE b LIKE 'Pawan'
GO

--

Execution plans

Index Comparisions

In the first case it is using index seek and in the second case it is using index scan. I hope you have enjoyed the article. In the next post I will explain one more interview question asked to me in one of technical interviews I faced recently. Thanks for reading !

-Pawan Khowal

MSBISkills.com