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.

Conferences to look for Autumn 2012

There are some nice SQL conferences upcoming:
SQLTuneIn 2012 in Zagreb, Croatia, 24-25 of September
http://www.sqltunein2012.com/

PASS SQLRally Nordic 2012 in Copenhagen, Denmark, 1-3 of October
http://www.sqlpass.org/sqlrally/2012/nordic/

And then it's:
Microsoft TechEd EMEA in Amsterdam, Netherlands, 26-29 of June
http://europe.msteched.com/

MCT Summit Europe in Warsaw, Poland, 6-8 of September
http://www.mctsummit.eu/

SEF 2012 in Stockholm, Sweden, 22-23 of October
http://www.seforum.se

And don't miss
Microsoft Sommarkollo 2012, Sweden, Summer of 2012
http://www.microsoft.com/sv-se/sommarkollo/default.aspx

I'll probably be on most of these as a speaker or other official role, if you're plan to visit one or more of these conferences. Check out this blog to see if I'm there aswell.

SQL Server 2012 goes RTM

A few weeks ago the last golden bits of SQL Server 2012 were finally signed ready. Yesterday, or today, depending on where on earth you are the launch of the newest, finest version of SQL Server ever.

Download from here: Try SQL Server 2012 RTM
Experience the SQL Server Virtual Launch here: Party

Back from my first ever MVP Summit

I'd like to direct a huge thank you to the SQL Server Product Group, my MVP Lead Willian Jansen, the MVP Awards team, and the community for giving me the opportunity of being a MVP on SQL Server: Architecture. The summit rocked, and I will be back if you want me too.

SQL Server 2012, new functions: DATE & TIME FROMPARTS

We have a series of new function in SQL Server 2012. Within the Date and Time area we got both the EOMONTH and a whole bunch of date and/or time from parts functions.

SMALLDATETIMEFROMPARTS( year, month, day, hour, minute )
DATETIMEFROMPARTS( year, month, day, hour, minute, seconds, milliseconds )
DATETIME2FROMPARTS( year, month, day, hour, minute, seconds, fractions, precision )
DATETIMEOFFSETFROMPARTS( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
DATEFROMPARTS( year, month, day )
TIMEFROMPARTS( hour, minute, seconds, fractions, precision )

When is this needed?

If you store date and time with integers, one column per part, take a look at the example below.

DECLARE @table TABLE (
iYear int,
iMonth int,
iDay int,
iHour int,
iMinute int,
iSecond int,
iMilliseconds int,
iFractions int );
DECLARE @timestamp datetime2(7);
SELECT @timestamp = sysdatetime();
INSERT INTO @table(iYear, iMonth, iDay, iHour, iMinute, iSecond, iMilliseconds, iFractions)
SELECT
DATEPART(yyyy,@timestamp), -- Years
DATEPART(mm,@timestamp), -- Months
DATEPART(dd,@timestamp), -- Days
DATEPART(hh,@timestamp), -- Hours
DATEPART(mi,@timestamp), -- Minutes
DATEPART(ss,@timestamp), -- Seconds
DATEPART(ms,@timestamp), -- Milliseconds
DATEPART(ns,@timestamp)/100;
-- The fraction, amount of nanoseconds divided by 100
-- Test the functions
SELECT SMALLDATETIMEFROMPARTS(iYear, iMonth, iDay, iHour, iMinute) FROM @table -- SMALLDATETIME
SELECT DATETIMEFROMPARTS(iYear, iMonth, iDay, iHour, iMinute, iSecond, iMilliseconds) FROM @table -- DATETIME
SELECT DATETIME2FROMPARTS(iYear, iMonth, iDay, iHour, iMinute, iSecond, iFractions, 7) FROM @table -- DATETIME2
SELECT DATETIMEOFFSETFROMPARTS(iYear, iMonth, iDay, iHour, iMinute, iSecond, iFractions, 1, 0, 7) FROM @table -- DATETIMEOFFSET
SELECT DATEFROMPARTS(iYear, iMonth, iDay) FROM @table -- DATE
SELECT TIMEFROMPARTS(iHour, iMinute, iSecond, iFractions, 7) FROM @table -- TIME