STAY IN TOUCH

Get New posts delivered straight to your inbox

  • Madeira Team

Answer to the Quiz

Hi Everybody,

Congratulation to Yair Gutman who won a free ticket to the Advanced Programming in SQL Server 2008 R2 Course !

Here’s the answer to the Quiz:

Let’s first analyze the query without DATEADD:

Transact-SQL

SELECT Orders.Id , Orders.DateAndTime , OrderStatusId FROM Sales.Orders AS Orders WHERE Orders.DateAndTime > SYSDATETIME()

1

2

3

4

5

6

7

8

SELECT

Orders.Id ,

Orders.DateAndTime ,

OrderStatusId

FROM

Sales.Orders AS Orders

WHERE

Orders.DateAndTime > SYSDATETIME()

Since SYSDATETIME is a known value at compile time, the estimated number of rows is correct.

Let’s analyze the effect of DATEADD on a fixed expression:

Transact-SQL

SELECT Orders.Id , Orders.DateAndTime , OrderStatusId FROM Sales.Orders AS Orders WHERE Orders.DateAndTime > DATEADD (YEAR , -2 , '20110801 22:47')

1

2

3

4

5

6

7

8

SELECT

Orders.Id ,

Orders.DateAndTime ,

OrderStatusId

FROM

Sales.Orders AS Orders

WHERE

Orders.DateAndTime > DATEADD (YEAR , -2 , '20110801 22:47')

You can see again that SQL Server calculates correctly the value of the DATEADD and thus estimates correctly the amount of rows.

However, when DATEADD is combined with SYSDATETIME(), as in the Quiz, SQL Server has a bug and estimates this expression to be SYSDATETIME() and ignores the DATEADD. This bug isn’t applied when GETDATE() is used.

If you need the DATETIME2 precision, a workaround this bug can be achieved by first calculating the value of the DATEADD function and then running the query with a Dynamic execution, here’s an example:

Transact-SQL

DECLARE @ThresholdDate DATETIME2 = DATEADD (YEAR , -2 , SYSDATETIME()) EXECUTE sp_executesql N' SELECT Orders.Id , Orders.DateAndTime , OrderStatusId FROM Sales.Orders AS Orders WHERE Orders.DateAndTime > @ThresholdDate', N'@ThresholdDate DATETIME2', @ThresholdDate

1

2

3

4

5

6

7

8

9

10

11

DECLARE

@ThresholdDate DATETIME2 = DATEADD (YEAR , -2 , SYSDATETIME())

EXECUTE sp_executesql N'

SELECT

Orders.Id ,

Orders.DateAndTime ,

OrderStatusId

FROM

Sales.Orders AS Orders

WHERE

Orders.DateAndTime > @ThresholdDate', N'@ThresholdDate DATETIME2', @ThresholdDate

As a result of our Quiz, Erland Sommarskog, has added the bug to Connect:

https://connect.microsoft.com/SQLServer/feedback/details/685903/incorrect-estimate-when-sysdatetime-appear-in-a-dateadd-expression

Please vote, so Microsoft quickly fixes it!

I would like to thank Guy Glantser and Haim Fishner for their great contribution in finding this bug !

#executionplans #spexecutesql

JOIN OUR MAILING LIST

CONTACT US

 3 Rapaport St. Kfar Saba, Israel

 +972-9-7400101

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle