datetime2 storage size

Question

Using SQL Server 2008 R2, SP2 The docs says that datetime2 takes 6, 7 or 8 bytes depending witch precision you use

I need to store a large amount of data in binary form (concatenated values) and I love the idea of using only 6 bytes for each datetime, however when I try:

declare @_dt_p0 datetime2(0) = '2012-05-18 11:22:33'
select  CONVERT(varbinary, @_dt_p0), LEN(CONVERT(varbinary, @_dt_p0))

declare @_dt_p4 datetime2(4) = '2012-05-18 11:22:33'
select  CONVERT(varbinary, @_dt_p4), LEN(CONVERT(varbinary, @_dt_p4))

declare @_dt_p7 datetime2(7) = '2012-05-18 11:22:33'
select  CONVERT(varbinary, @_dt_p7), LEN(CONVERT(varbinary, @_dt_p7))

It's clearly taking one extra byte, what I'm doing wrong?

Answer 1

I don't think I can explain why the length / datalength of a varbinary conversion is 7 instead of 6 (Mikael later found that the convert to varbinary adds the precision as an extra byte), but I don't know why you think that's a valid test anyway. I can confirm that 6 bytes are stored on the page when you are using an actual column (though null overhead for the row will be different depending on whether the column is nullable). How can I prove this?

USE tempdb;
GO

CREATE TABLE dbo.x
(
 d1 DATETIME2(0)  NULL, 
 v1 VARBINARY(32) NULL,
 d2 DATETIME2(0)  NOT NULL, 
 v2 VARBINARY(32) NOT NULL
);

declare @d datetime2(0) = '2012-05-18 11:22:33';

INSERT dbo.x(d1, v1, d2, v2)
SELECT @d, CONVERT(VARBINARY(32), @d), @d, CONVERT(VARBINARY(32), @d);

SELECT DATALENGTH(d1), DATALENGTH(v1), 
       DATALENGTH(d2), DATALENGTH(v2) FROM dbo.x;

Results:

6    7    6    7

So, the datetime2 columns are 6 bytes, but the varbinary columns are 7 bytes. Regardless of nullability. We can look closer by actually inspecting the page. Let's find all the pages in the heap for this table:

DBCC IND('tempdb', 'dbo.x', 0);

Partial results on my system (yours will be different):

PagePID  PageType
283      10
311      1

So now let's look at Page 311:

DBCC TRACEON(3604, -1);
DBCC PAGE(2, 1, 311, 3);

And we can see that the datetime2 columns indeed occupy 6 bytes on the page:

Slot 0 Column 1 Offset 0x4 Length 6 Length (physical) 6

d1 = 2012-05-18 11:22:33            

v1 = [Binary data] Slot 0 Column 2 Offset 0x19 Length 7 Length (physical) 7
v1 = 0x00f99f00b0350b               

Slot 0 Column 3 Offset 0xa Length 6 Length (physical) 6

d2 = 2012-05-18 11:22:33            

v2 = [Binary data] Slot 0 Column 4 Offset 0x20 Length 7 Length (physical) 7
v2 = 0x00f99f00b0350b              

Answer 2

Good day,

firstly i want to say that this is the best question : "I don't know why you think that's a valid test anyway", since the answer is that this is not valid test!

You can read all about the issue, including the explanation of DateTime2 real stored format, and why this is mistake to examine the result of "CONVERT to binary" and assume that this is the same as the actual stored data! It is not accurate in variable-length data like varchar or nvarchar and it is not accurate in DateTime2 as well. The only way to examine and get the real stored format is examine the data itself using DBCC PAGE. http://ariely.info/Blog/tabid/83/EntryId/162/Examine-how-DateTime2-type-stored-in-the-data-file.aspx

I hope this is useful :-)