Peter Larsson Blog

Patron Saint of Lost Yaks

The internal storage of a DATETIME2 value

Today I went for investigating the internal storage of DATETIME2 datatype. What I found out was that for a datetime2 value with precision 0 (seconds only), SQL Server need 6 bytes to represent the value, but stores 7 bytes.
This is because SQL Server add one byte that holds the precision for the datetime2 value.

Start with this very simple repro

declare @now datetime2(7) = '2010-12-15 21:04:03.6934231'

select  cast(cast(@now as datetime2(0)) as binary(7)),
        cast(cast(@now as datetime2(1)) as binary(7)),
        cast(cast(@now as datetime2(2)) as binary(7)),
        cast(cast(@now as datetime2(3)) as binary(8)),
        cast(cast(@now as datetime2(4)) as binary(8)),
        cast(cast(@now as datetime2(5)) as binary(9)),
        cast(cast(@now as datetime2(6)) as binary(9)),
        cast(cast(@now as datetime2(7)) as binary(9))

Now we are going to copy and paste these binary values and investigate which value is representing what time part.

Prefix  Ticks       Ticks         Days    Days    Original value
——  ———-  ————  ——  ——  ——————–
0x  00  442801             75844  A8330B  734120  0x00442801A8330B
0x  01  A5920B            758437  A8330B  734120  0x01A5920BA8330B 
0x  02  71BA73           7584369  A8330B  734120  0x0271BA73A8330B
0x  03  6D488504        75843693  A8330B  734120  0x036D488504A8330B
0x  04  46D4342D       758436934  A8330B  734120  0x0446D4342DA8330B
0x  05  BE4A10C401    7584369342  A8330B  734120  0x05BE4A10C401A8330B
0x  06  6FEBA2A811   75843693423  A8330B  734120  0x066FEBA2A811A8330B
0x  07  57325D96B0  758436934231  A8330B  734120  0x0757325D96B0A8330B

Let us use the following color schema
Red - Prefix
Green - Time part
Blue - Day part

What you can see is that the date part is equal in all cases, which makes sense since the precision doesm't affect the datepart. What would have been fun, is datetime2(negative) just like round accepts a negative value.
-1 would mean rounding to 10 second, -2 rounding to minute, -3 rounding to 10 minutes, -4 rounding to hour and finally -5 rounding to 10 hour.
-5 is pretty useless, but if you extend this thinking to -6, -7 and so on, you could actually get a datetime2 value which is accurate to the month only. Well, enough ranting about this. Let's get back to the table above.

If you add 75844 second to midnight, you get 21:04:04, which is exactly what you got in the select statement above.
And if you look at it, it makes perfect sense that each following value is 10 times greater when the precision is increased one step too.


Legacy Comments

re: The internal storage of a DATETIME2 value
One can not underestimate the importance of utilizing internal storage, such as a hard drive, when possible versus using a temporary storage such as RAM. When you are dealing with something such as the date and time, as in your example, you need to ensure that the values for the date and time are accurate, which means that they must not have the opportunity to be either lost or interrupted. If the values were stored on a temporary storage medium such as in the RAM then those values are at risk of being either deleted to make room for new values or they may be placed at the end of the priority list; therefore, they may become inaccurate. Using an internal storage device, such as a hard drive, avoids these risks. When dealing with the hard drives it is important to find a good manufacturer to ensure your hard drive does not crash or break, which would cause a lot of issues and data loss. Many companies, including Makuta Technics, make parts for hard drives. I encourage everybody to research hard drive producing companies to see which brand they feel best fits their needs.