IDENTITY has a very important place in RDBMS and especially in SQL Server. It's very often the best candidate for a surrogate PK.
You might want to actually read Dr. Codd and any book on RDBMS. Please quote Dr. Codd on this topic in TODS, any presentation or any speech, show me where it is in his 12 rules, etc.
People get "exposed physical locator" and surrogate mixed up; they are totally different concepts. Let me start with an appeal to authority, with a quote from Dr. Codd:
To me, the above is like:
I'm telling you:
There is no God!
You telling me:
You might want to actually read the Bible...
Let me start with an appeal to authority, with a quote from Pope...
There are theories and there is a real life. And in the real life there are cases where you will have a great trouble finding Natural Keys. We had numerous discussion on this forum where I've tried to get from you an example of Natural Keys for very common database entities such as "Person" and "Company" and you didn't manage to advise anything useful
(something which will work internationally for people and for all companies sizes).
But let try another one. Let say Vatican wants a database of all Christian saints. What kind of Natural Key will you advise? SSN number will not work, regardless of what Dr. Codd might written in his nice books...
Also, there are solutions where IDENTITY is a best candidate for PK anyway - data warehouses. Can you advise anything better than identity for slowly changing dimensions?
There are other aspects which make IDENTITY to be very good PK and performance one of them...
I have proven to you once my googling skills, haven't I? So, you do like authorities... :
From C. J. Date's book "An Introduction to Database Systems, C J Date, 8th Edition":
Page 434, reference 14.21 - he refers to P. Hall, J. Owlett and S J P Todd "Relations and Entities" and says:
"Surrogate Keys are keys in the usual Relational sense but have the following specific properties:
They always involve exactly one attribute.
Their values serve solely as surrogate (hence the name) for the entities they stand for ......
When a new entity is inserted into the database, it is given a surrogate key value that has never been used before and will never be used again, even if the entity in question is subsequently deleted.
Ideally surrogate keys value would be system-generated.
You took Dr. Codd's from the same site, didn't you. Actually, it was not there to explain why surrogate keys shouldn't be used, quite the opposite...
So looks like some other authorities do not have any problem with using Surrogates, especially system-generated ones. That SQL Server IDENTITY is for!
You stance on the IDENTITY, shows that you are very knowledgeable theoretic, but you don't have much real life experience with using SQL Server
in enterprise level solutions.
Should I say that you know nothing about SQL Server, that you should take and read at least some book on it(not yours, of cause)? Microsoft SQL Server Book Online will do for starter...
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help