SQL Server Tables without a Clustered Index

By:   |   Updated: 2011-10-04   |   Comments (9)   |   Related: 1 | 2 | 3 | > Indexing


Problem

I know it is a general best practice to have a clustered index on all of your SQL Server tables.  Unfortunately, everyone that works with SQL Server in our company does not know that.  I think I have uncovered a pattern where a number of tables do not have a clustered index.  Can you provide a script or two to find out which tables do not have a clustered index?  Check out this tip to learn more.

Solution

In general it does make sense to have a clustered index on your tables. I would not say this is an absolute rule 100% of the time.  However, if a table does not have a clustered index, it should be a conscious, rational decision.  To address your problem, I believe we need to address the following questions:

  • What is a SQL Server Clustered Index?
  • Why do I need a SQL Server Clustered Index?
  • How can I find tables in my database without a SQL Server clustered index?
  • How can I find out how many tables across all of my SQL Server databases that do have a SQL Server clustered index?

What is a SQL Server Clustered Index?

At the most basic level, a clustered index provides order to a table because the data is stored based on the clustered index columns.  Keep in mind that there is 1 clustered index per table, so there is only 1 order for the data.  Generally speaking, data can be consistently accessed based on the clustered index as opposed to what is called a heap.  A table without a clustered index is called a heap.  With a heap, the data is not ordered by an index, so data is not stored in any particular order.

A clustered index is generally a single column for most tables, but clustered indexes can be multiple columns.  Often times, clustered indexes are created when the primary key for the table is created in tools like SQL Server Management Studio's Database Designer.  However, this is not a requirement; the clustered index and Primary Key are not always best for suited for the same column(s).  How the data is searched can be very different from how each row is uniquely identified. 

Speaking of order, a clustered index can be created in ascending or descending order.  Most clustered indexes are created in ascending order, because that is logically how the data is viewed by users in the application.

For more information about clustered indexes, check out the following tips:

Why do I need a SQL Server Clustered Index?

The most important reason to have a clustered index is to improve data access.  Generally speaking the proper clustered index that is properly maintained will improve overall application performance.  In general clustered indexes on columns with help these types of operations in queries are improved:

  • WHERE clauses with BETWEEN, greater than, less than, etc. logic
  • Column(s) used to JOIN tables
  • Columns used in ORDER BY or GROUP BY clauses

Keep in mind you can only have 1 clustered index per table, so you need to chose it wisely to benefit the most queries.  As I mentioned earlier, it is generally a good idea to have a clustered index, but the definition for the clustered index can change.  As such, if how the data is queried changes significantly be sure to verify your clustered index still is appropriate.  If not, change your clustered index to match your new data access patterns.

For more information about clustered indexes, check out the following tips:

How can I find tables in my database without a SQL Server clustered index?

In SQL Server 2005 and beyond, we can query the sys.indexes and sys.objects catalog views to determine which tables in our database are a 'heap', meaning they do not have a clustered index.  Let's explain the query below:

  • Line 1 - Retrieve the top 1000 records from the database to include only the following columns:
    • name from sys.objects which is the table name
    • type_desc from sys.indexes which indicates the table is a HEAP
    • o.type_desc from sys.indexes which indicates the table is a USER_TABLE
    • o.create_date from sys.indexes which indicates when the table was created
  • Line 2 - Retrieve data from the sys.indexes catalog view and alias the view as 'i' in the query
  • Line 3 - INNER JOIN the data from the sys.objects catalog view and alias the view as 'o' in the query
  • Line 4 - Join the data between the sys.indexes and sys.objects catalog views based on the object_id column from both of these catalog views
  • Line 5 - WHERE clause to filter the data based on the value from the type_desc column of the sys.objects catalog view being equal to 'USER_TABLE'
  • Line 6 - Second WHERE clause to filter the data based on the value from the type_desc column of the sys.indexes catalog view being equal to 'HEAP'
  • Line 7 - Order the results by the o.name column
  • Line 8 - Execute the batch with the GO command 
SELECT TOP 1000 o.name, i.type_desc, o.type_desc, o.create_date
FROM sys.indexes i
INNER JOIN sys.objects o
 ON  i.object_id = o.object_id
WHERE o.type_desc = 'USER_TABLE'
AND i.type_desc = 'HEAP'
ORDER BY o.name
GO

Additional tips:

How can I find out how many tables across all of my databases that do have a SQL Server clustered index?

To build on the code above for a single database, let's use the sp_MSforeachdb system stored procedure to run a variation of the query above.  The code below will determine how many (i.e. count) tables across all of my databases on a single SQL Server instance do not have a clustered index.

