SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key

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 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 (http://blog.SQLAuthority.com) , BOL

About these ads

46 thoughts on “SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key

  1. 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

  2. Pingback: SQL SERVER - 2005 - Find Tables With Foreign Key Constraint in Database Journey to SQL Authority with Pinal Dave

  3. Pingback: SQL SERVER - 2005 - Find Tables With Primary Key Constraint in Database Journey to SQL Authority with Pinal Dave

  4. 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?

  5. 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://www.SQLAuthority.com )

  6. 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

  7. (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.

  8. 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?

  9. 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.

  10. 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?

  11. 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.

  12. Dave, you are propagating a dangerous generalization with this blog post and subsequent comments. DBAs and database developers need to have an in-depth understanding of SQL Server in order to comprehend the implications of creating any type of index. Clustered indices CAN enhance performance, but they come with many trade-offs. For example:

    - a poorly-chosen clustered index key can provoke table scans where an effective one will enable seeks, especially in the case of range queries.
    - a poor clustered index will definitely result in a high rate of table and index fragmentation, as alluded to by Marco C above.
    - the clustered index key is implicitly included in all non-clustered indices, so choosing one that is too wide will slow performance and increase the size of all non-clustered indices on the same table.

    So saying things like “Clustered Index improves performance,” as you do in the comments, is a bad idea. I urge you to review your understanding of SQL Server data structures so that you do not promote potentially damaging ideas in your role as a Microsoft MVP.

  13. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  14. Is their any simplest way to find table dependancies….
    I want to find a particular table is dependant on which other table in database.

  15. @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.

  16. 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

  17. 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.

  18. 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

  19. 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

  20. 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.

  21. 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

  22. 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

  23. 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

  24. Pingback: SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters Journey to SQLAuthority

  25. Pingback: SQL SERVER – Identify Numbers of Non Clustered Index on Tables for Entire Database « SQL Server Journey with SQL Authority

  26. 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

  27. Pingback: SQL SERVER – Weekly Series – Memory Lane – #031 | Journey to SQL Authority with Pinal Dave

  28. Hi. Who knew that a table with NO clustered index can cause your db file to grow and grow until the server crashes?

    see: http://support.microsoft.com/kb/913399

    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.)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s