Here’s a nice brain teaser, just before the end of the year. Despite the title, it is related to SQL Server, not to music!
A common misconception amongst SQL Server users is that a clustered index on a table will ensure that data is returned in the order implied by that index. I have lost count of the number of times I had to disprove this notion.
Of course, there are many cases where the rows returned by a query will be in the order of the clustered index. Here’s a quick illustration, using the lineup that The Beatles had during most of the 60s.
CREATE TABLE Beatles
(LastName varchar(20) NOT NULL PRIMARY KEY CLUSTERED,
FirstName varchar(20) NOT NULL UNIQUE NONCLUSTERED);
INSERT INTO Beatles (LastName, FirstName)
SELECT 'Lennon', 'John'
UNION ALL
SELECT 'McCartney', 'Paul'
UNION ALL
SELECT 'Harrison', 'George'
UNION ALL
SELECT 'Starr', 'Ringo';
SELECT LastName FROM Beatles;
DROP TABLE Beatles;
go
LastName
--------------------
Harrison
Lennon
McCartney
Starr
The results of this query are in alphabetical order of last name, the column used in the clustered index. Apparently, this is one of the very many cases where the order of the rows is implied by the clustered index, allowing the misconception that this is always the case to spread even further.
But an interesting thing happens if I use the exact same table definition to old and query the lineup of that other famous rock group of the 60s:
CREATE TABLE Stones
(LastName varchar(20) NOT NULL PRIMARY KEY CLUSTERED,
FirstName varchar(20) NOT NULL UNIQUE NONCLUSTERED);
INSERT INTO Stones (LastName, FirstName)
SELECT 'Jagger', 'Mick'
UNION ALL
SELECT 'Jones', 'Brian'
UNION ALL
SELECT 'Richards', 'Keith'
UNION ALL
SELECT 'Watts', 'Charlie'
UNION ALL
SELECT 'Wyman', 'Bill';
SELECT LastName FROM Stones;
DROP TABLE Stones;
go
LastName
--------------------
Wyman
Jones
Watts
Richards
Jagger
In this case, the names are returned in random order. That makes this a great example to really disprove the notion of a clustered index guaranteeing any output order. (Dare I say that we now finally have solid proof that the Stones are better than the Beatles? Or will that make me subject to loads of flames?)
What’s intriguing in this case is the difference in behaviour for the two examples. Apart from the table name, the two code snippets are exactly the same – and even renaming the tables won’t change the results. So here’s the brain teaser that I’ll leave you to ponder over your glass of champagne: what is the reason that the Beatles are, but the Stones are not returned in clustered index order?
I’ll post the answer in a few days.
On a more personal note, I want to apologize for not posting any new stuff during the last two months. I still have some good ideas in my scratchpad, but I need some time to polish them up to blog quality – and time is the one thing I have been lacking for the past two months. The bad news is that I will probably be short on time for the next month as well, but things are looking more sunny after that.
And with this being my last post of the year, I’ll also grab this opportunity to wish all readers of sqlblog.com a very great 2007, with lots of love and luck in your personal lifes, and lots of interesting SQL challenges and enticing performance gains at work.