May 3, 2011 at 5:40 am
Hi
We have a production DB in SQL server 2005.In most of the table we generating the primary key through this Code..
DECLARE @ID int
-- Get Next ID
SELECT @ID= ISNULL(MAX(ID),0) + 1 FROM table
From last 2 -3 months there is lot of users accessing our application..today we got a problem like this in application
[p]
Violation of PRIMARY KEY constraint 'PK_tablename. Cannot insert duplicate key in object 'dbo.tablename'.
[/p]
Is this error is caused by the above code.For eg;when one user come and generate the primary key he has to first get the maximum of ID from the table...at the same time if another user come is it possible they get the same maximun ID and at the time of insertion both inserting the same key..which causes the duplication.
There is no BEGIN Trasaction statement...in the code...BEGIN Transaction statement is handled at the Application side.
Please provide some inputs on the above situation
Is there any better way to autogenerating the primary key other than the IDENTITY
Thank you
with regards
Dilip D
May 3, 2011 at 6:06 am
It is very likely that the situation you described caused the problem. What you should consider is changing the column ID from what seems INT into IDENTITY(1,1) or UNIQUEIDENTIFIER. I would read a lot into these options I made and how to change the existing table column.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
May 3, 2011 at 9:58 am
What is wrong with identity? It is designed for just this purpose, creating a new unique value without intervention. I would tend to shy away from using uniqueidentifier as a primary key. There are plenty of articles on this site about why it is not a good choice.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 4, 2011 at 6:04 am
Not sure who you replied to, but it seems like the problem issued here is purely an own constructed identifier. I only suggested two options, while I stay neutral to use which type.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
May 4, 2011 at 6:16 am
Ronald H (5/4/2011)
Not sure who you replied to, but it seems like the problem issued here is purely an own constructed identifier. I only suggested two options, while I stay neutral to use which type.
It wasn't aimed at you.
Just asking the OP why he had rejected the identity option for his needs.
May 4, 2011 at 6:44 am
I once had a colleague used Excel who only knew how to use basic arithmetic functions (add, substract, etc) and passed by about any function Excel has and he still was able to develop a complex spreadsheet. Same way goes with SQL Server... You don't need to know basic knowledge and still be able to develop a (mostly) working database. I just hope he didn't develop the database himself.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
May 4, 2011 at 7:41 am
Ninja's_RGR'us (5/4/2011)
Ronald H (5/4/2011)
Not sure who you replied to, but it seems like the problem issued here is purely an own constructed identifier. I only suggested two options, while I stay neutral to use which type.It wasn't aimed at you.
Just asking the OP why he had rejected the identity option for his needs.
That is correct and thanks for jumping in. Was in no way intended as an attack or anything along those lines.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply