One of the basic Database Rule I have is that all the table must Clustered Index. Clustered Index speeds up performance of the query ran on that table. Clustered Index are usually Primary Key but not necessarily. I frequently run following query to verify that all the Jr. DBAs are creating all the tables with no Clustered Index.
USE AdventureWorks ----Replace AdventureWorks with your DBName
GO
SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE INDEX_ID = 0
AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
ORDER BY [TABLE]
GO
Result set for AdventureWorks:
TABLE
——————————————————-
DatabaseLog
ProductProductPhoto
(2 row(s) affected)
Related Post:
SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database
SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL
