Michael J. Swart

As many people will point out, you can’t depend on the order of results without using the ORDER BY clause. So it’s easy to say “Simple! Don’t write code that expects unsorted data to be sorted”. But it’s very difficult to be careful everywhere all the time.

Remember that this is an application problem and is not a SQL problem. We only get into trouble when applications (or people) expect results to be sorted when they’re not. So unless you have a tiny application, or a huge amount of discipline, it’s likely that there is some part of your application that assumes sorted results when it shouldn’t.

Here’s a method I used that attempts to identify such areas, exposing those assumptions. It involves reversing indexes.

If you don’t ask for results to be ordered they may still appear to be ordered. SQL Server will return results in a way that’s convenient and this is often in some index order. So if the indexes are reversed, then the idea is that what’s convenient for SQL Server will be reversed.

Which results are ordered on purpose and which are ordered by luck?

It’s impossible to tell. But after the indexes are reversed:

It’s now apparent.

Reverse the Indexes On A Dev Box

First use this powershell script to generate some SQL. It’s a script adapted from a Stackoverflow answer by Ben Thul “How to script primary key constraints”

[string]$dbname = "Adventureworks2012";
[string]$server = ".";
 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $server
$db = $SMOserver.databases[$dbname]
 
"" > drop_indexes.sql
"" > create_indexes.sql
"" > drop_foreign_keys.sql
"" > create_foreign_keys.sql
 
$option_drop = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions;
$option_drop.ScriptDrops = $true;
foreach ($table in $db.Tables) {
	foreach ($index in $table.Indexes) {
		$index.Script( $option_drop ) >> drop_indexes.sql
		$index.Script() >> create_indexes.sql
	}
	foreach ($foreign_key in $table.ForeignKeys) {
		$foreign_key.Script( $option_drop ) >> drop_foreign_keys.sql
		$foreign_key.Script() >> create_foreign_keys.sql
	}
}

Now follow these steps. Use the generated SQL to:

1. Drop the foreign keys
2. Drop the indexes
3. Open up the create_indexes.sql script in an editor and swap ASC for DESC and vice versa
4. Create the reversed indexes
5. Recreate the foreign keys

That’s it! Now unsorted results will be returned in a format convenient to SQL Server which should be opposite to the original order.

Test

Remember how these ORDER BY assumptions are human or application problems? It’s time to bring them into this process. Test your applications/reports manually, or if you’re fortunate enough to have them, run your automated tests.

I’m one of the fortunate ones. I have access to a suite of automated tests that includes thousands of integration tests. In my case, roughly one percent of them failed after this experiment. Colleagues reactions were varied. They ranged from “That many?” to “That few?”

This experiment cannot identify all ordering assumptions, but it has a good chance at identifying many.

Fix

First let me give some advice on how not to fix this. Don’t begin by indiscriminately sprinkling ORDER BY clauses throughout all your queries. I found the best approach is to handle each problem on a case-by-case basis.

Here are some approaches:

• Fix the test For automated tests, sometimes the test itself assumed an order. This is an easy case to deal with.
  • For example, use CollectionAssert.AreEquivalent instead of CollectionAssert.AreEqual.
  • And don’t forget to double-check the report or UI element that uses the same query. There’s a good chance the UI is making the same assumption as the test.
• Order results in the app If you’re using C#, try using Linq’s Enumerable.OrderBy. And if you’re using some other language or reporting framework, you should be able to sort there too.
• Order in SQL If necessary order your results using SQL with the ORDER BY clause.

Happy ordering!