Why do some DBAs avoid DATETIME2?

Question

Why do some DBAs avoid DATETIME2?

Andrew P

SSCarpal Tunnel

Points: 4649
More actions
June 1, 2017 at 1:11 am

#343036

I've been considering using DATETIME2(3) over DATETIME for new work due to the 1-byte saving of equivalent values (DATETIME2(3) vs DATETIME), but I've had a comment Jeff Moden made in the back of my mind for a that seemed to suggest against doing that. Jeff's post today brought this to mind again.
I can't find direct on this, are the need to use date functions (DATEADD, DATEDIFF etc.), and the inconsistency issues brought by adding DATETIME2 into an existing solution (implicit converts when comparing DATETIME and DATETIME2 and schema datatype inconsistency) the primary concerns with DATETIME2?
Andrew P.

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Give me a reason to use the new data type when I can't use date functions with it natively, or can't get DATEDIFF in particular to work with it at it's higher level of precision.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

Heh... obviously, by the link you posted, you know some of the reasons why I avoid it. Add to that that I have no need for the extended early dates (< 1753-01-01) and I don't need the headaches caused by not being able to do direct date math and I don't need the additional resolution and I don't need the headaches enforcing one or more standard resolutions (it would probably be (0) for what I need) and I don't like the fact that I can't use a very compact "0" in a lot of the necessary date calculations that I very frequently need to do without the result of the formula reverting back to DATETIME. For example....

DECLARE @ZeroDate DATETIME2(0) = '19000101'
;
 SELECT  DataType1 = SQL_VARIANT_PROPERTY(DATEADD(mm,DATEDIFF(mm,0         ,SYSDATETIME()),0)         ,'BASETYPE')
        ,DataType2 = SQL_VARIANT_PROPERTY(DATEADD(mm,DATEDIFF(mm,'1900'    ,SYSDATETIME()),'1900')    ,'BASETYPE')
        ,DataType3 = SQL_VARIANT_PROPERTY(DATEADD(mm,DATEDIFF(mm,'19000101',SYSDATETIME()),'19000101'),'BASETYPE')
        ,DataType4 = SQL_VARIANT_PROPERTY(DATEADD(mm,DATEDIFF(mm,@ZeroDate ,SYSDATETIME()),@ZeroDate) ,'BASETYPE')
;

DataTypes 1, 2, and 3 all come back as the DATETIME data type for this very common calculation for the first instant/day of the current month. The only way to get a DATETIME2() result datatype is to ensure that absolutely everything in the formula is DATETIME2(). The easiest way to do that is to use a variable as the necessary constant.Here's another one. Imagine that you don't yet have 2016 so you can't use that bloody DATEDIFF_BIG() function yet. Now, using the DATETIME2() datatype for the start and end dates, calculate the difference between those two date in days, hours, minutes, seconds, and milliseconds Here... I'll get you started with a DATETIME example. You do the DATETIME2 example. 😉

DECLARE  @StartDT DATETIME = '2017-01-01 00:00:00.000'
        ,@EndDT   DATETIME = '2017-01-25 20:31:23.650'
;
 SELECT  CONVERT(VARCHAR(10),DATEDIFF(dd,0,@EndDT-@StartDT))+':'
        +CONVERT(CHAR(12),@EndDT-@StartDT,114)
;

Along with what I said on the link you provided, all the hassle just isn't worth the byte or two of savings for me even across a billion rows and I haven't covered all of the reasons why I don't use DATETIME2(). And, good lord no... I don't believe in the myth of portability.And I can't believe that MS made a function to calculate the last day of the month and not the first day of the month! Unless you're only using whole dates, a whole lot of people miss out on almost a whole day of data using EOMONTH as the end date of a between.Don't get me started on the miserable performance behind FORMAT or the fact that there's no year on ISO_Week nor any easy way to convert the ISO_Week back to an actual week start or end date. :sick: And, finally, the only satisfaction I get out of the DATE and TIME datatype in the same table is helping some poor unlucky Developer put them back together when they need to. :0

This reply was modified 5 years, 7 months ago by Jeff Moden.
This reply was modified 4 months ago by Jeff Moden. Reason: Cleanup code messed up due to forum "Upgrades"

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

I'll pile on and say that I don't use datetime2 either. You can do direct math with datetime; it's that simple. The datetime2 returns an "operand clash" error.

