Identity column gernerating non unique value

Question

Identity column gernerating non unique value

kotnala_manish

Grasshopper

Points: 11
More actions
March 15, 2011 at 6:15 am

#389051

I had created a sp which is creating a non unique value for n concuurent user using this table.Might be concurrency issue what it might be.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'd have to see the code before I could even begin to help on this. Can you post the table definition ("create script") and the code for the procedure?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Grant Fritchey SSC Guru Points: 399381 More actions · Answer 2

Are you doing anything at all to the identity column as part of your insert processes? I only ask because, while I've seen issues around identity, not generating a unique value is an extremely uncommon.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lowell SSC Guru Points: 323755 More actions · Answer 3

i suspect that instead of using a column with the identity() property, he's using a stored procedure to generate a sequence , and using the results of that stored proc to insert into his pseudo-identity column; and as a result he's getting duplicates;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 399381 More actions · Answer 4

that makes sense. I've heard of getting dupes in identity, but i think it was a one-off bug or something. Certainly nothing standard.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004706 More actions · Answer 5

kotnala_manish (3/15/2011)
I had created a sp which is creating a non unique value for n concuurent user using this table.Might be concurrency issue what it might be.

If you were to post the code for the part of the sp that does that, I can show you how to fix it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Brandie Tarvin SSC Guru Points: 173125 More actions · Answer 6

Just a thought. Identity_Insert could be set ON for that table and data coming from elsewhere is forcing duplicate values in.

Or someone might have reseeded the table.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.