Identity column limitation

Question

Identity column limitation

Viewing 3 posts - 16 through 18 (of 18 total)

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

Dugi SSCoach Points: 17998 More actions · Answer 1

GilaMonster (1/12/2010)
Dugi (1/12/2010)
I said before that large and medium DBs have tables that the Identity Col has generated value with combination, and the last value can be sequence number that generated with any functions that plays like Identity incremental function.
A composite column (one constructed of more than one value) is a violation of first normal form and should not appear in a well-designed database. Setting up a column with a couple ov values and a sequence is difficult to get right and absolutely unnecessary (as well as being bad design)

I don't think so, I disagree with you in this case. I saw many professional DBs that has this logic and they works like a charm!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

And I've seen many where the developers thought it worked like a charm until suddenly there were duplicate key errors.

If you insist on maintaining the sequence yourself you've got to be very careful with the locking to ensure that two connections can't get the same value, both increment and insert. You've also got to be careful that the locking to prevent that doesn't cause major performance problems because of the locking. It can be done, but it's tricky to get right, far harder than making a column identity and letting SQL handle all the nasty stuff.

A composite column is a violation of 1st normal form. That's not a matter for discussion, one of the requirements for 1st normal form is that all column values are atomic.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Dugi SSCoach Points: 17998 More actions · Answer 3

A composite column is a violation of 1st normal form

As DBA I agree, as developer I cannot understand the developers, why they allow these things during the large Database design!?! I think that they have attention for duplications but they pass this problem, and the project manager allow them to do such things.

Finally interesting situation: In theory, practice is the same as theory. In practice, it differs!!! :w00t::hehe:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]