THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

The Beatles versus the Stones

This blog has moved! You can find this content at the following new location:

Published Sunday, December 31, 2006 1:49 PM by Hugo Kornelis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Anonymous said:

Nice one, Hugo -- that is a great example!  Took me a moment to figure out, but it makes perfect sense.  Have a great New Years!

December 31, 2006 10:31 AM

Kalen Delaney said:

This is great! I'll admit, I didn't figure it out by just looking at the code. I had to run it, and start doing some of my usual query analysis, but I got it pretty quickly after that. I'd love to use this as an example in my classes, if that's ok with you? It makes me wish I was still a college teacher, and I could actually use this as a pop quiz. ;-)

December 31, 2006 11:18 AM

Denis The SQl Menace said:

Oh yes, I remember this thread "insert into tbl1 select * from tbl2order by field1 doesnt work!"

from the microsoft.public.sqlserver.programming newsgroup(

There were 121 messages in total going back and forth until Hugo set the op straight




- Edited by admin to remove long link -

December 31, 2006 12:02 PM

AaronBertrand said:

The answer isn't too obvious, unless you've been dealing with this misconception a lot.  :-)  It's a very good, succinct example that I'm sure will be referenced in many such discussions in the future.

December 31, 2006 3:49 PM

Dave Markle said:

Awesome.  3 minutes!  Let me pat myself on the back... There.  Done.  I feel a lot better.  

Honestly, I haven't been fighting this misconception much.  I find myself fighting far too many "wtfs" -- subqueries like "(SELECT TOP 100 PERCENT FROM foo ORDER BY bar)".  I am a bit shocked every time I see that one.  And the fact that it usually seems to "work".  And the fact that the SQL 2000 EM query designer generates these sorts of hideous abominations.

January 1, 2007 8:56 AM

Hugo Kornelis said:

Kalen: Of course you can use this in your classes. The pop quiz idea is closer than you may think. I had intended to demonstrate these queries at the end of a session I delivered last month and give a prize to the first to correctly explain the behaviour, but I didn't have enough time left :-(  That's when I decided to post it here instead.

Dave: Allow me to add my back-pattings as well. Figuring this out in just 3 minutes is really good! re "TOP 100 PERCENT ... ORDER BY": you'll probably stop complaining about EM's view designer adding this once you find out that SSMS' view designer does the same - even though the "trick" doesn't even work anymore on SQL Server 2005!

January 1, 2007 4:56 PM

Dave Markle said:

Wow.  Thanks Hugo!  A truly disturbing revelation indeed.  Since it's so totally off-topic, I did some more investigation of it on my blog (  If you play with the numbers a bit in the TOP clause, it really makes you wonder what the "proper" behavior should be!

January 1, 2007 8:07 PM

Hugo Kornelis said:

Hi Dave,

The "proper" behaviour is to use the ORDER BY only to determine which rows are included in the TOP xxx subcollection. A view can't be ordered, by definition, so SQL Server is free to return rows in any order it sees fit.

SSMS should not even allow you to attempt to define an order for a view. I have submitted a bug for this to Connect:

January 2, 2007 8:05 AM

Denis The SQL Menace said:

Yes it does make sense but I think (some) people will say

No problem I will just add

where lastname > '';

and that fixes that 'problem'

January 2, 2007 10:53 AM

Ahmed Charles said:

Well, I thought I'd try to understand why the optimizer does what it does and came up with the following:


    (LastName varchar(20) NOT NULL PRIMARY KEY CLUSTERED,

     FirstName varchar(20),

     LastNameAgain AS CAST(LastName AS char(1597)));

CREATE UNIQUE INDEX IX_FirstName ON Stones(FirstName) INCLUDE (LastNameAgain);

In SQL Server 2005 (9.00.3033.00), the adding the char(1597) column to the nonclustered index, but not the clustered index changes the behavior. A char(1596) column doesn't. I guess this approach is another way to "solve the problem" without requiring that you change all your queries. :)

January 3, 2007 2:10 PM

Hugo Kornelis said:

Denis, Ahmed,

For the record - there is no "problem", so nothing needs to be fixed. The query has no ORDER BY, so any ordering in the returned data is correct. My post is just about the strange observation that the Beatles "happen to be" returned in clustered index order, while the Stones are not.

I'll get back to the reasons why Denis' where clause and Ahmed's (very impressive!! - kudos for finding that) computed column change the behaviour of the Stones at a later time. I can't get into that right now without giving away the answer, and I'll postpone that a bit, so that new readers can scratch their heads a bit (and others can continue to tear out what's left of their hair <evil grin>).

