By definition, primary keys must contain unique data. They are implemented in the database through the use of a unique index. If there is not already a clustered index on the table, then the primary key’s index will be clustered. It’s not always true, but most of the time, you want your primary keys to be clustered because it is usually the key criteria in your requests to the data. This includes join conditions and where clause criteria. Clustered indexes give you exceptional performance because it allows SQL Server to create optimal execution plans.

How to detect this problem:

Select AllTables.Name
From   (
       Select Name, id 
       From   sysobjects 
       Where  xtype = 'U'
       ) As AllTables
       Left Join (
         Select parent_obj
         From   sysobjects 
         Where  xtype = 'PK'
         ) As PrimaryKeys
         On AllTables.id = PrimaryKeys.parent_obj
Where  PrimaryKeys.Parent_Obj Is NULL
Order By AllTables.Name

How to correct it: Identify tables without a primary key using the query above. Examine each table and identify what makes each row in the table unique. Modify the table to include a primary key.

Level of severity: Moderate

Level of difficulty: Easy