Return query results in the exact same order every time

Query result ordering is not guaranteed. If your query does not include an ORDER BY clause, the order of your results is completely arbitrary. This is because tables are sets, which themselves have no order.

For example, in SQL Server the fastest possible path to the results influences the order of return. If you run the same query on a different machine, the order of your results may be different. If the order of the results matters, you must explicitly specify an order.

That’s what ORDER BY is for, right? Well yes, but with a caveat. If you only order by one column, and the order of two or more rows is tied, the order of those tied rows is undefined. There’s no way to be certain which of the ties will be returned.

So for example, what if our requirement is to show an ordered list of product categories in a dropdown menu for the end user? What if we need to return those results in a particular order every time and there’s a chance of ties?

What then?!

In that case you must add an ORDER BY column with two (or more) criteria for sorting. In other words, you need a tiebreaker. How many you need depends on the data itself.

USE AdventureWorks2017;
GO

SELECT
	[Name]
FROM Production.ProductCategory pc
ORDER BY pc.Name ASC, pc.ProductCategoryID ASC;

| Name        |
|-------------|
| Accessories |
| Bikes       |
| Clothing    |
| Components  |

These queries are called deterministic. They will return the same results in the same order, no matter who runs them.

USE AdventureWorks2017;
GO

SELECT TOP 0.01 PERCENT
	sd.SalesOrderID,
	CAST(sd.ModifiedDate AS date) AS ModifiedDate,
	sd.OrderQty as Quantity,
	CAST(sd.LineTotal AS decimal(18, 2)) AS LineTotal
FROM Sales.SalesOrderDetail sd
 -- Multiple columns specified in the ORDER BY create a unique row and breaks ties
ORDER BY sd.LineTotal DESC, sd.OrderQty DESC, sd.ModifiedDate DESC;

| SalesOrderID | ModifiedDate | Quantity | LineTotal |
|--------------+--------------+----------+-----------|
|        55282 |   2013-08-30 |       26 |  27893.62 |
|        43884 |   2011-07-01 |       14 |  27055.76 |
|        51131 |   2013-05-30 |       21 |  26159.21 |
|        43875 |   2011-07-01 |       13 |  24938.48 |
|        57054 |   2013-09-30 |       19 |  23667.85 |
|        44795 |   2011-10-31 |       12 |  23190.65 |
|        43875 |   2011-07-01 |       12 |  23190.65 |
|        46090 |   2012-03-30 |       12 |  23020.13 |
|        44518 |   2011-10-01 |       12 |  23020.13 |
|        44534 |   2011-10-01 |       12 |  23020.13 |
|        53460 |   2013-07-31 |       30 |  22963.37 |
|        55282 |   2013-08-30 |       18 |  22422.18 |
|        51823 |   2013-06-30 |       18 |  22422.18 |