November 19, 2003 at 10:59 am
I understand there was an issue with identity columns (the use of) in earlier versions of of SQL (7.0, 6.5) especially when it came to triggers, etc.
Is there any documentation regarding this anywhere?
What I'd like to do is challenge our standards team - who in our dB objects standard suggest that "identity columns are inherently flawed in providing a scalable solution to generating unique numeric identifiers. Identity columns produce hot spots (???)within data index pages. They also cause problems with triggers, data replication and synchronization..."
I feel that the issues they refer to are outdated and have been addressed by SQL 2000. Do you know where I might be able to find documentation regarding any of these claims? Currently, I cannot give my developers a good reason on why they should NOT use identity columns other than "cause our standards said so". I mean why not let the server(dB) do the work? All commentary is appreciated.
TIA,
aurora01
Aurora
November 20, 2003 at 1:46 am
I suspect that the problems with triggers may be because prior to SQL 2000, if a trigger (on a table with IDENTITY) happens to insert a record into another table that also has an IDENTITY column, you could only get the value of the last identity value generated which, in this case, would be the insert done by the trigger. This is still the case if you use @@IDENTITY.
However, SQL 2000 introduce a function called SCOPE_IDENTITY( ) which will give you the correct result.
Replication did present some issues in the past depending on what you were trying to do. SQL 2000 can now be told to manage identity ranges for subscribers who are doing inserts (e.g. queued subscribers).
I am not sure what issues your standards team are referring to wrt synchronization. I cannot think of any particular issues here (past or present).
And hot spots...well SQL 6.5 definitely did have some issues here but that was not so much to do with identity. It was more to do with system inserting lots of records into the same page (e.g. for a similar value of a clustered index). I don't observe any particular problems with this with SQL 2000 (or in SQL 7)
Have a read of book on line. It has quite good descriptions of the new features.
Cheers
Stephen
November 20, 2003 at 8:01 am
Thanks Stephen, I find the same to be true and that's why I am challenging (so to speak) the standard. One other base to cover though, what's the affect if doing some sort of "load" from an Oracle dB? I understand that Oracle has 'sequences' where SQL has identity. Anyone have any take on this?
TIA
aurora01
Aurora
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply