Sort Order

A sort order is a set of rules that determines how SQL Server collates and presents data in response to database queries. The sort order determines the order in which data is presented in response to SQL Server statements involving GROUP BY, ORDER BY, and DISTINCT. It also determines how certain queries are resolved, such as those involving WHERE and DISTINCT.

Important It is critical that you select the correct sort order when you first install SQL Server. If you need to change sort orders after you've installed SQL Server, you must rebuild your databases and reload your data.

SQL Server offers several sort orders:

The version 1.x-compatible sort order applies only to code page 850.

The following table shows how the sort order affects comparisons and sorting:

Sort order Comparison examples Sorting example
Dictionary order,
case-insensitive		 A = a, Ä = ä, Å = å,
a ¹ à ¹ á ¹ â ¹ ä ¹ å,
A ¹ Ä ¹ Å		 A, a, à, á, â, ä, Ä, Å, å
(See Note 2)
Binary See Note 1 See Note 1
Dictionary order,
case-sensitive		 A ¹ a, Ä ¹ ä, Å ¹ å,
a ¹ à ¹ á ¹ â ¹ ä ¹ å,
A ¹ Ä ¹ Å		 A, a, à, á, â, Ä, ä, Å, å
Dictionary order
case-insensitive, uppercase preference		 A = a, Ä = ä, Å = å,
a ¹ à ¹ á ¹ â ¹ ä ¹ å,
A ¹ Ä ¹ Å		 A, a, à, á, â, Ä, ä, Å, å
Dictionary order
case-insensitive,
accent-insensitive		 A = a = à = á = â = Ä =
ä = Å = å		 A, a, à, á, â, Ä, ä, Å, å
Strict compatibility
with version 1.x case-
insensitive databases		 See Note 3 See Note 3
Custom See Note 4 See Note 4
1    Comparisons and sorting for this sort order are based on the numeric values of the characters
    in the installed character set.

2    This is just one example of how the characters might be sorted. Because this sort order does
    not have uppercase preference, it is faster than the corresponding sort order with uppercase
    preference, but you cannot predict whether an uppercase character will come before or after its
    corresponding lowercase character.

3    For the first 128 characters, comparisons and sorting for this sort order are the same as the
    case-insensitive dictionary sort order. For the last 128 characters, comparisons and sorting are
    the same as the Binary sort order.

4    Comparisons and sorting for a custom sort order depend on the sort order.

The four alternate sort orders and the two Scandinavian sort orders provide results similar to their counterparts. For details about how these sort orders differ from their counterparts, see Appendix A, Character Sets and Sort Orders.

When upgrading a version 4.2x SQL Server, setup installs the new program files but does not change the installed sort order.

For additional information about SQL Server sort order choices, see Appendix A, Character Sets and Sort Orders.