Does select query will get the benefit of the Index created?

Question

I have a table :

 CREATE TABLE EmployeeDetails
 (
   EmpID Integer Primary Key,
   EmpName Varchar(500),
   CreateDate DateTime,
   Location  Varchar(500)
  )

And, I created index on it :

 CREATE INDEX IX_IndexName1 ON EmployeeDetails (CreateDate,Location)

I want to know if the below mentioned query will get the benefit of the Index IX_IndexName1 ?

SELECT *
FROM   EmployeeDetails
WHERE  CreateDate >= '2017-05-01' AND CreateDate <= '2018-01-31'

@MitchWheat, do you mean SQL Server requires all selected columns to be in the index? — jarlh, Jan 18 '18 at 08:09

Mitch Wheat · Accepted Answer · 2018-01-18T08:39:59.677

Your query will be unlikely to use your current index due to the SELECT * selecting all columns in the table.

To be a covering index, an index must include all columns in the where clause, the select etc.

BUT it will only use an index if it is selective enough i.e. it filters the resulting data to a small enough subset of rows.

You should examine the actual execution plan (and make sure your statistics are up to date).

What are Covering Indexes and Covered Queries in SQL Server?

Your query should use the following index (but always check the actual plan):

CREATE INDEX IX_CreateDate_Location_I_EmpName_EmpID
ON EmployeeDetails (CreateDate)
INCLUDE (Location, EmpName, EmpID)

[Note: assuming your primary key column is the clustered index (it is by default if unspecified), you don't have to explicitly include EmpId in the INCLUDE clause.]

If you want an index that can be used by queries that also filter by Location as well as CreateDate, create this index:

CREATE INDEX IX_CreateDate_Location_I_EmpName_EmpID
ON EmployeeDetails (CreateDate, Location)
INCLUDE (EmpName, EmpID)

An index might be used where the leading column of the index, 'CreateDate', is used but not if the query does not include the leading column. For instance, this query won't be able to use either of the indexes I have defined (in the majority of cases):

SELECT *
FROM   EmployeeDetails
WHERE  Location = 'blah'

My Index have columns CreateDate and Location. But in my query i used only CreateDate in the where condition.So i wants to know that this query will get the index benefits ,if i will not used all columns of the index in where condition — DeadCat, Jan 18 '18 at 08:34
see update above.....The problem is not that the query doesn't filter by location, but that it SELECT's columns not in the index (EmpName). — Mitch Wheat, Jan 18 '18 at 08:45
If my query is like this...SELECT CreateDate,Location FROM EmployeeDetails WHERE CreateDate >= '2017-05-01' AND CreateDate <= '2018-01-31' Will be working with index ? — DeadCat, Jan 18 '18 at 08:49
Yes, but in that case the index could be just CREATE INDEX IX_2 ON EmployeeDetails (CreateDate) INCLUDE (Location) — Mitch Wheat, Jan 18 '18 at 08:52

Does select query will get the benefit of the Index created?

1 Answers1