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
Is their any simplest way to find table dependancies….
I want to find a particular table is dependant on which other table in database.
@Pankaj,
You can try one of these,
1. SP_depends [object_name]
2. SP_help [object_name]
3. From SQL Server Management Studio, in Object Explorer, right click Table Name – View dependencies.
~ IM.
hi
i have converted non clustered index to clustered index
by using the below query
create unique clustered index PK_Activity on Activity(Activity_id) WITH DROP_EXISTING
some times it is not working because the table have
foreign key
how to avoid the foreign key problem
@sourirajan
What do you mean by Not working.
What is not working. Please provide more details.
~ IM.
Hi Pinal,
I am a developer from pvt. firm in navi mumbai. I am developing an application which will handle entire email server transactions by an IT person. To develop this application using .net 2008 and Sql server 2005, i have 2 tables having records 2,500,000+ and 150,000+ respectively in its initial stage which will grow to 10 folds possibly in future. I want to have search queries on these tables to show updates through this application. WIth this table i have 3 more tables having records less than 35000 each which will be used with inner joins to display specific info on application.
Now , i want your help on how should i design queries or indexes which will fetch me desired results without giving connection timeout error. And also it should not take considerable time to load.My seniors have challenged me to fix this issue. I dont want to keep anything for them from my side. So please help me on this.
Please reply me on above email id by or before monday so i shold progress asap on this development.
Hi All,
I going through the implementation of indexes on following given table and will use following reterival queries to retreive the data. Can you please help me out which column(s) I should use for Clustered index & for Non Clustered and for Included Columns index for covered queries.. with example..
–Table Defination starts–
Create Table tblNews
(
SectionID int,
NewsID int,
Headline varchar(500),
Short_Story varchar(500),
PublishDate DateTime,
CONSTRAINT pk_NewsID Primary Key (NewsID)
)
–Table Defination ends–
–Queries—
select Headline, Short_Story from tblNews order by PublishDate desc
select Headline, Short_Story from tblNews where SectionID=1 order by PublishDate desc
select Headline, Short_Story from tblNews where NewsID between 1000 and 1500
select Headline, Short_Story from tblNews where Headline like ‘%USA%’order by PublishDate desc
—-
Please do suggest and it will be a great help.
Thanks
Gaurav Kukar
Hello Pinal,
I was given database. They added some tables. And I need to improve database optimization of table. What is the best way to start and what other things it requires?
Thanks,
Krunal
Hi Gaurav Kukar,
I think you need non-clustered index on the following fields:
PublishDate
SectionID
Headline
Please correct me Pinal if I am wrong.
Thanks.
hi
i want to find out non-cluster index applied to column in that table
how it is possible
can you guide me please
Regards
sharad
hi Pinal,
I am having records of 1 lakh and i have to retrive date on search .So i want improve its performance.Is it would be good idea going for clustered index?.
Thanks and regards,
lokesh
Hi Dave,
There is another way to achieve this:
select NAME
FROM sys.tables
WHERE OBJECTPROPERTY([OBJECT_ID],’TableHasClustIndex’) =0
ORDER BY name ASC
And, if you want to return the schema too, you can run :
SELECT T.name AS TABLE_NAME , S.name AS SCHEMA_NAME
FROM sys.tables T
INNER JOIN sys.schemas S
on S.schema_id = T.schema_id
WHERE OBJECTPROPERTY([OBJECT_ID],’TableHasClustIndex’) =0
ORDER BY S.name,T.name ASC
can we create non clustered index on table which do not have a clustered index??
please send mr response..
Yes it is possible. Did you try?
Please Can u reply how can i write a query to compare a text box value with all the rows and columns of a table in sql server 2005
very well explained………thanks a lot…
sir i have one n half year of experience in development…want to switch….what type of questions can they asked to me. Please reply
Hi. Who knew that a table with NO clustered index can cause your db file to grow and grow until the server crashes?
see:
If there is no clustered index, SQL Server can only reuse pages released by DELETE statements if you TRUNCATE, use TABLOCK hints on the delete query or drop and re-create the table.
For this reason alone, I always add a clustered index, usually an int, identity column so there is no performance issue with re-paging existing data during bulk insert operations. (i.e. the identity column always increments, so if this is the clustered index, the most recent insert is always logically and physically stored at the ‘end’ of the table and never needs to ‘re-page’ other rows out of the way to make space.)
Thanks Pinal. Your query helped.
Hi
When I run the query to find tables without clustered Index I get results of table that actually does have clustered Indexes??
Should I update statistics?
Can you post schema of table (Create Table Script)
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 :)