Select Primary Key carefully in SQL Server Database

Basically Primary Key is combination of field(s) which can help to uniquely identify the row in one table. This concept is not limited to only Microsoft SQL Server but it is used in any RDBMS.

In short, you should have combination of fields or at least one field in your table which can uniquely identify the row in table, suppose in university database student identification number is unique. Primary Key ensures that there is no duplicate data available in database.  Primary Key is not only uniquely identifying the rows; it is also used to establish relations between tables which is a real heart of any RDBMS including SQL Server.

Primary Key will not accept NULL value (NULL is unknown value or missing value which is really different than blank space or 0)

Before making a field as a Primary Key, think whether the field is really unique for all rows, if answer is no, then you should make combination of fields as a primary key which is known as Composite Primary Key.

If I want to give you example then I would give example of POOR Primary Key which is first name or last name etc. because these are the fields which can be shared by many person and GOOD example of Primary Key is SSN (Social Security Number) which is unique for every individual.

If you don’t find any combination which makes row very unique than others, in that case there is a facility in SQL Server called IDENTITY field, You can use it. IDENTITY field was popular at auto increment id in past.

BTW, I have observed that even a seasoned developer also making IDENTITY filed in each and every table without even asking question to their self that why they are creating it? Is there any need? It is not a good idea to have IDENTITY column in every table even though you have other fields which can be used to identify unique row.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah