SQL Server 2016 Compatibility Level and Datetime 2

3 votes

1 answer

1031 views

sql-server-2016 cardinality-estimates datetime2

                          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.

                        

Asked by Sajid (310 rep)

Apr 17, 2018, 04:20 PM
Last activity: Apr 17, 2018, 04:44 PM

SQL Server 2016 Compatibility Level and Datetime 2

Related Questions