DateTime primary key not accurate to fractions of second

[Ed White]

I have a table set up where the primary key is a DateTime field. According
to the SQL Server documention, the DateTime field can store data with up to 3
digits representing fractional seconds. The problem I'm having is that if I
have two dates that are the exactly the same except for one millisecond, SQL
Server is treating them as the same date and I'm getting a 'cannot insert
duplicate key' violation. For example, these two dates should be different
2009-03-26 08:57:55.843
2009-03-26 08:57:55.844 (the last digit is different)
but SQL Server gives me a 'cannot insert duplicate key' if I try to enter
rows with those two dates in them.

However, if in SSMS I manually enter the second date as
2009-03-26 08:57:55.845
SQL Server changes it to
2009-03-26 08:57:55.847
and does not give a primary key violation.

So apparently, SQL Server's DateTime is not accurate to milleseconds, but
maybe accurate to 4 milliseconds. Is this correct? Is there a way to get
around this? (And yes, I need the dates to be that precise).

[Plamen Ratchev]

The accuracy of the DATETIME data type is 3.33 ms. You can read more
details in Tibor's article:
http://www.karaszi.com/SQLServer/info_datetime.asp

In SQL Server 2008 you can use the DATETIME2 data type which has
accuracy to 100ns.

One way to solve this on prior versions is to store the milliseconds as
a separate column (probably as INT data type) and keep the original
column trimmed at seconds. Then you can have unique constraint or
primary key on the two columns.

[Ed White]

I have SQL Server 2008, so I'll use the DATETIME2...thanks.

Post by Plamen Ratchev
The accuracy of the DATETIME data type is 3.33 ms. You can read more
http://www.karaszi.com/SQLServer/info_datetime.asp
In SQL Server 2008 you can use the DATETIME2 data type which has
accuracy to 100ns.
One way to solve this on prior versions is to store the milliseconds as
a separate column (probably as INT data type) and keep the original
column trimmed at seconds. Then you can have unique constraint or
primary key on the two columns.
--
Plamen Ratchev
http://www.SQLStudio.com

[Russell Fields]

Note that the DATETIME2 precision depends upon the Windows version and the
hardware.
http://technet.microsoft.com/en-us/library/ms186724.aspx
Look for: Higher-Precision System Date and Time Functions

Here is a discussion where this was explored a bit.
http://www.developersdex.com/sql/message.asp?p=581&r=6601914

Just check to make sure that your configuration is giving you the precision
you want. (It probably is, but check now and be sure.)

RLF

Post by Ed White
I have SQL Server 2008, so I'll use the DATETIME2...thanks.

Post by Plamen Ratchev
The accuracy of the DATETIME data type is 3.33 ms. You can read more
http://www.karaszi.com/SQLServer/info_datetime.asp
In SQL Server 2008 you can use the DATETIME2 data type which has
accuracy to 100ns.
One way to solve this on prior versions is to store the milliseconds as
a separate column (probably as INT data type) and keep the original
column trimmed at seconds. Then you can have unique constraint or
primary key on the two columns.
--
Plamen Ratchev
http://www.SQLStudio.com

[Plamen Ratchev]

Just to clarify - the DATETIME2 data type precision does not depends on
the Windows version and the hardware. This only applies if you use the
functions to generate the date/time value. If you are inserting directly
a value it does not affect the precision.

[Ed White]

I believe I understand. I'm just using SQL's INSERT command to put the data
into the DB, and I'm not doing any calculations (other than sorting) based
upon the DateTime2 data.

Post by Plamen Ratchev
Just to clarify - the DATETIME2 data type precision does not depends on
the Windows version and the hardware. This only applies if you use the
functions to generate the date/time value. If you are inserting directly
a value it does not affect the precision.
--
Plamen Ratchev
http://www.SQLStudio.com

[Russell Fields]

Plamen, Thanks, that makes it clearer. - RLF

Post by Plamen Ratchev
Just to clarify - the DATETIME2 data type precision does not depends on
the Windows version and the hardware. This only applies if you use the
functions to generate the date/time value. If you are inserting directly a
value it does not affect the precision.
--
Plamen Ratchev
http://www.SQLStudio.com

[Ed White]

I'm using VB (VS 2008 SP1) to work with SQL Server 2008, and the problem I'm
finding is that VB's TimeSpan function is accurate to only 1 millisecond, so
I don't see how I in my VB code I can work with the higher accuracy in SQL
Server's DateTime2. For example, is it possible to compare whether two dates
are .0001 seconds apart in VB?

Post by Russell Fields
Plamen, Thanks, that makes it clearer. - RLF

Post by Plamen Ratchev
Just to clarify - the DATETIME2 data type precision does not depends on
the Windows version and the hardware. This only applies if you use the
functions to generate the date/time value. If you are inserting directly a
value it does not affect the precision.
--
Plamen Ratchev
http://www.SQLStudio.com

[Aaron Bertrand [SQL Server MVP]]

