Primary Key vs. Identity? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Primary Key vs. Identity?

I’ve noticed that when creating fields in a table, I can click the "key" button and I can also change the Identity property to Yes, and that they seem to be unrelated! So these are not two different ways to create a primary key? What’s the difference between the two? Thanks!
If using an identity column is not a requirement, you can eliminate the overhead of managing the uniqueness of identity values in replicated data by using another column as the primary key, or using combinations of columns as the primary key. For example, you can define a primary key, consisting of an identity column whose values are not unique and a second column, that when combined with the identity column guarantees uniqueness (for example, a site ID column, pk_id_plus_site). In this example, the composite key pk_id_plus_site is a combination of the identity and site columns. In replication, duplicate identity values can be created only at different sites; therefore, each primary key value in this case will always be unique. ROWGUIDCOL is a property you can assign to a column with uniqueidentifier values, a SQL Server 2000 data type that defines a 128-bit integer guaranteed to be unique. As such, using a rowguid column as a primary key is a safe alternative to using an identity column to guarantee uniqueness. HTH Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
A PRIMARY KEY is one of the fundamental concepts of relational databases. It is one or more columns that uniquely identify a row in a table.
The IDENTITY property can be defined for numerical columns with scale 0 and is an easy and convenient way to provide a sequential number.
Both are not related. Your PRIMARY KEY does not need to be an IDENTITY column. —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

Very interesting… thank you. So, if I understand correctly, I always need to click that key button on the toolbar if I want to make a column the primary key. By contrast, it seems I will rarely be making a column an Identity. Does that sound right? Just one more point for clarification: Can the identity column be used to make a column or combination of columns a unique identifier, even though it is not the primary key? As you know, not all unique identifiers are primary keys, and it is sometimes necessary to enforce the uniqueness of another column or combination of columns. Up until now, I have been creating a unique constraint in the indexes dialog when I’ve needed to do that. Can the Identity be used for that, instead? So, to summarize, my questions are: 1. I always need to click the key button to make a primary key, correct?
2. Can the Identity be used to make unique identifiers that are not primary keys? Thank you.
1)Yes, either you can use EM gui or TSQL to assign PRIMARY Keys.
2)http://www.sqlteam.com/item.asp?ItemID=283 well said here. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>