Start with the Stack Overflow database, with no nonclustered indexes. If you’ve been playing around with demos, run my DropIndexes proc just to clear things out, which leaves the clustered indexes in place.
Say you run this query:
|
1 2 |
SELECT TOP 100 Id, DisplayName FROM dbo.Users; |
By default, with a clustered index on the Id field (identity, starts at 1 and goes up to a bajillion), your results will probably look like this, ordered by Id:
Because if you look at the execution plan:
SQL Server is doing a clustered index scan. Since we’re clustered on Id, the results happen to come back in Id order.
But watch what happens when I create an index on DisplayName and Id, and then run the exact same query again:
Now the data comes back ordered by DisplayName due to the way SQL Server ran the query, as shown in the execution plan:
As I talk about in How to Think Like the Engine, SQL Server doesn’t have to do a clustered index scan for queries like these. It can use the smallest copy of the table that achieves its objectives, and in this case, it’ll use the nonclustered index because it takes less time to scan.
If you have to scan the Users table to get DisplayName and Id, which of these two objects would you rather scan:
The clustered index is 58MB, but our index on DisplayName and Id is only 9MB. It’s faster to scan that nonclustered index.
SQL Server may use indexes you didn’t expect.
Say we’ve got an index on DisplayName that happens to include Reputation, and we frequently run this query:
|
1 2 3 |
SELECT TOP 100 DisplayName FROM dbo.Users WHERE Reputation = 1; |
Whenever we run the query, we always get the data back sorted by DisplayName, so we assume that it’s always going to be that way:
But that’s just because SQL Server happened to use the index on DisplayName that includes Reputation:
Because SQL Server knows a whole lotta users have Reputation = 1, so it can scan that index and quickly find the rows it needs. It doesn’t need to scan the ENTIRE index, just enough of it to find 100 users with Reputation = 1.
But the instant we create a “better” index – one that allows a seek, for example – and say that we only include DisplayName (not sort it):
The data doesn’t come back ordered. SQL Server did an index seek on our new index:
But since DisplayName was only included, not sorted, even just a 100-row result set doesn’t come back in the order you might expect.
So in summary: if you need stuff ordered, add an ORDER BY, or else your order can get changed based on the execution plan SQL Server chooses.
9 Comments. Leave new
Can parallelism impact ordering too?
Brian – yep.
ha remember this pre SQL Server 2005 nonsense
create view select top 100 percent order by..
Glad that went away too but I believe there is a trace flag that will enable that behavior (some shop must have paid some good money for that one)
The trick is to use SELECT TOP 999999999 instead (add more 9s if necessary).
I’m aware of two motivations for such tricks:
1) Force SQL to materialize an intermediate query. This can sometimes result in improved query plans, but it’s not particularly robust. Usually a temp table can accomplish the same thing more robustly.
2) Allow order by in a view. This is generally an awful bad idea: the view is not guaranteed to stay in order.
you can also do 2 billion.. I just tested it out.. it still works lol see here for screenshot: https://twitter.com/DenisGobo/status/1128715434948268032
Code…
create table F(x int primary key)
insert f values (3),(5),(1)
go
create view f_vw –__CELKO__ approved name…. NOT
as
select top 100 percent *
from F order by x desc
go
create view f2_vw –__CELKO__ approved name…. NOT
as
select top 2147483647 *
from F order by x desc
go
select x f from f
select x f_vw from f_vw
select x f2_vw from f2_vw
Hello,
do you know if order is guaranteed even if the field you order on contains more than one identical value? For example if I order on a date field and there are 10 entries with exactly the same date – is the order of those 10 entries always identical?
No. Add a second column to the order by.
Thanks for the clear explanation.
Is this how the sql works on all the versions? I mean 2008, 2014 with new CE?
Yep.