Here is an explanation of the code:

  • Line 1 - Execute the sp_MSForEachDB system stored procedure
    • The '?' i.e. question mark in this code is a placeholder for the database name in the sp_MSForEachDB system stored procedure
    • The single quote after the sp_MSForEachDB system stored procedure starts the code being executed against all of the databases
  • Line 2 - Retrieve the database name
    • The '?' i.e. question mark in this code is a placeholder for the database name in the sp_MSForEachDB system stored procedure
  • Line 3 - Retrieve the count of user defined tables that are heaps
  • Line 4 - Retrieve data from the sys.indexes catalog view and alias the view as 'i' in the query
  • Line 5 - INNER JOIN the data from the sys.objects catalog view and alias the view as 'o' in the query
  • Line 6 - Join the data between the sys.indexes and sys.objects catalog views based on the object_id column from both of these catalog views
  • Line 7 - WHERE clause to filter the data based on the value from the type_desc column of the sys.objects catalog view being equal to 'USER_TABLE'
    • Notice in this example we use double quotes around the WHERE clause parameters
  • Line 8 - Second WHERE clause to filter the data based on the value from the type_desc column of the sys.indexes catalog view being equal to 'HEAP'
    • The final single quote matches the single quote from the first line to end the code executed against all of the SQL Server databases on the instance
EXEC sp_MSforeachdb 'USE [?] 
SELECT   ''?'' AS DatabaseName,
COUNT(o.name) AS Count 
FROM sys.indexes i
INNER JOIN sys.objects o
 ON  i.object_id = o.object_id
WHERE o.type_desc = ''USER_TABLE''
AND i.type_desc = ''HEAP'''

Additional tips:

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-10-04

Comments For This Article




Tuesday, March 15, 2022 - 10:19:46 AM - Greg Robidoux Back To Top (89877)
Hi Shabill,

The clustered index is like the contents of a book. When you have a book all of the content is laid out in one order and only one order.

At the back of the book you have an index and also a table of contents in the front which allows you to find things quickly, this is the same as non-clustered indexes.

So, in SQL Server you can only have one clustered index, but you can have several non-clustered indexes.

Hopefully this helps.

-Greg

Tuesday, March 15, 2022 - 5:53:28 AM - Shabill Irfani Back To Top (89875)
In SQL Why Can't We Use Two Separate Clustered Index In One Table?
Why SQL doesn't Allow I want Reson Behind?

Monday, January 24, 2022 - 12:33:54 PM - Jeremy Kadlec Back To Top (89688)
Aubrey,

Thank you for posting the script. I should get this tip updated.

Thank you,
Jeremy Kadlec
Community Co-Leader

Friday, January 14, 2022 - 10:46:01 AM - Aubrey Back To Top (89663)
Jeremy,

Great article as usual. As Phill mentioned below, sometimes we need to span all databases in an instance to identify the HEAP tables. This script below, combines your two scripts from the article to return the best of both worlds. Here we return the database name, table name, HEAP status, table type and creation date for a more detailed analysis on all databases in a particular instance.
USE master;
GO
EXEC sp_MSforeachdb 'USE [?]
SELECT ''?'' AS database_name,
o.name AS ''table_name'',
i.type_desc,
o.type_desc AS ''table_type'',
o.create_date
FROM sys.indexes i
INNER JOIN sys.objects o
ON i.object_id = o.object_id
WHERE o.type_desc = ''USER_TABLE''
AND i.type_desc = ''HEAP''
ORDER BY o.name';
GO

I know this article is about 10 years old and the last comment was about 5 years ago, but maybe someone could still use this “combo” script.

Friday, October 6, 2017 - 5:40:36 PM - Rick Fraser Back To Top (66987)

I included schema name

SELECT
 s.name + '.' + o.name 'TableName'
,i.type_desc
,o.type_desc
,o.create_date
FROM sys.indexes i
JOIN sys.objects o
  ON  i.object_id = o.object_id
JOIN sys.schemas s
  ON  s.schema_id = o.schema_id
WHERE o.type_desc = 'USER_TABLE'
  AND i.type_desc = 'HEAP'
ORDER BY o.name

 

 

Thursday, March 19, 2015 - 4:58:09 PM - Jeremy Kadlec Back To Top (36596)

Phil,

Thanks so much.  That makes my day.

Thank you,
Jeremy Kadlec
Community Co-Leader

Thursday, March 19, 2015 - 10:11:24 AM - Phil Gardocki Back To Top (36587)

Thanks for your write up.  At first I had a WTF moment in what I was looking at, but eventually I parsed my way through it all and it really has been one of the best tips on evaluating my databases with regard to which tables are clustered and not.  This is specially true as in my situation I inherited a larger number of versions of the database,  as our customers are in various update stages.  So the script that spans all the databases was wonderful. 


Tuesday, October 4, 2011 - 9:20:28 AM - Jeremy Kadlec Back To Top (14772)

Armando,

Thank you for the post.

That is the one good and bad part about SQL Server; you can accomplish the same task a few different ways.

Thank you,
Jeremy Kadlec

Tuesday, October 4, 2011 - 9:08:40 AM - Armando Prato Back To Top (14770)

Nice, Jeremy!

I use the following to check for no clustered index on tables (an alternate approach)

 

select *
from information_schema.tables
where objectproperty(object_id(table_name), 'TableHasClustIndex') = 0














get free sql tips
agree to terms