DATETIME


Today I went for investigating the internal storage of DATETIME datatype. What I found out was that for a datetime value, SQL Server need 8 bytes to represent the value, and store 8 bytes.

Start with this very simple repro

Let us use the following color schema
Green – Time part
Blue – Day part

The difference with DATETIME compared to other data datatypes, is that the fractional part (time part) is stored in ticks and a tick has a default value of 3.3333…. milliseconds (300 ticks per second).
If you add multiply 22,753,108 ticks with 3.3333…. milliseconds, you get the value of 75,843,693 which is exactly the number of milliseconds since midnight (21:04:03.693).