The internal storage of a DATETIME2 value
- by Peter Larsson
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.
//Peter
