December 2010 Blog Posts
Today I went for investigating the internal storage of DATETIME2 datatype. What I found out was that for a datetime2 value with precision 0 (seconds only), SQL Server need 6 bytes to represent the value, but stores 7 bytes.
This is because SQL Server add one byte that holds the precision for the datetime2 value.
Start with this very simple repro
declare @now datetimeoffset(7) = '2010-12-15 21:04:03.6934231 +03:30'
select cast(cast(@now as datetimeoffset(0)) as binary(9)),
cast(cast(@now as datetimeoffset(1)) as binary(9)),
cast(cast(@now as datetimeoffset(2)) as binary(9)),
cast(cast(@now as datetimeoffset(3)) as binary(10)),
cast(cast(@now as datetimeoffset(4)) as binary(10)),
cast(cast(@now as datetimeoffset(5)) as binary(11)),
cast(cast(@now as datetimeoffset(6)) as binary(11)),
...
Today I went for investigating the internal storage of DATETIME2 datatype. What I found out was that for a datetime2 value with precision 0 (seconds only), SQL Server need 6 bytes to represent the value, but stores 7 bytes.
This is because SQL Server add one byte that holds the precision for the datetime2 value.
Start with this very simple repro
declare @now datetime2(7) = '2010-12-15 21:04:03.6934231'
select cast(cast(@now as datetime2(0)) as binary(7)),
cast(cast(@now as datetime2(1)) as binary(7)),
cast(cast(@now as datetime2(2)) as binary(7)),
cast(cast(@now as datetime2(3)) as binary(8)),
cast(cast(@now as datetime2(4)) as binary(8)),
cast(cast(@now as datetime2(5)) as binary(9)),
cast(cast(@now as datetime2(6)) as binary(9)),
cast(cast(@now...
A year or so back, I struggled with some consistency problems so I figured out I needed a way to "mount" backup files as a virtual database. At the time (SQL Server 2005 and SQL Server 2008) my choice fell on Idera's SQLvdb because it felt easy enough to use.
I used it a few times and it worked great. Some time later we upgraded to SQL Server 2008R2 and I didn't use SQLvbd for a long time. Until yesterday...
I was upset that suddenly SQLvbd took more than 2 hours to mount the backup file (if it succeeded at all). I...