Microsoft really got it right with the datetime data type. It works and it works very well. They state (https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql) that the datetime2 is more portable, but I believe the myth of portability is just that - a myth. As soon as a single variable is declared in a procedure in either one, it won't port to the other without changes.

Yes, they did fix the earliest date problem of 1/1/1753, but I don't have a need for it. That's not saying I won't someday, so it's nice to have an option available when I need it.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Adding to the pile:

Performance of operations on DATETIME2 is 20...100 times (depending on the operation) worse than on datetime.

Because of reversed sequence of bytes statistics are not effective.
A bigger binary value stored in a table does not necessarily represent a bigger datetime2 value.
Therefore optimizer effectively ignores statistics on datetime2 columns:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1533fe25-89f5-498e-97e7-a302c3a146fc/row-estimations-number-is-miscalculated-when-using-dateadd-sysdatetime-as-predicate?forum=transactsql
https://sqlperformance.com/2016/04/sql-performance/surprises-dateadd

Saving several MBs of storage can (and almost certainly will) cause several GB's in use of memory and tempdb.
With all the performance issues attached to it.

_____________
Code for TallyGenerator

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Sergiy - Monday, June 5, 2017 8:53 PM
Adding to the pile:
Performance of operations on DATETIME2 is 20...100 times (depending on the operation) worse than on datetime.
Because of reversed sequence of bytes statistics are not effective.
A bigger binary value stored in a table does not necessarily represent a bigger datetime2 value.
Therefore optimizer effectively ignores statistics on datetime2 columns:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1533fe25-89f5-498e-97e7-a302c3a146fc/row-estimations-number-is-miscalculated-when-using-dateadd-sysdatetime-as-predicate?forum=transactsql
https://sqlperformance.com/2016/04/sql-performance/surprises-dateadd
Saving several MBs of storage can (and almost certainly will) cause several GB's in use of memory and tempdb.
With all the performance issues attached to it.

Now that's just sad. Thanks for the links, Sergiy.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 6

Mostly, habit. datetime comes up first in intellisense, shorter to type, old habits.

I go back and forth. I'm trying to be better with declarations like explicitly using datetime2(3), but it's a hassle, so I tend to go with date when I need dates, or datetime if I'm not sure.

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 7

Man, you guys are kind!

sgmunson, I had expected DATEADD and DATEDIFF to automagically use the parsed objects data type. Paying more careful attention, I can see it does not. Is it doing an implicit convert when I pass a DATETIME2 to DATEADD/DATEDIFF?

Can you point to an example of DATEDIFF losing precision on it’s highest precision [DATETIME2(7)]? The below compares 100 nanoseconds either side of the current date/time at the highest precision, and seems to give the correct result.

select SYSDATETIME() as NowDT2, dateadd(ns, -100, SYSDATETIME()) as NowMinus200NS, dateadd(ns, 100, SYSDATETIME()) as NowPlus200NS, datediff(ns, dateadd(ns, -100, SYSDATETIME()), dateadd(ns, 100, SYSDATETIME()) ) as DateDiffBetweenThoseTwoJeff, thanks for the elaboration. Agreed that there’s a significant risk introduced to save a very small amount of space. That’s an impressively concise day, hour, minute, second, millisecond difference formula. Yeah, storing DATE and TIME separately sounds like it might do well for compression (fewer distinct DATE values, should compress easier?), but make everything on those values more difficult.

RE: EOMONTH, hahah - agreed, I haven’t previously used that, but it seems consistent in what it does. That issue helped me think more carefully about the date range filters I was using back when I was getting started. You’re right - it is strange that they went with that instead of a start of month function; I expect it will nearly always need to have 1 day added to it for use when filtering.

Ed Wagner, thanks for explaining the term "portability" regarding variable data types. I agree - if we started using DATETIME2 in existing tables, we would never be afforded the time to change stored procedure parameters, etc. that reference those columns, leading to an increase in implicit converts. Thanks also for the pointer on the earliest date supported by DATETIME2 - DATETIME2 seems like a good fit for that special requirement.

Sergiy, that bug is neat, and I was delighted to be able to reproduce the example bad row estimate. Poor support for statistics is surprising and reasonably serious; I’m guessing this is the cause of the performance issues you mention? If it’s not, can you point to more information on the cause of the performance issues with DATETIME2?

