0

I've just upgraded to SQL Server 2017 from SQL Server 2014 (only dev environment yet) and noticed a very strange behavior : the same query has two different impacts on both servers.

The query has been rewritten and reduced to the minimum for the example:

SELECT * FROM eth_Mandate M WHERE customerId = 1 AND M.isActive = 1

The field isActive is a computed column (returning 1 if mandate is active, 0 otherwise) defined as: 

[isActive]  AS (CASE WHEN GETDATE() >= ISNULL([man_start],GETDATE()) AND GETDATE() <= isnull(dateadd(day,(1),[man_stop]),GETDATE()) THEN 1 ELSE 0 END)

For SQL Server 2014 it always works. For 2017, once in 4-5 times it returns no rows as sometimes isActive is computed as 0.

Why? I've searched deeply and wanted to share it so you can find my own answer below the explanation I understood based on my tests.

Michael Bruesch
  • 632
  • 7
  • 23
pti_jul
  • 432
  • 1
  • 5
  • 18
  • Versions please. It is possible this has longbeen fixed and is an admin error. 2017 is under active management. Which versions are you using? – TomTom Oct 10 '18 at 10:47
  • build 14.0.2002 for SQL Server 2017 and 12.0.4213 for SQL Server 2014 (I'm using a hosted dedicated and managed server in the cloud). – pti_jul Oct 10 '18 at 10:48
  • I trongly suggest, before asking the question, to upgrade to 14.0.3038.14 - 12 CU packagets in the future from yours (!). You literally run on RTM + 2 security updates, ignoring so 11 (!) bug fix packs. No sense in trying to fis code bugs unless they happen on CURRENT versions. – TomTom Oct 10 '18 at 10:52
  • In the future check https://buildnumbers.wordpress.com/sqlserver/ for all build numbers and update accordingly ;) Helps to avoid bugs. – TomTom Oct 10 '18 at 10:52
  • Thanks TomTom for your answers. I'll ask my provider to update with latest patch. – pti_jul Oct 10 '18 at 11:03
  • @pti_jul, I updated your answer to answer the "why" question you asked. Your answer included workarounds. – Dan Guzman Oct 10 '18 at 11:07
  • @DanGuzman : Thanks for this very interesting article !! I read it with lots of interest. Thanks for your suggestion, but changing the Compatibility level is something temporary (as mentioned in your article) until a proper solution is found. So, I've added Solution 4. – pti_jul Oct 10 '18 at 11:25

1 Answers1

0

The problem is that in the ISNULL we have a datetime2 (the field man_stop) and getdate as fallback (returns a datetime) and apprently the ISNULL cast the second param based on the first param....Consequently, we are comparing the getdate() as datetime with a getdate() as datetime2. Note that the man_stop (end of the customer mandate) is most of the time not null as the mandate are running.

Then, you must take a look at this article and specifically about the sentence :

As for the time portion, SQL Server starts with 0 and increments the numeric value for each .003 seconds after midnight. Means what ? if the time is 12:15:00:002 is actually stored as 12:15:00:003 but compared with 12:15:00.002 so not equal !!

SOLUTION 1

So, to fix the problem for SQL Server 2017 (no problem with SQL Server 2014 though), I simply converted my fields datetime2 to datetime to have a correct comparison with getdate() as datetime : 

[isActive]  AS (case when getdate()>=isnull(CONVERT([datetime],[man_start]),getdate()) AND getdate()<=isnull(dateadd(day,(1),CONVERT([datetime],[man_stop])),getdate()) then (1) else (0) end)

SOLUTION 2

Or I could also convert all the datetime to datetime2. Instead of GETDATE() use SYSDATETIME() returning a datetime2(7). As my two fields are simply datetime2 (no precision defined) the comparison was worse...but when converting the datetime2 to datetime2(7) it was running like a charm. So it becomes : 

(case when SYSDATETIME () >= isnull(convert(datetime2(7), [man_start]),SYSDATETIME()) AND SYSDATETIME() <= isnull(dateadd(day,(1),convert(datetime2(7), [man_stop])),SYSDATETIME()) then (1) else (0) end)

SOLUTION 3

Change the database compatibility level to 120 or lower to preserve the legacy behavior when comparing datetime and datetime 2 types. This breaking change was introduced in SQL Server 2016.

SOLUTION 4 (solution applied)

Use same type of data and avoid type conversion. Change the type of the fields man_start and man_stop as datetime2(7) (instead of datetime2 which is datetime2(3)) to match SYSDATETIME() function returned type (datetime2(7)).

ALTER TABLE myTable ALTER COLUMN [man_start] datetime2(7) NULL
ALTER TABLE myTable ALTER COLUMN [man_stop] datetime2(7) NULL

So my computed column becomes : [isActive] AS (CASE WHEN SYSDATETIME() >= ISNULL([man_start],SYSDATETIME()) AND SYSDATETIME() <= isnull(dateadd(day,(1),[man_stop]),SYSDATETIME()) THEN 1 ELSE 0 END)

Advantages : Compatibility level remains, avoid type conversion (better peformance)

pti_jul
  • 432
  • 1
  • 5
  • 18
  • 2
    So, all kinds of information about what `isActive` actually is? That ought to be **in the question**. Your question, as it stands at the moment is "why does `isActive` sometimes not work" with *no further information about it*. This makes this question/answer pair very poor quality at the moment. – Damien_The_Unbeliever Oct 10 '18 at 10:52
  • You're right. I firstly had all together and decided finally to separate question and own answer...but missed that point. I've edited both question and answer to include the `IsActive` field definition in the question. – pti_jul Oct 10 '18 at 11:05