Find Tables That Are Missing a Clustered Index

Today I had the need to find tables in a database that did not have a clustered index defined. Why would I have such a need? That’s a great question.

Anyway, I set about with a few basic questions for myself such as “Self, how many tables are there?” So I did what I normally do whenever I have questions of MS SQL, I took a nose dive into the system information. First up would be to count the objects defined as a user table which I got with this (I was connected to AdventureWorks but feel free to run this against whatever database you are curious about):

SELECT COUNT(*)
FROM sys.objects
WHERE type = ‘U’

Running against AdventureWorks gives me a count of seventy (70) user defined tables. The next question I had for myself was “Self, how many clustered indexes exist for those seventy tables?” This required a little more work. I needed to keep swimming in the system information but now I had to swim in a very particular direction. That direction was towards the sys.indexes view where the index_id was equal to 1. Why a 1? Because that is how Microsoft decided to tag each and every clustered index.

So, the following query would give me a count of the number of tables with a clustered index defined, answering my second question:

SELECT COUNT(*)
FROM sys.indexes
WHERE object_id IN
(SELECT object_id FROM sys.objects WHERE type = ‘U’)
AND index_id = 1

Running this I promptly found that AdventureWorks has sixty-eight (68) tables that have a clustered index.Good news? Not really, no, I was hoping that every table had a clustered index. Why? Great question.

And to top it off, I have no idea which tables out of the 70 are missing a clustered index. All this talking to myself has distracted me from what I really need, which is a script that answers the real question: “Self, which tables are missing a clustered index?” I kept swimming in the system information, doing a loop around the same spot in the ocean I was already at, take the two answers above to arrive at:

SELECT name
FROM sys.objects
WHERE type = ‘U’
AND object_id NOT IN
(SELECT object_id FROM sys.indexes WHERE index_id = 1)

And a-bing, a-bang, a-bung there you go, the results are the following:

ProductProductPhoto
DatabaseLog

What’s that? You want to know why I would even care about this? Well, it turns out that this information is going to be important for anyone that is interested in SQL Azure. Why? That’s a great question. Let me introduce you to my new friend, Error Message 40054:

Msg 40054, Level 16, State 1, Line 3
Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

Yeah, that’s right, tables in SQL Azure *must* have a clustered index defined before you are allowed to insert any data. So, if you are looking to push your database to the cloud, you need to start thinking about your entire design, because there are a lot more factors that need to be considered other than just clustered indexes on each table.

And as luck would have it, I will be touching upon this topic (and others) as part of my talk next week at the Hartford SQL Server User Group meeting. We are looking to have the meeting broadcast live and I will post the event details once I have them.