Wednesday, September 23, 2015

SQL Server tables without a primarykey

T-SQL script to check if you have SQL Server tables without a primarykey. 

USE = your_db-name_here 
GO 

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME NOT IN
 (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY') 
AND TABLE_TYPE = 'BASE TABLE' and OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) IN (select object_id from sys.objects where type = 'U' and is_ms_shipped = 0) ORDER BY TABLE_NAME

Reminder: In SQL Server all tables should have a primary key, and all tables must have a clustered index.


-->
But the clustered index and the primary key are not mutual exclusive. They could differ and this depends on your requirements. As a general rule of thumb: clustered indexes perform very when handling ranges (so a date-field could be a good candidate, when a query selects data for a month or so) 

 also keep in mind that a clustered index is: 

- narrow 
- increasing 
- not modifiable 

Because a table only can have one clustered index, you should design with care and not assign an identity column as the 'clustered_index_primary_key' by default. I have seen this too many times. see my post on auto index management and let SQL Server assist you



Bookmark and Share