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'
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
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.
|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.