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. It returns each table without clustered index, so I can fix it.
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
What to Do When You Find a Table Without Clustered Index
The query works because every table without a clustered index, called a heap, has a row in sys.indexes with index_id 0. A table with a clustered index has index_id 1 instead, which makes the check simple and fast.
Note that no clustered index is not the same as no primary key. A table can have a primary key created as NONCLUSTERED and still be a heap. If you care about both, check both.
Heaps are not always wrong. Staging tables that are loaded, read once and emptied can work fine as heaps. The trouble starts with heaps that get many updates. When a row grows and no longer fits on its page, SQL Server moves it and leaves a forwarding pointer behind, and scans then need extra reads. You can see this in the forwarded_record_count column of sys.dm_db_index_physical_stats when you run it in DETAILED mode.
Before you add a clustered index, pick the key with care. A narrow, unique column that only grows, like an INT IDENTITY, is a good default. Also plan the timing: building a clustered index on a big table rewrites the whole table and can block users while it runs, so do it in a quiet window.
I also like to add the row count for each heap from sys.dm_db_partition_stats. Then I fix the big, busy tables first and leave small lookup tables for later, since they rarely cause trouble.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
49 Comments. Leave new
I have a table with Huge number of records. Is it advisable to have Clustered Index On it?
hi,
i’ve another question, i want to use this query in sql 2000, its not working with the above mentioned query kindly advise the update, will appreciate your quick response
Kalpesh,
Good Question, I will write query tonight and post it tomorrow. Please check the blog tomorrow.
Regards,
Pinal Dave ( http://blog.sqlauthority.com/ )
hello sir ,
can i use this query for finding non index tables
select y.name,x.name,y.id from sysindexes x join sysobjects y on x.id=y.id
where y.type=’u’ and x.indid=0
Indid = 0 means table without clustered index. So, if you have table with only non-clustered index – you would still have row with indid =0
what is query for finding primary key from the table ?
i have only table.
Srinivasan Prasanna,
I recompute statistics every night when we have very low usage of database. It is not recommended to compute based on some specific amount of rows are inserted because, it may be possible Server is very busy and will have degraded performance or may have lots of deadlocks.
Regards,
Pinal Dave ( http://blog.sqlauthority.com/ )
At what point do you recommend to recompute statistics on the indexes in the table? Is it advisable to have a job which will recompute the statistics on the table after xxx number of records have got inserted?
Clustered Index improves performance.
Dave,
Why the insistence on a clustered index as opposed to a non-clustered index? My understanding is that a clustered index actually orders the physical table based on the index field while non-clustered indexes do not. Would there be a cost associated with this as well( ie. slower updates, inserts )?
Thanks
(correct me if I’m wrong, all)
Brad, yes there is a cost, but frequently the cost during the insert is paid back by all the subsequent . Say you have a clustered index by date. Say you need to add some records from yesterday. Depending on how many, as well as the standard % of space in the index, in order to put the data in the index you might have to shift pages down, which would impact performance. But, except in high-transaction environments, this is usually not an issue – just make sure to leave room in the index, and don’t do something like use a random number (or GUID) as the key.
Michael, why do you say : “don’t do something like use a random number (or GUID) as the key” ? Why would that be a problem ?
Thx
Dave,
I have the same question as – 1 Sham
I have a table with Huge number of records. Is it advisable to have Clustered Index On it?
Hi, I disagree with your statement that all tables must have a clustered index, I have practical prove that it only enhances performance on tables that have very low insert, deletes and updates on the column that has the clustered index, and if you have lots of delete and updates on these tables the performance actually decreses.
If you really understand the concept of clustered indexes you find the answer. Clustered indexes order the phisical data table on the index key order, that means, instead of having another table with only the index key and the pointer, sql server has to order the whole data table based on the key order, and that translates to lots of I/O and lots of locks on the table, and that of course for tables that have frequent updates and deletes.
But if you have very low updates and deletes on this table, clustered indexes really increase performance.
what is primary key clustered??? and unique non clustered???
Is there is any way to fetch the Unique index key columns if we knows the database name ?
I want 2 know which type of index is applied on table .
there is no key applied on that table …
pls helpme
Guyes,
I want to know the F4 (search for objects) procedures in 2005. How to do that?
Thaks
how many unique key have in a table?
hi,
what is the diff between cluster/non cluster index?
Hi,
Could you please tell me the scenario in which i should opt for custered index or non-clustered index. What is the trade off between two?
first of all we have to look into our requirements. if we are working with database with lots of read (reporting server or olap db) then having a cluster index is a must. but if we are having lots of writes eg. table is working as temp table to store data then non cluster indexes can give better performance. but for important table , having cluster index is very important.
I want to find out tables in my database who are using indexes. So that i could create similar indexes on the copy of the databse.