CODEWITHSUNDEEP
sqlsql-serverperformance
Santosh Vaza
Santosh Vaza

Reputation: 19

SQL Server Query Datetime

A table with a date column like

create table OrderSold
(
  ID int primary key identity,
  SellDate date null,
  CustID
)

Now is there any difference in performance in below two, which one will be recommended most

Query #1

select ID 
from OrderSold 
where SellDate = '2014-01-31'

and

Query #2

select ID 
from OrderSold 
where SellDate = '01/31/2014'

and

Query #3

declare @MyDate date = '2014-01-31'

select ID 
from OrderSold 
where SellDate = @MyDate

Thanks..

Upvotes: 0

Views: 93

Answers (2)

I A Khan
I A Khan

Reputation: 8869

select ID 
from OrderSold 
where SellDate = '2014-01-31'

is better due to it is ISO8601 format, for more details about date time read this

on above link its describe in details

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Let's make a little test. First let's populate the table with 1.000.000 rows.

CREATE TABLE OrderSold
    (
      ID INT PRIMARY KEY
             IDENTITY ,
      SellDate DATE NULL ,
      CustID INT
    )
GO


DECLARE @i INT = 1000000

WHILE @i >= 0
    BEGIN

        INSERT  INTO dbo.OrderSold
                ( SellDate, CustID )
        VALUES  ( DATEADD(dd, -@i % 1000, GETDATE()), -- SellDate - date
                  @i  -- CustID - int
                  )
        SET @i = @i - 1
    END

Now let's see actual execution plans on a heap:

enter image description here

As you see execution plans are the same.

Now let's create a non clustered covering index on the heap:

CREATE INDEX IDX_OrderSold_SellDate ON dbo.OrderSold(SellDate) INCLUDE(ID)

Let's see execution plans:

enter image description here

As you can see plans are the same again. The only difference is between scan and seek. So the answer to your question is: there is absolutely no difference between those 3 statements.

Also notice, as mentioned by @marc_s and @Imran Ali Khan,

select ID 
from OrderSold 
where SellDate = '01/31/2014'

this format is language dependent and may not work on some instances. But as those 3 statement are valid and runnable, they would be the same from performance viewpoint.

EDIT:

As mentioned by @Martin Smith, this emulation was not completely correct. Let's add additional 4 rows to table:

INSERT  INTO dbo.OrderSold
        ( SellDate, CustID )
VALUES  ( '20150224', -- SellDate - date
          1  -- CustID - int
          )

INSERT  INTO dbo.OrderSold
        ( SellDate, CustID )
VALUES  ( '20150224', -- SellDate - date
          2  -- CustID - int
          )

INSERT  INTO dbo.OrderSold
        ( SellDate, CustID )
VALUES  ( '20150224', -- SellDate - date
          3  -- CustID - int
          )

INSERT  INTO dbo.OrderSold
        ( SellDate, CustID )
VALUES  ( '20150224', -- SellDate - date
          4  -- CustID - int
          )     

SELECT  ID
FROM    OrderSold
WHERE   SellDate = '2015-02-24'

SELECT  ID
FROM    OrderSold
WHERE   SellDate = '02/24/2015'


DECLARE @MyDate DATE = '2015-02-24'
SELECT  ID
FROM    OrderSold
WHERE   SellDate = @MyDate

enter image description here

As you can see now there is difference, because estimated rows count was 999 and actual rows count is 4(when estimated rows count of first 2 statements are 4). This is because of parameter sniffing problem. Optimizer doesn't know what is the value of variable is and average density of column from statistics comes into play. Here it is 1000.

But you can use query hint OPTION(RECOMPILE) to work around this problem. You can read about it here for example:

http://sqlmag.com/sql-server/using-recompile-query-hint-solve-parameter-sniffing-problems

Upvotes: 4

Related Questions