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
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
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
Legacy Comments
|
Jerry2
2012-02-09 |
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. |