We have been running SQL Server 2016 for some time now but initially left the database compatibility level to SQL Server 2008 (100). Recently we did update the DB Compatibility Level to 2016 (130) but we have encountered few issues there. We managed to find one particular one where a query converting datetime to datetime2 and it returns NULL when it should have returned a value, not sure if it is CE or something? Please see the example below:
CREATE TABLE TestCE
(
Id INT IDENTITY(1,1),
CurrentDateTime DATETIME
)
GO
INSERT dbo.TestCE(CURRENTDATETIME)
SELECT GETDATE()
UNION ALL SELECT '2018-04-11 08:44:42.643'
UNION ALL SELECT '2018-04-12 09:49:45.334'
GO
SELECT * FROM TestCE
--The resultset
1 2018-04-17 16:49:02.813
2 2018-04-11 08:44:42.643
3 2018-04-12 09:49:45.333
But when I run the following queries, don't get any result:
SELECT * FROM TESTCE
WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-12 09:49:45.333')
GO
SELECT * FROM TESTCE
WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-11 08:44:42.643')
GO
SELECT * FROM TESTCE
WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-17 16:49:02.813')
I have also tried with QueryTraceOn option for backward CE but with no success:
SELECT * FROM TESTCE
WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-12 09:49:45.333')
OPTION(QUERYTRACEON 9481);
GO
SELECT * FROM TESTCE
WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-11 08:44:42.643')
OPTION(QUERYTRACEON 9481);
GO
SELECT * FROM TESTCE
WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-17 16:49:02.813')
OPTION(QUERYTRACEON 9481);
Also set the following DB option but with no success either:
ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON
As soon as I change the compatibility level back to 2008, queries start returning the data as expected.
Best Answer
From this KB article:
In your specific example, the comparison still works if:
datetime2
(and this is unnecessary since you don't have any more precision in the input anyway);datetime2
column in the first place;datetime
after converting todatetime2
; or,Otherwise you are trying to make SQL Server forever know that, for example,
.333
=.334
. The new types were introduced in SQL Server 2008, and were replaced to address exactly this type of imprecision; I guess they feel they have to move on at some point.As an aside, I don't think the current behavior is 100% correct, but it's what we're stuck with for now.
Further, compatibility level is one way (and not a really good way) to affect the cardinality estimation model, but the reverse is not true. You can't change compatibility level by changing the CE model. This is why you didn't have any success using the query-level trace flag or database configuration option. But you don't have to go back to
100
;120
would probably work as well.