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. For clarification, the prefix byte is only used when converting the value to it’s binary representation. The prefix in a table is stored in the metadata and not in the actual value itself.
Start with this very simple repro
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 doesn’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 75,844 seconds 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.