Character Sets and Sort Orders

The character set determines the types of characters that SQL Server recognizes in databases. A character set is a set of 256 letters, numbers, and symbols specific to a country or a language. The printable characters of the first 128 values are the same for all character sets. The last 128 characters, sometimes referred to as extended characters, are different.

You should use the same character set for both the client and the server, or your results can be unpredictable. However, if your databases use only the first 128 characters of a character set (for example, if you do not use characters with diacritical marks), it makes no difference which character set you use, because the first 128 characters are the same for all character sets.

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. The sort order also determines how certain queries are resolved, such as queries involving WHERE and DISTINCT.

During installation, SQL Server allows you to select a character set and a sort order to be used by the server. Several sort orders and character sets are available. The default character set is ANSI (ISO 8859-1), and the default sort order is dictionary order, case-insensitive. For in-depth information about character sets and sort orders, see Microsoft SQL Server Setup.

The character set and sort order applies to the entire server. You cannot have different databases with different character sets or sort orders on a server.

You cannot dump and load databases between servers that are configured for different character sets or sort orders.

Caution Care should be taken to select the correct character set and sort order during SQL Server installation. If the character set or sort order needs to be changed after SQL Server is installed, you must completely rebuild the master database, which renders all other databases useless. Reinstalling SQL Server with a new character set or sort order can profoundly affect the results of queries to previously created databases.