Sure, if you pull the datetime value to the highest accuracy VB will allow,
then pull DATEPART(MICROSECOND as a separate column.


On 3/26/09 3:08 PM, in article

Post by Ed White
I'm using VB (VS 2008 SP1) to work with SQL Server 2008, and the problem I'm
finding is that VB's TimeSpan function is accurate to only 1 millisecond, so
I don't see how I in my VB code I can work with the higher accuracy in SQL
Server's DateTime2. For example, is it possible to compare whether two dates
are .0001 seconds apart in VB?

Post by Russell Fields
Plamen, Thanks, that makes it clearer. - RLF

Post by Plamen Ratchev
Just to clarify - the DATETIME2 data type precision does not depends on
the Windows version and the hardware. This only applies if you use the
functions to generate the date/time value. If you are inserting directly a
value it does not affect the precision.
--
Plamen Ratchev
http://www.SQLStudio.com

[Plamen Ratchev]

Seems TimeSpan has the same accuracy to 100ns as DATETIME2:
http://msdn.microsoft.com/en-us/library/system.timespan.aspx

[--CELKO--]

Let me scare you a bit more about the decision to use SQL Server. The
FIPS and NIST Standards require that times be kept to at least FIVE
decimal seconds.

SQL Server grew up in the days of early UNIX on 16-bit machinery; the
legacy shows even today with 64-bit hardware and changing standards.
This is why 2008 added lots of new stuff and you can expect what you
have to deprecate.

Be afraid, be very afraid :)

[Ed White]

Thanks for the warning. I set up a test table with one field as DateTime2 as
the primary key, and entered two rows
2009-03-26 08:57:55.8430000
2009-03-26 08:57:55.8430001
and it didn't give me a duplicate key error. I'm running XP Professional
SP3 and SQL Server 2008 v. 10.0.1600.22, which should be the version with the
latest service pack, so I should be OK.

Post by Russell Fields
Note that the DATETIME2 precision depends upon the Windows version and the
hardware.
http://technet.microsoft.com/en-us/library/ms186724.aspx
Look for: Higher-Precision System Date and Time Functions
Here is a discussion where this was explored a bit.
http://www.developersdex.com/sql/message.asp?p=581&r=6601914
Just check to make sure that your configuration is giving you the precision
you want. (It probably is, but check now and be sure.)
RLF

Post by Ed White
I have SQL Server 2008, so I'll use the DATETIME2...thanks.

Post by Plamen Ratchev
The accuracy of the DATETIME data type is 3.33 ms. You can read more
http://www.karaszi.com/SQLServer/info_datetime.asp
In SQL Server 2008 you can use the DATETIME2 data type which has
accuracy to 100ns.
One way to solve this on prior versions is to store the milliseconds as
a separate column (probably as INT data type) and keep the original
column trimmed at seconds. Then you can have unique constraint or
primary key on the two columns.
--
Plamen Ratchev
http://www.SQLStudio.com

[Aaron Bertrand [SQL Server MVP]]

Post by Ed White
and it didn't give me a duplicate key error. I'm running XP Professional
SP3 and SQL Server 2008 v. 10.0.1600.22, which should be the version with the
latest service pack, so I should be OK.

There have been four cumulative updates, and Service Pack 1 is on its way.
The latest CU is 10.0.1798 I believe, and the CTP for SP1 was 10.0.2520. I
doubt any of these updates will affect the way DATETIME2 works, unless there
was a fix to some issue I did not notice in the readme/fix lists...

[Eric Russell]

Considering all the variations in implementation of the datetime datatype
across different SQL Server versions (like millisecond granularity for
example) and other database platforms like Oracle and MySQL, it may be a good
idea not to use datetime, even the newer 2008 versions, as a primary key,
because you never know where this key may end up. For example, if you extract
your table to flat text and then bulk load into Oracle, I could easily see
your key values loosing resolution and somehow getting mangled in the
process, thus breaking the relationship back to the master table.

Consider if it's feasible to redefine your primary as an 8 byte integer
(bigint in SQL Server), and then store the timestamp as an offset in
milliseconds.

The UDF below is just something I hacked together in 5 minutes, so forgive
me if my calculation is off, but you get the idea.

if object_id('dbo.fnGetOffsetTimeStampAsBigInt','FN') is not null
drop function dbo.fnGetOffsetTimeStampAsBigInt;
GO
create function dbo.fnGetOffsetTimeStampAsBigInt
(
@offsetdate datetime = null,
@timestamp datetime
)
returns bigint
as
begin
declare @TimeStampAsBigInt bigint;
select @offsetdate = isnull(@offsetdate,'2000/01/01');
select @TimeStampAsBigInt = cast(datediff(day,@offsetdate,getdate()) as
bigint) * cast(86400000 as bigint);
select @TimeStampAsBigInt = @TimeStampAsBigInt +
datediff(millisecond,convert(char(10),getdate(),112),getdate());
return(@TimeStampAsBigInt)
end;
GO

select dbo.fnGetOffsetTimeStampAsBigInt( default, getdate() );
waitfor delay '00:00:00:003';
select dbo.fnGetOffsetTimeStampAsBigInt( default, getdate() );
waitfor delay '00:00:00:003';
select dbo.fnGetOffsetTimeStampAsBigInt( default, getdate() );

--------------------
291460997693

--------------------
291460997710

--------------------
291460997726

Post by Plamen Ratchev
The accuracy of the DATETIME data type is 3.33 ms. You can read more
http://www.karaszi.com/SQLServer/info_datetime.asp
In SQL Server 2008 you can use the DATETIME2 data type which has
accuracy to 100ns.
One way to solve this on prior versions is to store the milliseconds as
a separate column (probably as INT data type) and keep the original
column trimmed at seconds. Then you can have unique constraint or
primary key on the two columns.
--
Plamen Ratchev
http://www.SQLStudio.com