Identity column

Question

Identity column

Kayal-717623

Ten Centuries

Points: 1390
More actions
June 3, 2008 at 6:58 am

#191300

I am in the process of creating few tables. I have internal keys say CustomerID which is an integer. Is it a good idea to make that field an identity column ? But i find many disadvantages using identity column, in replication and if had to delete a row, tht number is no longer available etc.. Or shud i add an alogorithm to increase the number by 1 instead of identity column whenever i do an insert to the table? Please advise.
Thanks,
Kayal

Viewing 15 posts - 1 through 15 (of 19 total)

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

Paul-474309 SSC Rookie Points: 30 More actions · Answer 1

Hi,

Use identity, replication is much more mature than your own procedures of creating an unique number probably will ever be (no disrespect intended).

If you are not looking for a identity use a customer number which should be 100% unique in that table. When I say 100% I mean 100% you will never be able to use a duplicate number.

best regards,

Paul

GSquared SSC Guru Points: 260824 More actions · Answer 2

You'll definitely get better performance from an Identity column than from something you create on your own.

- 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

Harit Gohel Ten Centuries Points: 1254 More actions · Answer 3

I would like to add one point about Identity column is that You can Re-use the deleted number by inserting Explicit values using

SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

ref link: http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

mailsar (6/3/2008)
I am in the process of creating few tables. I have internal keys say CustomerID which is an integer. Is it a good idea to make that field an identity column ? But i find many disadvantages using identity column, in replication and if had to delete a row, tht number is no longer available etc.. Or shud i add an alogorithm to increase the number by 1 instead of identity column whenever i do an insert to the table? Please advise.
Thanks,
Kayal

With regards to replication, be sure to mark the Identity column "Not for replication" so you don't get conflicts during the replication process (see this article for a better description of the issue). Otherwise, identity IMO is by far the most efficient way to maintain an autoincrement (over pretty much any other home-built method).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

noeld SSC Guru Points: 96590 More actions · Answer 5

Paul (6/3/2008)
Hi,
Use identity, replication is much more mature than your own procedures of creating an unique number probably will ever be (no disrespect intended).
If you are not looking for a identity use a customer number which should be 100% unique in that table. When I say 100% I mean 100% you will never be able to use a duplicate number.
best regards,
Paul

For replication purposes I have to disagree. Identity columns do create a lot of headaches and not precisely because of the "deletes" ... Although not impossible neither out this world, there are "many" things that can go wrong when using Identity and replication.

Just my $0.02

* Noel

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 6

I have never had any issues with identity columns and replication, and I have used replication extensively.

People seem to get hung up on identity columns having unused "gaps" in the values and I can never understand why. Who cares? The identity value is a surrogate identifier to automatically generate uniqueness. The value itself should really have no meaning.

GSquared SSC Guru Points: 260824 More actions · Answer 7

Gotta agree with Michael on this one. Gaps in identity don't matter, and that's a good thing.

- 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

noeld SSC Guru Points: 96590 More actions · Answer 8

Ok here we go:

If you happen to use the replica for "failover" you will have to "update" the status of your identity columns.

If you use triggers that insert from one table into an audit table you will be forced to mark the identity as not for replication.

If you happen to use filtered publications identity range handling will have to be very well thought and even then flexibility is limited

I could go on ... and on ...

I am not saying that it can not be done. I am just saying that it "DOES" brings complexity higher when used with replication. I couldn't care less about "gaps" in the values.

* Noel

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 9

I guess I don't consider these things that make replication more complicated, they are just things you have to do to configure and manage replication.

Creating some kind of custom identity routine seems like a lot more work than checking the "not for replication" checkbox, but that's just my opinion.

GSquared SSC Guru Points: 260824 More actions · Answer 10

As far as replication set-up goes, I haven't done it enough to have a strong opinion one way or the other.

My point was merely that "rolling your own" on an auto-increment column is going to be more problematic than using the built-in Identity property.

- 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

Kayal-717623 Ten Centuries Points: 1390 More actions · Answer 11

Thanks everybody for your suggestion, that helps. I am gonna go with the identity column and have not for replication set to yes.

Thank You guys. I really appreciate your help. You guys are awesome.

Kayal

noeld SSC Guru Points: 96590 More actions · Answer 12

The use of a "central" table to get_nextID is not that hard and is "portable". Of course it will never get closer to the simplicity of an identity column.

I was just giving an option because where I work after a lot and I mean *a lot* of publication management issues accross a very large number of servers we went for the custom Id solution.

There are places for which "identity" will be just fine!

* Noel

Kayal-717623 Ten Centuries Points: 1390 More actions · Answer 13

Hi noel,

I appreciate your suggestion.What do you mean by publication management issues?

Thanks,

Kayal

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 14

noeld (6/3/2008)
The use of a "central" table to get_nextID is not that hard and is "portable". Of course it will never get closer to the simplicity of an identity column.
I was just giving an option because where I work after a lot and I mean *a lot* of publication management issues accross a very large number of servers we went for the custom Id solution.
There are places for which "identity" will be just fine!

Does the use of NEWID() present the same issues as IDENTITY insofar as replication is concerned? Heh... Not that I'd ever want to do that, mind you... :hehe: I'm just curious...

--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)