Which Indexes are required for SQL Server Query ?

'

A lot of time Jr. DBA/Developer and students are not clear which Index should be created for performance tuning a query. In my last 16 years as SQL DBA, I have taken more than 500 interviews at different levels and I have seen that even very Senior DBA are not clear at times as to what index will properly support my query.

So let’s see a very brief and clear explanation on what index will improve performance of my query and why:

We will use the below Sample queries:

CREATE TABLE [dbo].[Buildings](

       [Buildingid] [int] NOT NULL,

       [BuildingName] [varchar](50) NULL,

       [BuildingLocation] [varchar](50) NULL,

PRIMARY KEY CLUSTERED

(

       [Buildingid] ASC

)

)

--INSERT SOME RECORDS

INSERT INTO Buildings values (1,'Empire State','NYC')

INSERT INTO Buildings values (2,'Building2','NDLS')

INSERT INTO Buildings values (3,'Building3','NDLS')

INSERT INTO Buildings values (4,'Building4','Mumbai')

INSERT INTO Buildings values (5,'Building5','Mumbai')

INSERT INTO Buildings values (6,'Building6','Mumbai')

INSERT INTO Buildings values (7,'Building7','LA')

INSERT INTO Buildings values (8,'Building8','LA')

INSERT INTO Buildings values (9,'Building9','LAS')

INSERT INTO Buildings values (10,'Building10','LAS')

--Clustered Index Seek

select Buildingid FROM Buildings WHERE Buildingid=1

As you can see above, since we have where clause on Clustered Index and SELECT also have a clustered column, a clustered Index seek will happen.

--Clustered Index Scan

select Buildingid FROM Buildings WHERE BuildingName='Empire State'

Now since where clause has a non clustered column, there is no way for query optimizer to fetch data for BuildingName. So as shown above, entire clustered index will be scanned. NOTE that clustered index in nothing but table itself which is sorted based on some column, BuildingId here.

--Create a supporting Index

create index IDX_Buildings_BuildingName on Buildings(BuildingName)

--Scan Is converted to non clustered Index Seek

select Buildingid FROM Buildings WHERE BuildingName='Empire State'

As you can see above, Scan Is converted to non clustered Index Seek and new index is used.

--A scan or Lookup

select BuildingLocation FROM Buildings WHERE BuildingName='Empire State'

The above query will give Clustered Index Scan or a Key Lookup depending upon amount of data. Here data is less, so optimizer decides to do a scan . Why this Happened? Because BuildingLocation is not part of any index. So there is no way for optimizer to get this data, so it has to do a scan. To do away with this scan, we need to create a covering index.

--Covering Index

create index IDX_Buildings_BuildingName2 on Buildings(BuildingName) INCLUDE(BuildingLocation)

--A Perfect Seek now

select BuildingLocation FROM Buildings WHERE BuildingName='Empire State'

Now the index IDX_Buildings_BuildingName2 is used and a perfect seek happens because query has all supporting indexes now.

Now do we really need IDX_Buildings_BuildingName any more. Not actually. Figure out why not.