datetime issues in primary key
-
Bad form!!! A PK should ALWAYS be unique. A datetime data type can not be guranteed to be unique.
When was the last time you poured some wine for you and your sweetie and went out on the front porch to watch the geometry frolic on the lake?--Rebecca M. Riordan, Designing Effective Database Systems
Well I didn't choose this and I'm just the c# developer, but Its my task to find out why. Thier argument is this. 2 inserts will never be done in less than a 15 second time frame so in theory it should be okay. But, were still getting inserts. Is there any documents that explain why this is bad, so I can share this with the DBA to get it changed? And accorcding to the doc if a row is 1 second difference then it shouldn't matter. Thanks, Nick
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
-
Well I didn't choose this and I'm just the c# developer, but Its my task to find out why. Thier argument is this. 2 inserts will never be done in less than a 15 second time frame so in theory it should be okay. But, were still getting inserts. Is there any documents that explain why this is bad, so I can share this with the DBA to get it changed? And accorcding to the doc if a row is 1 second difference then it shouldn't matter. Thanks, Nick
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
I believe Ennis and I have given you the best reason, but if you need further explanation you can start here.[^]
When was the last time you poured some wine for you and your sweetie and went out on the front porch to watch the geometry frolic on the lake?--Rebecca M. Riordan, Designing Effective Database Systems
-
Well I didn't choose this and I'm just the c# developer, but Its my task to find out why. Thier argument is this. 2 inserts will never be done in less than a 15 second time frame so in theory it should be okay. But, were still getting inserts. Is there any documents that explain why this is bad, so I can share this with the DBA to get it changed? And accorcding to the doc if a row is 1 second difference then it shouldn't matter. Thanks, Nick
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
Ista wrote:
2 inserts will never be done in less than a 15 second time frame so in theory it should be okay
How can they be sure that this is always going to be the case? I'd never think of using a datetime as a primary key. It is bad practice.
I'd like to help but I don't feel like Googling it for you.
-
Ista wrote:
2 inserts will never be done in less than a 15 second time frame so in theory it should be okay
How can they be sure that this is always going to be the case? I'd never think of using a datetime as a primary key. It is bad practice.
I'd like to help but I don't feel like Googling it for you.
Well I agree with you I and I would never use datetime as a part of a key, but this is in production for 5 years before I got here. And yes the way it works, it would take a miracle from a higher power to create one that fast. But, my delimna is that I know its wrong. But, I'm am merely the c# developer and its not good for a c# developer to tell the DBA ( which is the CIO ) that its wrong with out some sort of document to prove that its wrong other than some opinionated articles. Even though, its wrong and I see why, it takes proof to change that and tell someone thier idea is faulty. thanks
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
-
Well I agree with you I and I would never use datetime as a part of a key, but this is in production for 5 years before I got here. And yes the way it works, it would take a miracle from a higher power to create one that fast. But, my delimna is that I know its wrong. But, I'm am merely the c# developer and its not good for a c# developer to tell the DBA ( which is the CIO ) that its wrong with out some sort of document to prove that its wrong other than some opinionated articles. Even though, its wrong and I see why, it takes proof to change that and tell someone thier idea is faulty. thanks
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
Ista wrote:
Even though, its wrong and I see why, it takes proof to change that and tell someone thier idea is faulty.
Hopefully, your DBA/CIO should accept some constructive criticism that his or her idea could be improved. I wouldn't say it is faulty. Maybe five years ago using a datetime as a PK was okay, but I still wouldn't use it. Blurb from Microsoft[^]
I'd like to help but I don't feel like Googling it for you.
Last modified: Thursday, August 17, 2006 1:21:30 PM --
-
We have a datetime issue. The primary key uses a datetime data type of the primary key. The times are different by more than 3 milliseconds. We have wait 15 seconds between inserts and it fails. Are there any settings for the database or what not that would cause this behavior. ( we are not using smalldatetime either ) Thanks, Nick
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
Ista wrote:
We have wait 15 seconds between inserts and it fails.
What is the error?
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
Well I agree with you I and I would never use datetime as a part of a key, but this is in production for 5 years before I got here. And yes the way it works, it would take a miracle from a higher power to create one that fast. But, my delimna is that I know its wrong. But, I'm am merely the c# developer and its not good for a c# developer to tell the DBA ( which is the CIO ) that its wrong with out some sort of document to prove that its wrong other than some opinionated articles. Even though, its wrong and I see why, it takes proof to change that and tell someone thier idea is faulty. thanks
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
1 insert every 15 seconds for 5 years = 691,00010 million (ID-10-T error) records so it isn't that large of a table. Append an autonumber, make the autonumber the primary key and add an index on the date field. It will take less time to implement the solution than to argue the merits of either case. -- modified at 15:10 Thursday 17th August, 2006
A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
-
Ista wrote:
We have wait 15 seconds between inserts and it fails.
What is the error?
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
It says a primary key violation. The only different field is the DateTime value. And the date time milli seconds are different
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
-
1 insert every 15 seconds for 5 years = 691,00010 million (ID-10-T error) records so it isn't that large of a table. Append an autonumber, make the autonumber the primary key and add an index on the date field. It will take less time to implement the solution than to argue the merits of either case. -- modified at 15:10 Thursday 17th August, 2006
A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
yeah thats what I said. I told them to change it to an ID field and they told me that wouldn't make it unique. I give up
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
-
yeah thats what I said. I told them to change it to an ID field and they told me that wouldn't make it unique. I give up
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
Some people are stupid beyond help. For your situation try writing a before trigger that does a loop that expires 15 seconds before allowing an insert. That will eliminate the problem. Heavily comment why you did it so you will not be made fun of years from know when someone reads it.
A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
-
Some people are stupid beyond help. For your situation try writing a before trigger that does a loop that expires 15 seconds before allowing an insert. That will eliminate the problem. Heavily comment why you did it so you will not be made fun of years from know when someone reads it.
A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
Ennis Ray Lynch, Jr. wrote:
Some people are stupid beyond help
I feel sorry for the situation he's in.
Ennis Ray Lynch, Jr. wrote:
Heavily comment why you did it so you will not be made fun of years from know when someone reads it.
Start looking for a new job afterwards :) Who knows what other b.s. will pop up :~
too much daily WTF for someone... - Anton Afanasyev
-
It says a primary key violation. The only different field is the DateTime value. And the date time milli seconds are different
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
Whilst I agree with the other guys that datetime fields are not ideal for a PK, they should be able to provide what you need under the circumstances described. I have a few questions that may help clarify/resolve the issue. Has it only just started happening as you say that the code is 5 years old? Does it always cause a PK violation? Are you only inserting into 1 table? Are all of the datetime values obtained from the same source? Is the datetime value different for each insert? Ian
-
It says a primary key violation. The only different field is the DateTime value. And the date time milli seconds are different
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
create table MyTable
(
MyCol datetime
PRIMARY KEY CLUSTERED
)insert into MyTable (MyCol) values ('2006-08-17 15:36:13.779')The value is rounded to '2006-08-17 15:36:13.780'insert into MyTable (MyCol) values ('2006-08-17 15:36:13.781')the value is rounded to '2006-08-17 15:36:13.780' Strange...not sure why this is. No time to research right now. But, this may be what is causing the primary key violation.--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
Whilst I agree with the other guys that datetime fields are not ideal for a PK, they should be able to provide what you need under the circumstances described. I have a few questions that may help clarify/resolve the issue. Has it only just started happening as you say that the code is 5 years old? Does it always cause a PK violation? Are you only inserting into 1 table? Are all of the datetime values obtained from the same source? Is the datetime value different for each insert? Ian
i.j.russell wrote:
Has it only just started happening as you say that the code is 5 years old?
Well it appears that on the development server the primary key exists, but on the rest of the servers(staging, QA, and production) it has either been deleted or never published. Probably it wasn't working in a roll-out and never got put back in.
i.j.russell wrote:
Does it always cause a PK violation?
Always. They have managed to get the row inserted with the violation. Since The stored procedure calls another stored procedure to do the actual insert
i.j.russell wrote:
Are you only inserting into 1 table?
Yes
i.j.russell wrote:
Are all of the datetime values obtained from the same source?
Yes, there is a GetDate() inside the insert statement.
i.j.russell wrote:
Is the datetime value different for each insert?
We have waited from 15 seconds to a few minutes and we always get the issue. I realize, like everyone else, that this isn't advisable but this method seems to be carved in stone. Since I recommend an int column and they tell me that can't be the problem. If the world was a best case scenario, we probably wouldn't have a job to go to. If you can tell me why this is bugging out when I'm not violating the date millisecond issue. The PK is has 7 columns in it. Each isd an int field and thier exactly the same except for the GetDate() column, which is at the end -- modified at 17:06 Thursday 17th August, 2006
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
-
create table MyTable
(
MyCol datetime
PRIMARY KEY CLUSTERED
)insert into MyTable (MyCol) values ('2006-08-17 15:36:13.779')The value is rounded to '2006-08-17 15:36:13.780'insert into MyTable (MyCol) values ('2006-08-17 15:36:13.781')the value is rounded to '2006-08-17 15:36:13.780' Strange...not sure why this is. No time to research right now. But, this may be what is causing the primary key violation.--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
yeah every 3 milliseconds is treated as the same number. I found tons of documents at MSDN for it
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
-
Whilst I agree with the other guys that datetime fields are not ideal for a PK, they should be able to provide what you need under the circumstances described. I have a few questions that may help clarify/resolve the issue. Has it only just started happening as you say that the code is 5 years old? Does it always cause a PK violation? Are you only inserting into 1 table? Are all of the datetime values obtained from the same source? Is the datetime value different for each insert? Ian
Well I thank you for trying to help. The problem actually was that we had bad test data. It tried to update many rows and couldn't and somehow forced at PK issue. Odd. It works fine now. And they seems to think its okay to keep it datetime. I guess once a customer calls in they will want to change it. Thanks.
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
-
yeah every 3 milliseconds is treated as the same number. I found tons of documents at MSDN for it
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
What are the exact times that are causing problems in your table?
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters