July 30, 2008 at 10:56 pm
In my web based application Identity column is duplicated when simultaneously record are inserted by different machine
(i think so) i will solution for handling this condition .
July 31, 2008 at 12:12 am
Identity column is duplicated
Do you have any triggers?
If @@IDENTITY is used, replace it with SCOPE_IDENTITY()
July 31, 2008 at 5:37 am
When you say duplicated, do you mean in the data? I've never seen that in normal functions, it must be a bug or you're not using the IDENTITY setting and are instead using some sort of trigger or function. It's also possible you've hit a bug.
If it's a perceived duplication, meaning, the record goes in, but the values you're retrieving are duplicated, then I think the post above has the answer.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 31, 2008 at 5:54 am
The data in the Identity Column can be duplicated if it is Not a PK or the column is Not Uniquely Indexed. This can be checked by using
SET IDENTITY_INSERT dbo.TableName ON
But, generally, the Indentity Columns are defined as PKs (in most of the cases).
I think the duplication can be stopped by avoiding SET IDENTITY_INSERT dbo.TableName ON while inserting data and let the sql server decide the value to be inserted in the Identity column.
Atif Sheikh
July 31, 2008 at 6:18 am
IDENTITY columns do not duplicate, even without a constraint, when operating normally. You can force duplicates in by using IDENTITY_INSERT or by doing a RESEED, but the normal behavior is one of constantly increasing values, without duplication because of multiple inserts. None of these circumstances were in the OP.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 4, 2008 at 12:47 am
You are right Grant. I was just making clear that IDENTITY column may have duplicate values IFF the IDENTITY_INSERT is set to ON while inserting a record OR if RESEED option is used (as you mentioned, I have not checked it).
OP might be using one of these options while inserting records. Otherwise I donot see any resason for duplication in an IDENTITY Column.
Atif Sheikh
August 5, 2008 at 10:03 am
We are assuming the OP is saying;-
when a simultaneous insert occurs from two client appliactions
the id of the two inserted records in a/the table are identical.
This indicates no existing unique constraint on the coulmn, therfore its not a primary key.
and
The column in question probably is not set set for IDENTITY.
It sounds like a "get last value + 1" type insert........
Post the Create Table script please....:D
Hiding under a desk from SSIS Implemenation Work :crazy:
August 6, 2008 at 1:19 pm
Hi
you can check your Web page and prevent F5 , disable SUBMIT button(when submits)
Valentin
August 11, 2008 at 11:10 am
If you think your data is corrupted, look for DBCC CheckIdent
paul
August 17, 2008 at 6:01 pm
dolly (7/30/2008)
In my web based application Identity column is duplicated when simultaneously record are inserted by different machine(i think so) i will solution for handling this condition .
I don't think it's an "IDENTITY" column at all... I think that's what they calling it and someone is using MAX+1 or some other method, perhaps like a sequence table, to create "ID's".
Dolly... are you still there?
--Jeff Moden
August 18, 2008 at 1:18 am
From my post before yours Jeff I totally agree.
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
August 18, 2008 at 5:14 am
Shaun McGuile (8/18/2008)
From my post before yours Jeff I totally agree.--Shaun
Spot on, Shaun! I missed that... sorry. Good to see I'm not the only one.
Now, if the OP were only kind enough to respond. 😛
--Jeff Moden
August 18, 2008 at 5:43 am
No kidding, drop the grenade & run...
They could be doing something silly with reseed?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 18, 2008 at 6:35 am
Grant Fritchey (8/18/2008)
No kidding, drop the grenade & run...They could be doing something silly with reseed?
Thats not a grenade its a nuke......:D
Hiding under a desk from SSIS Implemenation Work :crazy:
August 18, 2008 at 5:37 pm
Shaun McGuile (8/18/2008)
Grant Fritchey (8/18/2008)
No kidding, drop the grenade & run...They could be doing something silly with reseed?
Thats not a grenade its a nuke......:D
COOL! Radioactive pork chops! I'm good with THAT! 😛
--Jeff Moden
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply