CODEWITHSUNDEEP
sqlsql-servert-sql
Toni0123
Toni0123

Reputation: 107

Difference between SQL query and use of index on Datetime column

I have simple table with index on DateTime column.

Can someone explain me which one of these two queries will use index? 

CREATE TABLE exams
(
    name VARCHAR(50),
    grade INT,
    date DATETIME
);

CREATE INDEX date_idx ON exams(date);

SELECT * 
FROM exams 
WHERE date = '2018-01-01';      -- doesn't use index?

SELECT * 
FROM exams 
WHERE MONTH(date) = 1;      -- uses index?

Upvotes: 0

Views: 1469

Answers (2)

gotqn
gotqn

Reputation: 43636

There are different ways this to be solved by the SQL Engine. Let's insert some sample data in your table:

DROP TABLE if exists exams;

CREATE TABLE exams(
  name varchar(50),
  grade INT,
  date datetime
);

INSERT exams
SELECT TOP (5000)  CONCAT('name', row_number() over(order by t1.number))
                   ,6
                   ,'2019-07-01'
FROM master..spt_values t1 
CROSS JOIN master..spt_values t2

INSERT exams
SELECT TOP (5)  CONCAT('name', row_number() over(order by t1.number))
                   ,6
                   ,'2019-07-02'
FROM master..spt_values t1 
CROSS JOIN master..spt_values t2

CREATE INDEX date_idx ON exams(date);

As you can see, we have inserted:

  • 5 000 rows for date 2019-07-01
  • 5 rows for date 2019-07-02

Let's execute the following queries, now:

SELECT * FROM exams WHERE date= '2019-07-01'; 

SELECT * FROM exams WHERE date= '2019-07-02'; 

SELECT * FROM exams WHERE MONTH(date)=1;

and check the execution plans:

enter image description here

In the first query, the engine knows (because of the statistics) that almost all of the data is going to be read, so it performs table scan on your heap.

In the second query, the engine see that only few of the records are going to be return, so there is no need to read all the data - it uses the index, and performs index seek.

In the last case, the index can't be used, because the query si not sargable.

So, the engine decides if or not to use an index, and if or not to perform seek or scan. The only thing you can do is to make sure your indexes are covering, your statistics are updated and your queries are sargable.

Upvotes: 6

Michał Turczyn
Michał Turczyn

Reputation: 37347

Well, I believe that the truth is quite inversed:

First query uses index, while second DOES NOT.

Why second query doesn't use index? Because indexed column is wrapped in a function which prevents SQL Server from using index.

Index can be thought of as way of storing records. Applying function to indexed column may alter order of stored records, thus index can be no longer valid when using function.

Upvotes: 4

Related Questions