Articles → SQL SERVER → Covering index in SQL server

Covering index in SQL server






Technical knowledge













Non-clustered index and key loop ups




Column NameData Type
Idint
Titlevarchar(300)
DeadlineDate
Assigned Tovarchar(100)






Picture showing the logical representation of table having non-clustered index applied on the columns

Click to Enlarge



select Title from Task where Title = 'Task 1'




Picture showing the execution plan of the select query having columns where non-clustered index is applied

Click to Enlarge



select Title, Deadline from Task where Title = 'Task 1'




Picture showing the execution plan of the select query having columns where non-clustered index is not applied

Click to Enlarge


Covering index






CREATE NONCLUSTERED INDEX non_clustered_index_name ON table_name(column_name) include (list_of_included_columns)




CREATE NONCLUSTERED INDEX ix_Title ON Task(Title) include (Deadline)




Picture showing the select query with the column where covering index is applied

Click to Enlarge


Posted By  -  Karan Gupta
 
Posted On  -  Friday, April 3, 2015

Query/Feedback


Your Email Id  
 
Subject 
 
Query/FeedbackCharacters remaining 250