Identity column value should not be increased if there's pbm in insertion of record.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Identity column value should not be increased...

Identity column value should not be increased if there's pbm in insertion of record.

Rate Topic

Display Mode

Topic Options

Author

Message

Joy Smith San

Posted Monday, November 03, 2008 11:53 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:39 AM
Points: 1,380, Visits: 2,335

Dear All,

I have a table which has got an IDENTITY COLUMN. There is another column in the same table which is primary key.
When user inserts a new record, I don't check for the uniqness of the value inserted for the primary key field.
Instead I capture the error code when SQL server throws primary key constraint error (if a duplicate entry is made.).
It works fine...The problem is that, When a duplicate entry is made, the record is not entered...
but IT INCREASES THE IDENTITY COLUMN by 1. I don't want this. Am using the IDENTITY column value for display also.
So I want it in sequece. Is there any solution to this problemm.?
(ie, Identity column value should not be increased if there is any pbm in insertion of record.)

Thanks in advance,
Santhu.

Post #596334

ChiragNS

Posted Tuesday, November 04, 2008 12:43 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

you will have to validate your data being inserted before inserting.

"Keep Trying"

Post #596353

Joy Smith San

Posted Tuesday, November 04, 2008 12:53 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:39 AM
Points: 1,380, Visits: 2,335

Thank you.....

Infact I wanted to avoid that..At present I do that valdation only when error comes...So there's no way to achieve this..rite.?

Post #596362

GilaMonster

Posted Tuesday, November 04, 2008 1:14 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:37 AM
Points: 38,074, Visits: 30,369

nairsanthu1977 (11/4/2008)

Thank you.....
So there's no way to achieve this..rite.?

Not unless you want to do your own form of auto-increment column.
Why are the values of the identity column important?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #596369

Joy Smith San

Posted Tuesday, November 04, 2008 2:18 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:39 AM
Points: 1,380, Visits: 2,335

We use Identity column as 'CODE' s...(cost code, branch code, etc.)
I was against it, but manager insisted to use identity column for this.
Thats y IDENTITY column has become the key value in the database.
(By the way, The primary key I mentioned is not CODE but we made Description as Primary key....)

Post #596386

Michael Earl-395764

Posted Tuesday, November 04, 2008 5:21 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

Unfortunately, that is really a misuse of an identity column. The are designed to be used as meaningless primary keys so having gaps is unimportant. There is no real way around this. It is by design.

If you really need to ensure they are in order and have no gaps, you have to serialize inserts into the table. To do this, the most common practice is to keep another table with a single record that has the current identity value. When inserting a new record, lock the table that has the current identity value and use this value for your insert. Only unlock the current identity value table after you commit your record. This will ensure you cannot have a second insert before you commit.

I would not advocate this. The best solution is to ensure your identity values are meaningless and that gaps do not matter.

Post #596478

steveb.

Posted Tuesday, November 04, 2008 5:29 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 8:56 AM
Points: 2,802, Visits: 7,110

If you already have a primary key then what is the need for the Identity column?

if is for display purposes only , you could use ROW_NUMBER() and generate a sequence number when you run a query

Post #596484

Lowell

Posted Tuesday, November 04, 2008 6:25 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:22 PM
Points: 11,789, Visits: 28,063

can you change the way the data gets inserted? if an application is trying to do an INSERT, can you make it call a stored proc instead?
what happens if the value already exists? what do you do then?
do you just want to return the Identity that matches the PK you tried to insert?

in a procedure you could try something like
If Exists(select PK from YourTable where PK=@PK)
--Return the Indentity
ELSE
--INSERT the new PK

to avoid the Identity() being triggered on attempted insert.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #596515

« Prev Topic | Next Topic »

Permissions