XML FeedsBy 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
I agree with this....can't remember how many times I had to clean up tables and even worse with child tables (but no pk --> fk) running group by having count(*) > 1
Are there any exceptions to the rule? Say, when would you want to have a heap table?
Naomi,
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.