patternsqlModerate
Are SQL Server's Date values stored as a single int?
Viewed 0 times
storedserveraresqldatesinglevaluesint
Problem
SQL Server stores
Is it correct (by logical extension) that
DATETIME internally as two INT values, as SQLDenis explains.Is it correct (by logical extension) that
DATE types (SQL Server 2008+) are stored as a single INT?Solution
Just for fun, let's see what's on the page.
Partial results (will differ on your system a bit):
So let's look at page 228:
Partial results. We see that the value for d occupies 3 bytes. If we look at the memory dump for the row, we see a hex value for the internal storage component:
Result:
So:
Result:
(Looks amazingly similar to what's on the page, just with bits flipped in the opposite order?)
Then:
Result:
CREATE DATABASE floob;
GO
USE floob;
GO
CREATE TABLE dbo.experiment(d DATE NOT NULL);
CREATE CLUSTERED INDEX x ON dbo.experiment(d);
INSERT dbo.experiment SELECT SYSDATETIME();
DBCC IND('floob', 'dbo.experiment', 1);Partial results (will differ on your system a bit):
----
13
PagePID iam_chain_type PageType
------- -------------- --------
229 In-row data 10
228 In-row data 1So let's look at page 228:
DBCC TRACEON(3604, -1);
DBCC PAGE(13, 1, 228, 3);Partial results. We see that the value for d occupies 3 bytes. If we look at the memory dump for the row, we see a hex value for the internal storage component:
c8350b02 - what could that be?SELECT DATEDIFF(DAY, CONVERT(DATE, '0001-01-01'), CONVERT(DATE, '2012-06-11'));Result:
734664So:
SELECT CONVERT(VARBINARY(8), 734664);Result:
0x000B35C8(Looks amazingly similar to what's on the page, just with bits flipped in the opposite order?)
Then:
SELECT DATEADD(DAY, CONVERT(INT, 0x000B35C8), CONVERT(DATE, '0001-01-01'))Result:
2012-06-11Code Snippets
CREATE DATABASE floob;
GO
USE floob;
GO
CREATE TABLE dbo.experiment(d DATE NOT NULL);
CREATE CLUSTERED INDEX x ON dbo.experiment(d);
INSERT dbo.experiment SELECT SYSDATETIME();
DBCC IND('floob', 'dbo.experiment', 1);----
13
PagePID iam_chain_type PageType
------- -------------- --------
229 In-row data 10
228 In-row data 1DBCC TRACEON(3604, -1);
DBCC PAGE(13, 1, 228, 3);SELECT DATEDIFF(DAY, CONVERT(DATE, '0001-01-01'), CONVERT(DATE, '2012-06-11'));SELECT CONVERT(VARBINARY(8), 734664);Context
StackExchange Database Administrators Q#19204, answer score: 11
Revisions (0)
No revisions yet.