jh-638585 (9/27/2011)
Loved the article and, even more so, the fact that your brainwave has provoked such excellent feedback (haven't read it all yet mind).I'm on the programmer-side and, therefore, no DB expert but while I understand the issues associated isn't one of the key things about Identity columns that no matter what client (any number of bespoke clients or apps like Excel, etc.) attempts to insert data the uniqueness of the id field will be guaranteed by the database (single instance).
Set me straight here but isn't the alternative that all apps (app developers) must know more about the internal storage of data and isn't that 1. a bad thing 2. open to be abused.
Perhaps you suggest all identity/identifier fields should be calculated somehow using an appropriate guid-type creation function from within the database?
I have seen apps that use all sorts of unique record identifier functions that must be called by the client app before inserting data. I have seen many problems with all of these implementations but the consistent thing i noticed was that it was always difficult for other apps to use - most seems to decide that this database is only to be access from this application or instances of it. Regardless of database scalability and all that good stuff the idea that any significant database would be accessed by a single application (even a single language) over the course of the lifetime of the data is....limiting.
Again, very much enjoyed this lateral thinking article.
Hey jh, you are correct in what you assert for a single server environment. The issue here is really what happens when a database table spans multiple SQL Servers, either for temporary capacity or as a part of a failover procedure.
It was only a few years ago that this sort of argument was not even contemplated due of cost except in the biggest systems but with the dropping price of hardware & hosting, along with the increasing abilities of SQL Azure, it is becoming more and more of a consideration.
Even then the partitioning of a table ("vertical" partitioning rather than "horizontal" I guess) is really only an issue when you need to bring some or all of the partitioned data back onto a single DB server. Where a system is partitioned across multiple SQL Servers for temporary capacity increase, or when a DR site temporarily receives live transactions are examples of this. With identity columns on each server this means you almost always have key collisions.
There are ways to get around this, and the guid, sequential guid and generated ID methods are just some of them.
Hope this helps!