Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Identity And Primary Keys - Part I

Author  Topic  Jay99 468 Posts Posted - 2001-03-07 : 12:35:56 I have read this article several times and I don't get it . . . 1. They're not standard SQL . . . I am using SQL Server. I am developing only for SQL Server. SQL Server has it.2. They can't be updated. I have never needed up update an identity field. For the most part, it has never been a value actually used (or known) by the end user. I have never had SQL Server duplicate identities except once when I bulk copied out -> in and there is a flag to handle identity values. 3. ...hard to read Like I said the identity is not something the end user uses . . . and actually on the contrary, can't SQL Server compare numerics faster than characters? So it may be hard for me to read, but it is easier for SQL Server to read. 4. Not meaningful . . . I agree, they are not meaningful, but I have never had a problem writing the query to figure out what the LanName of user 113 is. I am also reading in 'SQL Server 7.0 DBO Survival Guide', "As a general rule, use an identity column as the primary key of a table . . .Guaranteed to be unique . . .avoids compound keys . . ." (page 569-70). Also, it seems to me that an Identity field would have high (the highest) selectivity, would virtually eliminate page splits, is a numeric allowing for fast comparison, and would be very usefull to a wide variety of queries. So what give? I have read many posts by robvolk. Infact, he (assumption) has responded to several of my posts on this board and has been very helpful. That is to say, I am not busting on robvolk. Instead, I am just asking for more dialog on this issue. I am currently trying to create a 'Employee' table. Last week, I would have had siEmployeeID SMALLINT IDENTITY(100,1) NOT NULL . . . and moved on. Now, I have spent the last several hours trying to figure out what else I can use. Employees can have more than one LAN identity. I guess I could use SSN, but I don't need that for any other part of this application. LastName/FirstName would work today, but maybe not in the future. Phone ext changes all the time . . . . I am at a loss. Jay