Thanks Steve Jones, I can relate to that!

I’ve standardized on DATE, SMALLDATETIME or DATETIME2(3) for a couple of newer internal projects, but all of the other members of my team (around 6 C# developers) use DATETIME, so I’ll break my habit here and get back to DATE, SMALLDATETIME, DATETIME unless the situation calls for DATETIME2.

I feel silly asking this, but why does “SELECT SQL_VARIANT_PROPERTY(DATEADD(NS, 100, SYSDATETIME()),'BASETYPE')” return “datetime2”, and the DATEADD function keep precision, when the function definition indicates it expects a SMALLDATETIME and returns a SMALLDATETIME? I.e., why doesn’t DATEADD convert and output the DATETIME2 value as a SMALLDATETIME in keeping with its definition?

The input on this very much appreciated. Apologies for the delay responding - I really do appreciate the commentary.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 8

Sergiy - Monday, June 5, 2017 8:53 PM
Adding to the pile:
Performance of operations on DATETIME2 is 20...100 times (depending on the operation) worse than on datetime.
Because of reversed sequence of bytes statistics are not effective.
A bigger binary value stored in a table does not necessarily represent a bigger datetime2 value.
Therefore optimizer effectively ignores statistics on datetime2 columns:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1533fe25-89f5-498e-97e7-a302c3a146fc/row-estimations-number-is-miscalculated-when-using-dateadd-sysdatetime-as-predicate?forum=transactsql
https://sqlperformance.com/2016/04/sql-performance/surprises-dateadd
Saving several MBs of storage can (and almost certainly will) cause several GB's in use of memory and tempdb.
With all the performance issues attached to it.

I'd love to be able to say that DATETIME2() is slower than DATETIME to give me one more reason to not use it but that's not actually a problem with DATETIME2(). It's a poorly formed index problem in the code for those links. Even the test setup code is faulty in that the poster doesn't know that an ORDER BY shouldn't be on an indeterminate function because it cause the cross join to fully materialize before the TOP comes into play. The reason it looks so bad is because the improper index causes row lookups whereas the implicit cast for DATETIME causes a more efficient table scan. Both benefit greatly with the proper index.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 9

Jeff Moden - Wednesday, June 7, 2017 7:07 AM
Sergiy - Monday, June 5, 2017 8:53 PM
Adding to the pile:
Performance of operations on DATETIME2 is 20...100 times (depending on the operation) worse than on datetime.
Because of reversed sequence of bytes statistics are not effective.
A bigger binary value stored in a table does not necessarily represent a bigger datetime2 value.
Therefore optimizer effectively ignores statistics on datetime2 columns:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1533fe25-89f5-498e-97e7-a302c3a146fc/row-estimations-number-is-miscalculated-when-using-dateadd-sysdatetime-as-predicate?forum=transactsql
https://sqlperformance.com/2016/04/sql-performance/surprises-dateadd
Saving several MBs of storage can (and almost certainly will) cause several GB's in use of memory and tempdb.
With all the performance issues attached to it.
I'd love to be able to say that DATETIME2() is slower than DATETIME to give me one more reason to not use it but that's not actually a problem with DATETIME2(). It's a poorly formed index problem in the code for those links. Even the test setup code is faulty in that the poster doesn't know that an ORDER BY shouldn't be on an indeterminate function because it cause the cross join to fully materialize before the TOP comes into play. The reason it looks so bad is because the improper index causes row lookups whereas the implicit cast for DATETIME causes a more efficient table scan. Both benefit greatly with the proper index.

Jeff,

It seems like the forum software "merges" 2 links I've posted.
Here is the 2nd one which does not open from my original post:

https://sqlperformance.com/2016/04/sql-performance/surprises-dateadd

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 10

Sergiy - Wednesday, June 7, 2017 7:21 AM
Jeff Moden - Wednesday, June 7, 2017 7:07 AM
Sergiy - Monday, June 5, 2017 8:53 PM
Adding to the pile:
Performance of operations on DATETIME2 is 20...100 times (depending on the operation) worse than on datetime.
Because of reversed sequence of bytes statistics are not effective.
A bigger binary value stored in a table does not necessarily represent a bigger datetime2 value.
Therefore optimizer effectively ignores statistics on datetime2 columns:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1533fe25-89f5-498e-97e7-a302c3a146fc/row-estimations-number-is-miscalculated-when-using-dateadd-sysdatetime-as-predicate?forum=transactsql
https://sqlperformance.com/2016/04/sql-performance/surprises-dateadd
Saving several MBs of storage can (and almost certainly will) cause several GB's in use of memory and tempdb.
With all the performance issues attached to it.
I'd love to be able to say that DATETIME2() is slower than DATETIME to give me one more reason to not use it but that's not actually a problem with DATETIME2(). It's a poorly formed index problem in the code for those links. Even the test setup code is faulty in that the poster doesn't know that an ORDER BY shouldn't be on an indeterminate function because it cause the cross join to fully materialize before the TOP comes into play. The reason it looks so bad is because the improper index causes row lookups whereas the implicit cast for DATETIME causes a more efficient table scan. Both benefit greatly with the proper index.
Jeff,
It seems like the forum software "merges" 2 links I've posted.
Here is the 2nd one which does not open from my original post:
https://sqlperformance.com/2016/04/sql-performance/surprises-dateadd

Ah. Ok. Now I'm bagging what you're raking. In that instance (and a couple of others similar to it), the DATETIME(2) version is estimating a row count of 1 and so does a very expensive seek with RID lookups whereas the DATETIME example correctly figured out that a table scan would be less expensive. There are 144 times the number of logical reads and takes 66% more time for the DATETIME2() example than the DATETIME example. And it doesn't seem to matter if the date and time column itself is a DATETIME2() or "just" a DATETIME column.

Thanks for your persistence, Sergiy. Now I have another reason to not use DATETIME2().

{EDIT} Heh... dammit... you got me thinking about whether or not DATE and TIME have that same problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 11

Jeff Moden - Wednesday, June 7, 2017 7:47 PM
Sergiy - Wednesday, June 7, 2017 7:21 AM
Jeff Moden - Wednesday, June 7, 2017 7:07 AM
Sergiy - Monday, June 5, 2017 8:53 PM
Adding to the pile:
Performance of operations on DATETIME2 is 20...100 times (depending on the operation) worse than on datetime.
Because of reversed sequence of bytes statistics are not effective.
A bigger binary value stored in a table does not necessarily represent a bigger datetime2 value.
Therefore optimizer effectively ignores statistics on datetime2 columns:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1533fe25-89f5-498e-97e7-a302c3a146fc/row-estimations-number-is-miscalculated-when-using-dateadd-sysdatetime-as-predicate?forum=transactsql
https://sqlperformance.com/2016/04/sql-performance/surprises-dateadd
Saving several MBs of storage can (and almost certainly will) cause several GB's in use of memory and tempdb.
With all the performance issues attached to it.
I'd love to be able to say that DATETIME2() is slower than DATETIME to give me one more reason to not use it but that's not actually a problem with DATETIME2(). It's a poorly formed index problem in the code for those links. Even the test setup code is faulty in that the poster doesn't know that an ORDER BY shouldn't be on an indeterminate function because it cause the cross join to fully materialize before the TOP comes into play. The reason it looks so bad is because the improper index causes row lookups whereas the implicit cast for DATETIME causes a more efficient table scan. Both benefit greatly with the proper index.
Jeff,
It seems like the forum software "merges" 2 links I've posted.
Here is the 2nd one which does not open from my original post:
https://sqlperformance.com/2016/04/sql-performance/surprises-dateadd
Ah. Ok. Now I'm bagging what you're raking. In that instance (and a couple of others similar to it), the DATETIME(2) version is estimating a row count of 1 and so does a very expensive seek with RID lookups whereas the DATETIME example correctly figured out that a table scan would be less expensive. There are 144 times the number of logical reads and takes 66% more time for the DATETIME2() example than the DATETIME example. And it doesn't seem to matter if the date and time column itself is a DATETIME2() or "just" a DATETIME column.
Thanks for your persistence, Sergiy. Now I have another reason to not use DATETIME2().
{EDIT} Heh... dammit... you got me thinking about whether or not DATE and TIME have that same problem.

I guess you did something similar to what I did before my last post. The row estimates are off. The Bookmark lookups reminded me of a demo of 895955 seeks versus 1 scan, but at a lower scale. 😉

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 12

Yes. Almost identical.

I also just found out this also affects the DATE data-type when used in a similar fashion in the WHERE clause. That means that all those folks using a conversion or cast to the DATE datatype to get rid of time in criteria in the WHERE clause have got a nice surprise that hadn't counted on whether the compared column is DATETIME2(), DATE, or DATETIME.

I'm at a loss for words because the CONNECT item on the subject was "Closed as Won't Fix" and, according to comments on that CONNECT item, the problem hasn't been fixed even in 2014. No one made any comments about it for 2016 but, if I were a betting man, I'd say the problem still exists there, as well.

Here's some test code... it's similar to the original but cleaned up a lot and the table population is a lot faster. The logical reads are insane for the DATE conversions..

--================================================================================================= -- Create and populate the test table --================================================================================================= --===== If the test table exists, drop it to make reruns in SsMS easier. IF OBJECT_ID('tempdb..#Order','U') IS NOT NULL DROP TABLE #Order ; --===== Create the table with a machine named PK. CREATE TABLE #Order ( OrderID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED ,DateAndTime DATETIME2(3) NOT NULL --Doesn't matter if DATETIME2 or DATETIME. ,OrderStatusId TINYINT NOT NULL ) ; GO --===== Add the expected non-clustered index. CREATE NONCLUSTERED INDEX IX_Order_DateAndTime ON #Order (DateAndTime ASC) ; GO --===== Populate the table. INSERT INTO #Order WITH (TABLOCK) (DateAndTime, OrderStatusId) SELECT TOP (1000000) DateAndTime = DATEADD(mi,-ABS(CHECKSUM(NEWID())%(60*24*365*5)),SYSDATETIME()) ,OrderStatusId = ABS(CHECKSUM(NEWID())%8)+1 FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2 ; GO --================================================================================================= -- Do the tests --================================================================================================= SET STATISTICS TIME,IO OFF; PRINT REPLICATE('=',119); RAISERROR('========== Converted to DATE ==========',0,0) WITH NOWAIT; SET STATISTICS TIME,IO ON; GO SELECT OrderID ,DateAndTime ,OrderStatusId FROM #Order WHERE DateAndTime > DATEADD(yy,-2,CAST(SYSDATETIME() AS DATE)) ;

SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('=',119);
RAISERROR('========== Converted to DATETIME #1 ==========',0,0) WITH NOWAIT;
SET STATISTICS TIME,IO ON;
GO
SELECT OrderID
        ,DateAndTime
        ,OrderStatusId
   FROM #Order
WHERE DateAndTime > CAST(DATEADD(yy,-2,CAST(SYSDATETIME() AS DATE)) AS DATETIME)
;
GO

SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('=',119);
RAISERROR('========== Converted to DATETIME #2 ==========',0,0) WITH NOWAIT;
SET STATISTICS TIME,IO ON;
GO
SELECT OrderID
        ,DateAndTime
        ,OrderStatusId
   FROM #Order
WHERE DateAndTime > CAST(DATEADD(yy,-2,CAST(GETDATE() AS DATE)) AS DATETIME)
;
GO

SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('=',119);
RAISERROR('========== All DATETIME ==========',0,0) WITH NOWAIT;
SET STATISTICS TIME,IO ON;
GO
SELECT OrderID
        ,DateAndTime
        ,OrderStatusId
   FROM #Order
WHERE DateAndTime > DATEADD(yy,DATEDIFF(yy,0,GETDATE())-2,0)
;
GO
SET STATISTICS TIME,IO OFF;
GO

Here are the results on my 2008 laptop. Results are similar on my 2012 production server.

(1000000 row(s) affected) ======================================================================================================================= ========== Converted to DATE ========== SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

(401279 row(s) affected)
Table '#Order______________________________________________________________________________________________________________000000000017'.
Scan count 1, logical reads 1229627, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 655 ms, elapsed time = 2368 ms.
=======================================================================================================================
========== Converted to DATETIME #1 ==========
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(401279 row(s) affected)
Table '#Order______________________________________________________________________________________________________________000000000017'.
Scan count 1, logical reads 1229627, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 639 ms, elapsed time = 2276 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
=======================================================================================================================
========== Converted to DATETIME #2 ==========
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(401279 row(s) affected)
Table '#Order______________________________________________________________________________________________________________000000000017'.
Scan count 1, logical reads 1229627, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 546 ms, elapsed time = 2178 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
=======================================================================================================================
========== All DATETIME ==========
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

(487062 row(s) affected)
Table '#Order______________________________________________________________________________________________________________000000000017'.
Scan count 1, logical reads 2608, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 2717 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

Indeed, thank you for the links, Sergiy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 13

Jeff Moden - Wednesday, June 7, 2017 9:52 PM
I'm at a loss for words because the CONNECT item on the subject was "Closed as Won't Fix" and, according to comments on that CONNECT item, the problem hasn't been fixed even in 2014. No one made any comments about it for 2016 but, if I were a betting man, I'd say the problem still exists there, as well.

I recon the problem is not fixable with the current design of datetime2.

Reversed order of bytes makes it impossible for a computer to compare stored binary values as whole ones.

64 bit CPU can compare 2 DATETIME binaries in a single cycle, it's 3 Assembler commands.

But it does not work for DATETIME2.
You need to split the binaries into separate bytes (we need to lookup somewhere else for the number of bytes to be used) and then perform sequential comparison byte-by-byte, using 8 bit out of 64 on each cycle.

Now, when you search a value in an index you face a prospect of doing this for every entry in the statistics.
Statistics on the reversed binary strings do not provide any useful hints whatsoever.
Which means - need to perform an index scan just to estimate if it has to be scanned or seeked.
No wonder MS chose not to use statistics at all and assume SEEK for this kind of datatype every single time.

One could say "Somebody had a gigantic brain fart", but actually not.
Since MS charges Azure users for CPU cycles it makes a good sense for them to advocate usage of DATETIME2 instead DATETIME.
Clear profit. "It's just a business" (c).

_____________
Code for TallyGenerator

David Burrows SSC Guru Points: 65142 More actions · Answer 14

Jeff Moden - Wednesday, June 7, 2017 9:52 PM
Here are the results on my 2008 laptop. Results are similar on my 2012 production server.
(1000000 row(s) affected) ======================================================================================================================= ========== Converted to DATE ========== SQL Server parse and compile time:    CPU time = 0 ms, elapsed time = 0 ms.
(401279 row(s) affected)
Table '#Order______________________________________________________________________________________________________________000000000017'.
Scan count 1, logical reads 1229627, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 655 ms, elapsed time = 2368 ms.
=======================================================================================================================
========== Converted to DATETIME #1 ==========
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
(401279 row(s) affected)
Table '#Order______________________________________________________________________________________________________________000000000017'.
Scan count 1, logical reads 1229627, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 639 ms, elapsed time = 2276 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
=======================================================================================================================
========== Converted to DATETIME #2 ==========
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
(401279 row(s) affected)
Table '#Order______________________________________________________________________________________________________________000000000017'.
Scan count 1, logical reads 1229627, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 546 ms, elapsed time = 2178 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
=======================================================================================================================
========== All DATETIME ==========
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.
(487062 row(s) affected)
Table '#Order______________________________________________________________________________________________________________000000000017'.
Scan count 1, logical reads 2608, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 156 ms, elapsed time = 2717 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

Interesting Jeff,
I did start using DATETIME2 but now have reverted to DATETIME.
I do use DATE extensively as a lot of my data is date only never any time.
I ran your test on my new SQL 2016 server, which not currently in use, so the test was the only thing running, results as follows

========== Converted to DATE ========== SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

(400623 row(s) affected) Table '#Order______________________________________________________________________________________________________________00000000014D'. Scan count 1, logical reads 2608, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 78 ms, elapsed time = 764 ms. =======================================================================================================================

========== Converted to DATETIME #1 ========== SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

(400623 row(s) affected) Table '#Order______________________________________________________________________________________________________________00000000014D'. Scan count 1, logical reads 2608, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 266 ms, elapsed time = 612 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

========== Converted to DATETIME #2 ========== SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

(400623 row(s) affected) Table '#Order______________________________________________________________________________________________________________00000000014D'. Scan count 1, logical reads 2608, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 266 ms, elapsed time = 745 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

========== All DATETIME ========== SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

(487120 row(s) affected) Table '#Order______________________________________________________________________________________________________________00000000014D'. Scan count 1, logical reads 2608, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 250 ms, elapsed time = 811 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

Far away is close at hand in the images of elsewhere.
Anon.