The list is sorted, yet the values still look out of order. Collation and ORDER BY decide what SQL Server compares and which row appears first.
Always State the Order You Need
A SELECT without ORDER BY has no guaranteed presentation order. An index scan can look sorted until a plan changes. Put ORDER BY in the outermost query that returns rows to the reader. A sort inside a CTE or view does not promise final display order.
I ask which columns define a tie. Sorting by CustomerName alone leaves customers with the same name in arbitrary order. Add a stable key such as CustomerId so paging and repeatable exports do not shift between runs.
A database can store rows in an index order, but a relational result is still unordered unless the query requests order. That distinction saves many arguments about why a report changed after an index rebuild.
SELECT CustomerId, CustomerName
FROM dbo.Customer
ORDER BY CustomerName, CustomerId;Know What Collation Controls in ORDER BY
Collation influences string comparisons and sorting. It can be case-sensitive or case-insensitive, accent-sensitive or accent-insensitive, among other options. The database or column default can differ from another source. A join between databases can fail with a collation conflict or compare names under unexpected rules.
Use stable keys for joins when possible. Names are display data and can change. When string comparison is required, decide the desired rules rather than accepting whichever collation happened to be assigned during installation.
I test values that differ only by case or accent. A sample containing only plain English letters cannot reveal the rule. The exact collation name matters, so inspect the current database and column definitions before changing a query.
SELECT name, collation_name
FROM sys.databases
WHERE name = DB_NAME();
SELECT name, collation_name
FROM sys.columns
WHERE object_id = OBJECT_ID(N'dbo.Customer')
AND name = N'CustomerName';Force One Query’s Collation When Needed
A query can apply COLLATE to an expression for one comparison or sort. This is useful when a report needs a specific case or accent rule without changing the column for every application. Choose a collation supported by the instance and test its exact behavior.
An expression-level COLLATE can affect index use and add sorting work. Inspect the plan for an important report. A persisted normalized key or separate search column can be better for a high-volume path. The query-level rule is still a good way to make a small report explicit.
I avoid changing database collation to fix one dropdown. That is a broad operation with many dependencies. Apply the narrow rule first, measure it, and only plan a wider change when the data model requires one.
SELECT CustomerId, CustomerName
FROM dbo.Customer
ORDER BY CustomerName COLLATE Latin1_General_100_CS_AS,
CustomerId;
Sort Numbers as Numbers
A varchar column containing 1, 2, and 10 sorts lexically, so 10 can appear before 2. That is correct string order and wrong numeric order. The durable fix is to store numeric data in a numeric type. For a temporary report over legacy text, TRY_CONVERT can separate valid numeric values from bad ones.
Decide where invalid values belong in the sort. TRY_CONVERT returns NULL for text that is not numeric. A NULL sort position can hide data quality problems unless the report shows the original text too. Do not silently cast a mixed code field if leading zeros are meaningful.
I distinguish identifiers from quantities. A code such as 0010 can be text by design, and converting it to 10 loses formatting. The business meaning decides whether numeric ordering is appropriate.
SELECT CodeText, TRY_CONVERT(int, CodeText) AS NumericValue
FROM dbo.LegacyCode
ORDER BY TRY_CONVERT(int, CodeText), CodeText;Understand NULL and Tie Order
SQL Server’s ascending sort puts NULL before non-NULL values. If the report needs missing names last, add an explicit CASE expression to the ORDER BY. Then add a stable key for ties. A clear rule is better than relying on an accidental plan order.
I check whether blank strings and NULL both mean missing. They can sort differently and affect filters. Normalize them at the data boundary if the domain treats them as one state, or handle them separately in the report.
For pagination, deterministic tie order is essential. A query that returns rows in an apparently stable order can repeat or skip items between pages when the tie rule is omitted. Test the boundary with duplicated sort values.
SELECT CustomerId, CustomerName
FROM dbo.Customer
ORDER BY CASE WHEN CustomerName IS NULL THEN 1 ELSE 0 END,
CustomerName, CustomerId;Keep Collation and ORDER BY Rules Near the Consumer
An operational report can need a different order from an export. Put the desired ORDER BY in each final query. A view can expose useful normalized columns, but should not be treated as a presentation order contract. The caller owns the last ordering step.
A text search can have yet another rule. Case-insensitive filtering and case-sensitive display sort can coexist when specified. Document that choice so a user does not confuse matching with ordering. They are related collation decisions, not the same operation.
I compare report output with a small deliberately awkward test set. Include case variants, accents, numbers as text, NULL, and ties. The test reveals the exact order more clearly than a large production list.
Measure Collation and ORDER BY Cost After Correctness
Sorting can require memory and spill to tempdb. An index that matches filter and order can avoid some work. A query-level collation or expression in ORDER BY can require a separate sort. Inspect actual plans and memory grants for the workload that matters.
Do not drop the required order simply to make the query look faster. A report whose order changes across pages is not correct. Find an index or data representation that supports the specified semantics. Sometimes a small result set needs no optimization at all.
The right sort is a business rule expressed in SQL. State the order, choose collation and type semantics, add tie breakers, then measure its cost. Readers notice wrong order immediately, even when the database reports a quick execution.
Which collation governs the sort: the column, an explicit expression, or the database default? A migration between databases can change string ordering while leaving query text untouched. Include ties and mixed case in a test set, then add a unique tie-breaker for stable pages. I check the result under the same collation and language assumptions the application uses, especially when an export must repeat in the same order.
Related reading on this blog: How to Sort a Varchar Column Storing Integers with Order By? Interview Question of the Week #206 and Resolve Cannot Resolve Collation Conflict Error: SQL in Sixty Seconds #047.
ORDER BY is not cosmetic, it is the contract for how a result is presented.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
