"
Dear dbguru,
How do I find which tables that are heaps and/or clustered?
Best regards,
XXX
"
Well, first of all the XXX person has probably been to TechEd 2013 in Madrid, and maybe seen the keynote day one when they did a great demo of GeoFlow. If you haven't seen it you have the link here, http://channel9.msdn.com/Events/TechEd/Europe/2013/Key01. The most juicy part is appr 1 hour into the keynote when Dandy Wein, @ilikesql, enters the scene and do a demo of GeoFlow. It's about ten to fifteen minutes... Anyway, I wore that jersey with pride all week!
Secondly, XXX, has a real name too. But I have decided to leave the person out of this.
Ok, let's get back on track.
In SQL Server the tables are either one of two basic physical forms; HEAP or CLUSTERED INDEX.
* A heap is a table without a clustered index, i.e. this table stores the rows in any order on data pages. This means that when you insert a row it ends up on any of the available data pages assigned to this table.
* A clustered index on the other hand has a clustered index key, which is one up to sixteen columns, with a width up to 900 bytes, that determines on which data page the inserted rows should end up on. This index clusters the rows on pages. This means that rows that is relating to each other can end up close to each other in the database.
Ok, how do I find out how my database is configured related to this?
Well, you could run the following query!
SELECTIf i.type equals 0 the table is a heap, 1 means a clustered index, 2 means a non clustered index and i.index_id is the sequential id of indexes on a table, 0 is heap, 1 is clustered index, 2 or larger *) is a non clustered index.
QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS TABLE_OBJECT,
CASE
WHEN i.type = 0 THEN 'HEAP'
WHEN i.type = 1 AND i.is_primary_key = 1 THEN 'CLUSTERED INDEX (' + i.name + ') AS PRIMARY KEY'
WHEN i.type = 1 AND i.is_primary_key = 0 THEN 'CLUSTERED INDEX (' + i.name + ')'
END AS TABLE_PHYSICAL_FORM
FROM sys.Schemas s
INNER JOIN sys.Tables t ON s.schema_id = t.schema_id
INNER JOIN sys.Indexes i ON t.object_id = i.object_id
WHERE i.index_id < 2
*) There are more types of indexes than I cover here. Just larger than 2 is not the whole truth, but it's Close enough in this blog post.
