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
49 Comments. Leave new
CREATE TABLE [sim].[Simulations](
[Id] [int] IDENTITY(1,1) NOT NULL,
[SimulationDate] [datetime] NOT NULL,
[ScenarioId] [int] NULL,
[SimulationBudgetId] [int] NOT NULL,
[ScenarioStrategies] [int] NOT NULL,
CONSTRAINT [PK_Simulation] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
Table has clustered index and that’s why not given in output. I would correct the text in blog
CREATE DATABASE FOO
GO
USE FOO
GO
CREATE TABLE [Simulations](
[Id] [int] IDENTITY(1,1) NOT NULL,
[SimulationDate] [datetime] NOT NULL,
[ScenarioId] [int] NULL,
[SimulationBudgetId] [int] NOT NULL,
[ScenarioStrategies] [int] NOT NULL,
CONSTRAINT [PK_Simulation] PRIMARY KEY CLUSTERED
(
[Id] ASC
))
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
Thank you for the reply.
Your welcome :)