A sort order is a set of rules that determines how Microsoft® SQL Server™ collates and presents data in response to database queries. A sort order determines how character data is compared and in what sequence it is returned from a query.
At various points in a query, SQL Server compares characters to see if they are equal. A sort order determines what characters are considered equal when compared. A case-insensitive sort order, for instance, considers an uppercase letter to be the same as its lower-case equivalent. A case-sensitive sort order considers the two characters to be different. Another example is accent sensitivity: whether characters with accents are considered the same as their unaccented counterparts.
Important If you install a case-sensitive sort order, all entries are case-sensitive, including object names. This SELECT statement against a table named Customers is successful on a server with a case-insensitive sort order.
SELECT * FROM CUSTOMERS
The same statement fails on a server with a case-sensitive sort order.
A sort order also determines the sequence of rows in the result set of a query.
The sort orders available depend on the character set you chose. You cannot have different databases with different sort orders on the same server. In addition, you cannot back up and restore databases between servers configured for different sort orders.
Important It is critical that you select the correct sort order when you install SQL Server. If you need to change sort orders after installation, you must rebuild your databases and reload your data.
Here is an example of how the sort order affects comparisons and sorting.
| Sort order | Comparison examples | Sorting example |
|---|---|---|
| Dictionary order, case-insensitive | A = a, Ä = ä, Å = å, a ¹ à ¹ á ¹ â ¹ ä ¹ å, A ¹ Ä ¹ Å |
a, A, à, á, â, ä, Ä, Å, å1 |
| Binary2 | A ¹ a, Ä ¹ ä, Å ¹ å, a ¹ à ¹ á ¹ â ¹ ä ¹ å, A ¹ Ä ¹ Å |
|
| 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, à, â, á, Ä, ä, Å, å |
| 1 This is one example of how the characters may be sorted. Because this sort order does not have uppercase preference, you cannot predict whether an uppercase character will come before or after its corresponding lowercase character.
2 Sorting for this sort order is based on the numeric values of the characters in the installed character set. |
||
Other sort orders provide similar results.