Physical forms of a table

I just had this question in my email.

"
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!

SELECT
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

If 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.

*) 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.

Updates

As from today you all can update your SQL Servers, at least 2008 SP3 and 2012 SP1.

Check these links out:
* SQL Server 2008 SP3 Cumulative Update 12
* SQL Server 2012 SP1 Cumulative Update 5

Enjoy!

A blog, to be continued...

For the last year I've been blogging on and off at my current employers blog and almost forgot about this blog. I have a lot of old blogposts from previous and old blogs I'm going to inject into this blog in a close future. Or at least having the ambition to...

Changes are about to happen, close to a month ago I decided to move on  as I got an offer from Claremont AB and to be their database guru from somewhere in the time frame of "late september/early october" this year. My future role will be database guru/trainer/mentor/speaker, and I'll continue on doing consultancy on Microsoft SQL Server.

So guys, see this post as a new start of this blog, this is where I'll continue to post stuff about SQL Server, databases, BI, and whatever that comes in mind.