DATETIME as Primary Key

Hello All,
We have a strange issue.
Environment:
SQL 2K SP3a
TableA Definitioin:
Col1 varchar(10) NOT NULL
Col2 datetime NOT NULL default getdate()
Primary Key col1 and col2
Many inserts are occuring and we are receiving primary key vilolations.
The Insert statement does not explicitly select the datetime just uses the
default on the column declaration.
I.E INSERT TABLE1(col1)
SELECT 'TEST' FROM MyTable
Anyone have an idea why?Fred,
datetime measures time only in increments of 1/300 of a
second. Many rows can be inserted within a single "tick"
of datetime, and apparently this is the case for you, some
of them having matching Col1 values.
It sounds like (Col1, Col2) is not a good choice of primary key.
Steve Kass
Drew University
FredG wrote:

>Hello All,
>We have a strange issue.
>Environment:
>SQL 2K SP3a
>TableA Definitioin:
>Col1 varchar(10) NOT NULL
>Col2 datetime NOT NULL default getdate()
>Primary Key col1 and col2
>Many inserts are occuring and we are receiving primary key vilolations.
>The Insert statement does not explicitly select the datetime just uses the
>default on the column declaration.
>I.E INSERT TABLE1(col1)
> SELECT 'TEST' FROM MyTable
>Anyone have an idea why?
>
>|||Hi Steve,
That makes great sense. So, if the inserts are happening very frequently,
within that 1/300 of a second window, this would cause the error.
"Steve Kass" wrote:

> Fred,
> datetime measures time only in increments of 1/300 of a
> second. Many rows can be inserted within a single "tick"
> of datetime, and apparently this is the case for you, some
> of them having matching Col1 values.
> It sounds like (Col1, Col2) is not a good choice of primary key.
> Steve Kass
> Drew University
> FredG wrote:
>
>|||That's because rows (with the same Col1 value) are being inserted
within 300 milliseconds of each other
Denis the SQL Menace
http://sqlservercode.blogspot.com/
FredG wrote:
> Hello All,
> We have a strange issue.
> Environment:
> SQL 2K SP3a
> TableA Definitioin:
> Col1 varchar(10) NOT NULL
> Col2 datetime NOT NULL default getdate()
> Primary Key col1 and col2
> Many inserts are occuring and we are receiving primary key vilolations.
> The Insert statement does not explicitly select the datetime just uses the
> default on the column declaration.
> I.E INSERT TABLE1(col1)
> SELECT 'TEST' FROM MyTable
> Anyone have an idea why?|||FredG wrote:
> Hello All,
> We have a strange issue.
> Environment:
> SQL 2K SP3a
> TableA Definitioin:
> Col1 varchar(10) NOT NULL
> Col2 datetime NOT NULL default getdate()
> Primary Key col1 and col2
> Many inserts are occuring and we are receiving primary key vilolations.
> The Insert statement does not explicitly select the datetime just uses the
> default on the column declaration.
> I.E INSERT TABLE1(col1)
> SELECT 'TEST' FROM MyTable
> Anyone have an idea why?
If your inserts occur less than 1/300th of a second apart or if you do
multiple row inserts then you will get duplicates. Also you've used
local time, which means you could get duplicates if your local time is
adjusted for DST and then back again.
If you need to record time more precisely then you'll have to populate
some other time value without relying on GETDATE() and DATETIME.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi David,
You mention that duplicate values would be generated because of DST.
How would you get around this problem? I've never really thought about
it until seeing your post.
I don't have a specific example or problem - just curious really.
Thanks
Barry|||Barry wrote:
> Hi David,
> You mention that duplicate values would be generated because of DST.
> How would you get around this problem? I've never really thought about
> it until seeing your post.
Always make sure that your system is down for maintenance during that
one hour window each year :-)
-Tom.|||Ha! I like your style...just kick the plug out ;-)|||Barry wrote:
> Hi David,
> You mention that duplicate values would be generated because of DST.
> How would you get around this problem? I've never really thought about
> it until seeing your post.
> I don't have a specific example or problem - just curious really.
> Thanks
> Barry
Use GETUTCDATE() instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Ah right ok.
Thanks
Barry