Geek asks and answers

Datetime2 storage size

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

I need to store a large amount of data in binary form (concatenated values), and I like the idea of ​​using only 6 bytes for each day and time, however when trying:

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 obviously takes one extra byte, what am I doing wrong?

+5
source share
2 answers

, , /datalength varbinary 7 6 ( Mikael , varbinary ), , , . , 6 , ( , , ). ?

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;

:

6    7    6    7

, datetime2 6 , varbinary - 7 . . , . :

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

( ):

PagePID  PageType
283      10
311      1

, 311:

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

, datetime2 6 :

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
+10

,

-, , : " , ", , !

, DateTime2, , "CONVERT to binary" , , ! , varchar nvarchar, DateTime2. - DBCC PAGE. http://ariely.info/Blog/tabid/83/EntryId/162/Examine-how-DateTime2-type-stored-in-the-data-file.aspx

, : -)

0

More articles:


All Articles