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:
SELECT TOP 100 Id, DisplayName
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:
SELECT TOP 100 DisplayName
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.