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:
- Category – Maintenance
- Category – Maintenance Plans
- Finding a better candidate for your SQL Server clustered indexes
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:
- Here is a tip on how to find tables without a clustered index using Policy Based Management
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
- Since you are concerned about tables in your environment without clustered indexes, I would recommend running the second query in this tip in a development or test environment to get a sense of the magnitude of the issue.
- Once you have a sense of the problem, you can run the first query to determine the tables per database without clustered indexes.
- I would recommend reading these tips to determine which columns to use for your clustered indexes:
Jeremy Kadlec is a Founder, Editor and Author at MSSQLTips.com with more than 300 contributions and 25+ years of SQL Server experience. Jeremy leads a team of more than 300 authors helping millions of SQL Server professionals around the globe every second of the day for the last 20 years. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP based on his community contributions. Jeremy brings 25+ years of SQL Server DBA and Developer knowledge to the community and holds a bachelor’s degree from SSU and master’s degree from UMBC.
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
In SQL Why Can’t We Use Two Separate Clustered Index In One Table?
Why SQL doesn’t Allow I want Reson Behind?
Aubrey,
Thank you for posting the script. I should get this tip updated.
Thank you,
Jeremy Kadlec
Community Co-Leader
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.