Skip to main content
5 of 6
Updated link
Paul White
  • 90.3k
  • 30
  • 424
  • 663

A less comprehensive answer than Aaron's but the core issue is a cardinality estimation bug with DATEADD when using the datetime2 type:

Connect: Incorrect estimate when sysdatetime appear in a dateadd() expression

One workaround is to use GETUTCDATE (which returns datetime):

WHERE CreatedUtc > CONVERT(datetime2(7), DATEADD(DAY, -365, GETUTCDATE()))

Note the conversion to datetime2 must be outside the DATEADD to avoid the bug.

The issue of the 1 row cardinality estimation reproduces for me in all versions of SQL Server up to and including 2016 RC0 where the 70 model cardinality estimator is used.

Aaron Bertrand has written an article about this for SQLPerformance.com:

Paul White
  • 90.3k
  • 30
  • 424
  • 663