Heaps are Lurking in your Database…
And you know that’s a bad thing™. At least, most of the time. Some people swear by ’em, and for certain requirements a heap is the best thing since sliced bread.
Heaps ‘o trash!
For most scenarios you don’t want OLTP data stored in heaps for all kinds of reasons. Anyway, if you suspect you might have a database that’s been questionably designed, and need a list of tables that have been built without a clustered index, you can use this quick and dirty ole query to identify ’em:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT ObjectName = QUOTENAME(s.name) + N'. ' + QUOTENAME(t.name) , IndexList = STUFF(( SELECT N', ' + i.name + N' (' + i.type_desc + N')' FROM sys.indexes i WHERE i.object_id = t.object_id FOR XML PATH(N'')) , 1, 2, N'') FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE NOT EXISTS ( SELECT 1 FROM sys.indexes i WHERE i.object_id = t.object_id AND i.type_desc = N'CLUSTERED' ) ORDER BY s.name, t.name; |
The query returns two columns; the name of the table, and a comma-separated list of indexes defined on the table, just in case there are some. You never know, you might have a table with a non-clustered primary key. And by all means, you might actually need a table with a non-clustered primary key. If you clearly understand the reasons you might want a primary key index that is not clustered, then be my guest.
The query above can be extended using a little dynamic T-SQL to show heaps in all the databases on an instance:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
DECLARE @cmd nvarchar(max); SET @cmd = N''; SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N' UNION ALL ' END + N' SELECT DatabaseName = ''' + d.name + N''' , ObjectName = QUOTENAME(s.name) + N''. '' + QUOTENAME(t.name) , IndexList = STUFF(( SELECT N'', '' + i.name + N'' ('' + i.type_desc + N'')'' FROM ' + QUOTENAME(d.name) + N'.sys.indexes i WHERE i.object_id = t.object_id FOR XML PATH(N'''')) , 1, 2, N'''') FROM ' + QUOTENAME(d.name) + N'.sys.tables t INNER JOIN ' + QUOTENAME(d.name) + N'.sys.schemas s ON t.schema_id = s.schema_id WHERE NOT EXISTS ( SELECT 1 FROM ' + QUOTENAME(d.name) + N'.sys.indexes i WHERE i.object_id = t.object_id AND i.type_desc = N''CLUSTERED'' ) ' FROM sys.databases d WHERE d.database_id > 4 AND d.state_desc = 'ONLINE' ORDER BY d.name; SET @cmd = @cmd + N' ORDER BY DatabaseName , ObjectName , IndexList;' PRINT @cmd; EXEC sys.sp_executesql @cmd; |
Let me know if you think Heaps shouldn’t be persecuted so much!
This post is part of our series on troubleshooting.