January 3, 2007 5:37 PM

Ahmed Charles said:

I agree, there is no "problem" (unfortunately sarcasm is fairly hard to express in text). I also agree that people should scratch there heads for awhile, though I'm sure it'll make sense in the end.

January 3, 2007 6:52 PM

Chuck Boyce said:

Hugo, my friend,

There's simply no comparison between the Beatles and the Stones.  I love the Stones, btw..."Jumping Jack Flash", "Happy", "Miss You".  The Stones rock, man.

But the Beatles???  Are you kidding me???????

"Dear Prudence"


"Baby, You're a Rich man"

That awesome Rickenbacker John Lennon played?

The Stones are at the top of the 20th century and certainly one of the best bands of the 60's and 70's, but the Beatles are the most significant Pop group in a millennia.

January 4, 2007 5:22 PM

Hugo Kornelis said:

Hi Chuck,

Heheh! I expected comments llike yours - in fact, I am surprised I didn't get a big flame war about which band was better within hours of posting <g>.

I won't get into this debate. In fact, I even carefully avoided any indication that either band might be better or worse, or even that either behaviour of any of the tables might be better or worse!

January 4, 2007 6:21 PM

ShimMaster said:

Results are being returned in order by the non clustered index.

Remove the non clustered index and the results are returned in the correct order.

Optimizer chooses to do an index scan when clustered index only exists and via non clustered  index when both indexes exist?

Very interesting.

January 16, 2007 10:01 AM

Hugo Kornelis said:

Hi ShimMaster,

Congratulations! You figured out the correct answer.

But I must object to the usage of the words "correct order" when refering to a query that has no ORDER BY. All orders are correct - they are only different (or rather, they <b>appear</b> to be different).

I tried to publish a new blog post with a detailed explanations and some follow-up to the observations made by Denis and Ahmed, but experienced some technical difficulties. I'll try again later today.

January 16, 2007 2:21 PM

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' said:

I have just managed to overcome the technical difficulties. The full explanation of this seemingly weird behaviour is now online.

January 16, 2007 3:47 PM

Denis Gobo said:

A year in review, The 21 + 1 best blog posts on SQLBlog Best posts according to me, it might have been

December 27, 2007 4:11 PM

Pollus said:

Very nice article ! Short and using a nice title !

December 28, 2007 1:12 PM

Sanchay said:

Got it 1 minute, but don't know the reason for behavior, why its considering FirstName at all?

March 21, 2013 3:27 AM

Maniac said:

This fixes the problem:

SELECT LastName FROM Stones WITH (Index(1));

November 21, 2014 7:55 AM

How about #temp table? said:

if I already have Stones table and I copy data I need into temp table, will the data in temp table always sorted


    (LastName varchar(20) NOT NULL PRIMARY KEY CLUSTERED,

     FirstName varchar(20) NOT NULL UNIQUE NONCLUSTERED);

INSERT INTO Stones (LastName, FirstName)

SELECT 'Jagger', 'Mick'


SELECT 'Jones', 'Brian'


SELECT 'Richards', 'Keith'


SELECT 'Watts', 'Charlie'


SELECT 'Wyman', 'Bill';



INTO #MyTemp

FROM Stones Order by LastName

SELECT * from #MyTemp --will this data always be ordered?


Also, is the order guaranteed with SELECT LastName FROM Stones WITH (Index(1))"?


April 5, 2016 2:30 PM

Hugo Kornelis said:

Hi, "How about #temp table?"!

Your question:

>> SELECT * from #MyTemp --will this data always be ordered?

has a simple answer: No. It might be, but there is no guarantee. If you want to be sure that data is returned in any specific order, then you will always have to add an ORDER BY clause. Nothing else will ever provide guarantees.

April 10, 2016 8:13 AM

Andy Hogg said:

Great post.

Disappointing to see that the ability to create a supposedly ordered view is still present within the view designer in the latest version of SSMS.

I see the comments in your Connect item.

It doesn't look like there is any appetite on Microsoft's part to address this.

December 31, 2016 11:20 AM

Pablone said:

In the first query you don't know if it is ordering ascending by first or last name because in both cases the result is the same. In the second query it is not random, it seems it is ordering for first name, the non clustered index. It seems in both cases it is ordering ascending for the non-clustered index, but why it is not using the clusted index?

February 15, 2017 10:38 AM

Leave a Comment


About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Privacy Statement