Always seems to spark a very heated debate…So I’d like to make a list of Pros and Cons that I’d like to keep Adding to. All comments appreciated. So I’ll start with the CON’s, mostly because I believe there are a few reasons for a surrogate key, but it is usually WAY overused and also gets dbas/developers in more trouble .

EDIT:  Ok a couple of response so far.  I think I'd like to start a Poll (If that's possible).  I'll add another pro for the devolpers in item #4.

EDIT: It's been suggested that surrogates are ANSI.  I thought they were extensions.  Anyone?

EDIT:  OK, it seems everyone is “on the fence“, but the original question is preferance, not wether you wouldn't use them.

POLL:

Prefer to avoid surrogates:     2

On the Fence: 6

Prefer to only use surrogates: 4

CON

1. It’s not ANSI

2. It’s not defined anywhere in the rules for a relational model or Codd’s Rules

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33785

http://en.wikipedia.org/wiki/Ted_Codd

3. Even SQL Server itself gets in trouble because of it. Had a bunch of fun with SELECT * in a view. When the table is altered problems arise in the catalog. See item 8:  

http://weblogs.sqlteam.com/brettk/archive/2004/01/30/825.aspx

4. Excessive Need to Join to obtain values (None more glaring then one guy asking to set up identities for a zip code table.)

 5. Outside of IDENTITY, all the problems of getting the next ID for an int (The locking alone…ok, there’s always the next id table solution…but why bother)

6. You need to add constraints of attributes that should not be duplicated anyway for data integrity.

7.  People think that you can “clean up“ a table by reseting the seed...a conceptually bad idea in the relational model...AND it actualyy can be done...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30084

8.  Peopel do stuff like

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myPhone99  (PhoneID  int IDENTITY(1,1), PhoneNumber  varchar(10))
CREATE TABLE myLastName99 (LastNameID  int IDENTITY(1,1), LastName  varchar(50))
CREATE TABLE myFirstName99 (FirstNameID  int IDENTITY(1,1), FirstName varchar(10))
CREATE TABLE myGender99  (GenderID  int IDENTITY(1,1), Gender  varchar(10))

CREATE TABLE myClient99(
   ClientID int IDENTITY(1,1)
 , PhoneID int
 , LastNameID int
 , FirstNameID int
 , GenderId int)
GO

INSERT INTO myGender99(Gender) SELECT 'Male' UNION ALL SELECT 'Female'
INSERT INTO myFirstName99(FirstName) SELECT 'Brett' UNION ALL SELECT 'Boy5' UNION ALL SELECT 'Tara'
INSERT INTO myLastName99(LastName) SELECT 'Kaiser' UNION ALL SELECT 'CSE' UNION ALL SELECT 'Duggan'
INSERT INTO myPhone99(PhoneNumber) SELECT '9735551212' UNION ALL SELECT '800551212'
GO

INSERT INTO myClient99(PhoneID, LastNameID, FirstNameID, GenderID)
SELECT 1,1,1,1 UNION ALL
SELECT 2,2,2,1 UNION ALL
SELECT 3,3,3,2 UNION ALL
SELECT 4,3,3,2 UNION ALL
SELECT 3,4,3,2 UNION ALL
SELECT 3,4,4,2 UNION ALL
SELECT 3,3,4,4
GO

-- Very useful, huh
SELECT * FROM myClient99
GO

-- Fun with surrogates...

   SELECT c.ClientID, p.PhoneNumber, l.LastName, f.FirstName, g.Gender
     FROM myClient99 c
LEFT JOIN myPhone99  p ON c.PhoneID     = p.PhoneID
LEFT JOIN myLastName99  l ON c.LastNameID  = l.LastNameID
LEFT JOIN myFirstName99 f ON c.FirstNameID = f.FirstNameID
LEFT JOIN myGender99 g ON c.GenderID    = g.GenderID
GO

SET NOCOUNT OFF
DROP TABLE myPhone99, myLastName99, myFirstName99, myGender99, myClient99
GO

PRO

1. Addresses. What else would be a good key? I haven’t seen one.

2. In a response it was suggested that for navigating many levels of parent child relationships it would be a good idea to use surrogate keys. The suggested number of columns was 5. I don’t consider that too unwieldy, my number would be much higher. But how deep have people seen these relationships…4, 5 or 6? I did remember long ago a logical model that a group tried to implement. Went to 15 levels. But only drives home my IDENTITY Point. A code for a code? Anyway I was honored by this reference. Please visit his site.  

http://www.xml-blog.com/archives/000234.html

3. I guess I have to put that a paper thin Index would also be desirable.

4.  Another response from developers indicating their preference.  So a PRO I guess is the overwhelming fact the people prefer this method.  I have yet to be convinced however...and this is what these blogging are all about anyway.

Please take a peek at his bolg

http://odetocode.com/Blogs/scott/archive/2004/06/09/275.aspx?Pending=true

4 1/2. Robert adds that the surogate changes so that's a plus. To me that's a debatable point...when was the last time a State changed it's name.  And even if it did, isn't that a new state?  So I'll Add it, but I have to make it item 4 1/2.