Sample Header Ad - 728x90

How does the arithmetic for how many bytes are saved by row-compressing a datetime add up?

0 votes
1 answer
135 views
Everything that follows is quoted from the datetime row [here](https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/row-compression-implementation?view=sql-server-ver16#how-row-compression-affects-storage) . I have broken the quote to give commentary on my understanding. > Uses the integer data representation by using two 4-byte integers. The integer value represents the number of days with base date of 1900-01-01. In other words, we have eight bytes to work with. This is exactly the same as what datetime already takes up. > The first 2 bytes can represent up to the year 2079. Compression can always save 2 bytes here until that point. In other words, until 2079 we are losing two bytes to the year. After that point, we will lose more. This means that for dates before 2079, we have six of our original eight bytes to work with. > Each integer value represents 3.33 milliseconds. Compression exhausts the first 2 bytes in first five minutes [sic] and needs the fourth byte after 4PM. In other words, after 4 PM we only have two of our remaining six bytes to work with. > Therefore, compression can save only 1 byte after 4PM. **This is clearly the part I misunderstand**. My earlier arithmetic proves that we have two byes left. However, Microsoft says that we only have one. This is doubly confusing, because if we are past 2079 then Microsoft's numbers would put us as having negative one bytes left. > When datetime is compressed like any other integer, compression saves 2 bytes in the date. The user has no control over how the compression works, so this line is entirely irrelevant. From the above, it's evident that I have a great misunderstanding. What have I missed?
Asked by J. Mini (1235 rep)
Apr 15, 2024, 08:36 PM
Last activity: Apr 16, 2024, 12:54 PM