2

I've run across something that surprised me.

I am using WinPcap to collect data off a network. Internally, WinPcap uses the Windows performance counters to generate its timestamps. I know they are subject to drift, but these timestamps nevertheless have precision down to the microsecond level.

If I insert these timestamps into a SQL Server Compact 4.0 database as a datetime value and extract them later, I noticed that the precision has dropped to milliseconds.

For example, 

10:52:19.706084 -> 10:52:19.706000

Now, I have since read here that SQL Server rounds values with the datetime type to .000, .003, or .007 milliseconds. That explains what is happening.

Now, the datetime field uses 8 bytes to store its data, 4 bytes for the date and 4 for the milliseconds since midnight. But if I call DateTime.ToBinary(), I get back an 8-byte number that represents the value in all of its precision. In fact, if I write this value to the database in a bigint column and then call DateTime.FromBinary() when extracting that value, I get the original value with the same precision.

This is the approach I'm going to use, but I'm still curious: Why didn't the original datetime type in SQL Server Compact use DateTime's ToBinary/FromBinary storage mechanism?

EDIT:

As Aaron Bertrand rightly points out, SQL Compact does not support datetime2. Further, datetime2 uses 6, 7, or 8 bytes, not 54 bytes in regular SQL Server. My basic question still stands, though.

Matt Davis
  • 45,297
  • 16
  • 93
  • 124
  • This may help: http://stackoverflow.com/questions/12729972/allow-entity-framework-4-5-to-use-datetime2-with-sql-server-ce4 – Aaron Bertrand Aug 01 '13 at 02:46
  • Also I don't think Compact supports `datetime2`. And in "regular" SQL Server, it [certainly does not take 54 bytes (rather 6, 7 or 8 bytes)](http://msdn.microsoft.com/en-us/library/bb677335.aspx). Can you cite an official source for this `nvarchar(27)` bit? – Aaron Bertrand Aug 01 '13 at 02:53
  • Looking again, I think you're right on both counts. Here's where I was looking but apparently not close enough...http://msdn.microsoft.com/en-us/library/bb677335(v=sql.100).aspx – Matt Davis Aug 01 '13 at 02:57
  • Yeah that's the length of the string representation, not the storage size. – Aaron Bertrand Aug 01 '13 at 03:00
  • I've updated the question to be more precise in what I'm asking. It's more of a curiosity that anything. I was just surprised that inserting a timestamp into the database caused the value to lose data. And I don't understand that when the `DateTime` struct can encode/decode itself in 8 bytes and maintain precision. – Matt Davis Aug 01 '13 at 03:08
  • This might help as well: http://stackoverflow.com/questions/1143259/what-is-the-internal-representation-of-datetime-in-sql-server – Aaron Bertrand Aug 01 '13 at 03:16
  • I'm not sure why they chose the 4+4 storage mechanism for `datetime`, but at least they corrected it with `datetime2` (even if they chose a horrible name). Perhaps you could consider using SQL Server 2012 LocalDB instead of Compact, then you won't be limited by Compact's constant game of catch-up... – Aaron Bertrand Aug 01 '13 at 03:18
  • The accepted answer at the SO link you provided has a link to a pretty detailed writeup on the DATETIME type. A quick glance makes me think the 4+4 format was chosen to aid queries based on time. That at least seems plausible to me. Can you provide a little more detail on the 'LocalDB' option? Is it portable like Compact? – Matt Davis Aug 01 '13 at 03:25
  • 1
    yes, the entire runtime is 33 MB. [I wrote up a lengthy article about it here](http://www.mssqltips.com/sqlservertip/2694/getting-started-with-sql-server-2012-express-localdb/) and the [official documentation starts here](http://msdn.microsoft.com/en-us/library/hh510202.aspx). – Aaron Bertrand Aug 01 '13 at 03:27

1 Answers1

2

I don't know the full internal details or the motivation behind the choice, but datetime is stored internally as - essentially - two 4-byte integers. One represents date, the other represents time. I suspect you lose some precision in the latter because of the way ticks / milliseconds have been handled since the very first versions of SQL Server, but again, I don't know low-level implementation details.

Related questions for more background info:

In order to support the precision you want without moving the value in and out of binary format, I would suggest using LocalDB which has the same portability advantages of Compact but without many of the feature limitations (such as support for the more precise datetime2 type - which I assure you takes 6-8 bytes, not 54 :-)).

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490