SQL Server default character encoding

by

Encodings

In most cases, SQL Server stores Unicode data (i.e. that which is found in the XML and N-prefixed types) in UCS-2 / UTF-16 (storage is the same, UTF-16 merely handles Supplementary Characters correctly). This is not configurable: there is no option to use either UTF-8 or UTF-32 (see UPDATE section at the bottom re: UTF-8 starting in SQL Server 2019). Whether or not the built-in functions can properly handle Supplementary Characters, and whether or not those are sorted and compared properly, depends on the Collation being used. The older Collations — names starting with SQL_ (e.g. SQL_Latin1_General_CP1_CI_AS) xor no version number in the name (e.g. Latin1_General_CI_AS) — equate all Supplementary Characters with each other (due to having no sort weight). Starting in SQL Server 2005 they introduced the 90 series Collations (those with _90_ in the name) that could at least do a binary comparison on Supplementary Characters so that you could differentiate between them, even if they didn’t sort in the desired order. That also holds true for the 100 series Collations introduced in SQL Server 2008. SQL Server 2012 introduced Collations with names ending in _SC that not only sort Supplementary Characters properly, but also allow the built-in functions to interpret them as expected (i.e. treating the surrogate pair as a single entity). Starting in SQL Server 2017, all new Collations (the 140 series) implicitly support Supplementary Characters, hence there are no new Collations with names ending in _SC.

Starting in SQL Server 2019, UTF-8 became a supported encoding for CHAR and VARCHAR data (columns, variables, and literals), but not TEXT (see UPDATE section at the bottom re: UTF-8 starting in SQL Server 2019).

Non-Unicode data (i.e. that which is found in the CHAR, VARCHAR, and TEXT types — but don’t use TEXT, use VARCHAR(MAX) instead) uses an 8-bit encoding (Extended ASCII, DBCS, or EBCDIC). The specific character set / encoding is based on the Code Page, which in turn is based on the Collation of a column, or the Collation of the current database for literals and variables, or the Collation of the Instance for variable / cursor names and GOTO labels, or what is specified in a COLLATE clause if one is being used.

To see how locales match up to collations, check out:

To see the Code Page associated with a particular Collation (this is the character set and only affects CHAR / VARCHAR / TEXT data), run the following:

SELECT COLLATIONPROPERTY( 'Latin1_General_100_CI_AS' , 'CodePage' ) AS [CodePage];

To see the LCID (i.e. locale) associated with a particular Collation (this affects the sorting & comparison rules), run the following:

SELECT COLLATIONPROPERTY( 'Latin1_General_100_CI_AS' , 'LCID' ) AS [LCID];

To view the list of available Collations, along with their associated LCIDs and Code Pages, run:

SELECT [name],
       COLLATIONPROPERTY( [name], 'LCID' ) AS [LCID],
       COLLATIONPROPERTY( [name], 'CodePage' ) AS [CodePage]
FROM sys.fn_helpcollations()
ORDER BY [name];

Defaults

Before looking at the Server and Database default Collations, one should understand the relative importance of those defaults.

The Server (Instance, really) default Collation is used as the default for newly created Databases (including the system Databases: master, model, msdb, and tempdb). But this does not mean that any Database (other than the 4 system DBs) is using that Collation. The Database default Collation can be changed at any time (though there are dependencies that might prevent a Database from having it’s Collation changed). The Server default Collation, however, is not so easy to change. For details on changing all collations, please see: Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

The server/Instance Collation controls:

  • local variable names
  • CURSOR names
  • GOTO labels
  • Instance-level meta-data

The Database default Collation is used in three ways:

  • as the default for newly created string columns. But this does not mean that any string column is using that Collation. The Collation of a column can be changed at any time. Here knowing the Database default is important as an indication of what the string columns are most likely set to.
  • as the Collation for operations involving string literals, variables, and built-in functions that do not take string inputs but produces a string output (i.e. IF (@InputParam = 'something') ). Here knowing the Database default is definitely important as it governs how these operations will behave.
  • Database-level meta-data

The column Collation is either specified in the COLLATE clause at the time of the CREATE TABLE or an ALTER TABLE {table_name} ALTER COLUMN, or if not specified, taken from the Database default.

Since there are several layers here where a Collation can be specified (Database default / columns / literals & variables), the resulting Collation is determined by Collation Precedence.

All of that being said, the following query shows the default / current settings for the OS, SQL Server Instance, and specified Database:

SELECT os_language_version,
       ---
       SERVERPROPERTY('LCID') AS 'Instance-LCID',
       SERVERPROPERTY('Collation') AS 'Instance-Collation',
       SERVERPROPERTY('ComparisonStyle') AS 'Instance-ComparisonStyle',
       SERVERPROPERTY('SqlSortOrder') AS 'Instance-SqlSortOrder',
       SERVERPROPERTY('SqlSortOrderName') AS 'Instance-SqlSortOrderName',
       SERVERPROPERTY('SqlCharSet') AS 'Instance-SqlCharSet',
       SERVERPROPERTY('SqlCharSetName') AS 'Instance-SqlCharSetName',
       ---
       DATABASEPROPERTYEX(N'{database_name}', 'LCID') AS 'Database-LCID',
       DATABASEPROPERTYEX(N'{database_name}', 'Collation') AS 'Database-Collation',
  DATABASEPROPERTYEX(N'{database_name}', 'ComparisonStyle') AS 'Database-ComparisonStyle',
       DATABASEPROPERTYEX(N'{database_name}', 'SQLSortOrder') AS 'Database-SQLSortOrder'
FROM   sys.dm_os_windows_info;

Installation Default

Another interpretation of “default” could mean what default Collation is selected for the Instance-level collation when installing. That varies based on the OS language, but the (horrible, horrible) default for systems using “US English” is SQL_Latin1_General_CP1_CI_AS. In that case, the “default” encoding is Windows Code Page 1252 for VARCHAR data, and as always, UTF-16 for NVARCHAR data. You can find the list of OS language to default SQL Server collation here: Collation and Unicode support: Server-level collations. Keep in mind that these defaults can be overridden; this list is merely what the Instance will use if not overridden during install.

UPDATE 2018-10-02

SQL Server 2019 introduces native support for UTF-8 in VARCHAR / CHAR datatypes (not TEXT!). This is accomplished via a set of new collations, the names of which all end with _UTF8. This is an interesting capability that will definitely help some folks, but there are some “quirks” with it, especially when UTF-8 isn’t being used for all columns and the Database’s default Collation, so don’t use it just because you have heard that UTF-8 is magically better. UTF-8 was designed solely for ASCII compatibility: to enable ASCII-only systems (i.e. UNIX back in the day) to support Unicode without changing any existing code or files. That it saves space for data using mostly (or only) US English characters (and some punctuation) is a side-effect. When not using mostly (or only) US English characters, data can be the same size as UTF-16, or even larger, depending on which characters are being used. And, in cases where space is being saved, performance might improve, but it might also get worse.

For a detailed analysis of this new feature, please see my post, “Native UTF-8 Support in SQL Server 2019: Savior or False Prophet?“.

More Related Contents:

  1. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  2. When must we use NVARCHAR/NCHAR instead of VARCHAR/CHAR in SQL Server?
  3. How to add xml encoding to xml Output in SQL Server
  4. determining the character set of a table / database?
  5. SQL Server – defining an XML type column with UTF-8 encoding
  6. Changing SQL Server collation to case insensitive from case sensitive?
  7. SQL Query Where Column = ” returning Emoji characters 🎃 and 🍰
  8. SQL Server, How to set auto increment after creating a table without data loss?
  9. When should I use a table variable vs temporary table in sql server?
  10. Datatype for storing ip address in SQL Server
  11. SQL Server: Query fast, but slow from procedure
  12. Errors: “INSERT EXEC statement cannot be nested.” and “Cannot use the ROLLBACK statement within an INSERT-EXEC statement.” How to solve this?
  13. Is there a way to loop through a table variable in TSQL without using a cursor?
  14. sql server invalid object name – but tables are listed in SSMS tables list
  15. How to get a date in YYYY-MM-DD format from a TSQL datetime field?
  16. What columns generally make good indexes?
  17. What is a free tool to compare two SQL Server Databases? [closed]
  18. SQL Server Management Studio alternatives to browse/edit tables and run queries [closed]
  19. SQL Server error on update command – “A severe error occurred on the current command”
  20. Recursive sum in tree structure
  21. SQL Server SP – Pass parameter for “IN” array list?
  22. Why the SQL Server ignore the empty space at the end automatically?
  23. Add default value of datetime field in SQL Server to a timestamp
  24. SQL server schema and default schema
  25. SQL to find first non-numeric character in a string
  26. How to decrypt a password from SQL server?
  27. What are the advantages of VistaDB
  28. How to pivot unknown number of columns & no aggregate in SQL Server?
  29. In SQL Server, how can I find everywhere a column is referenced?
  30. Convert float into varchar in SQL Server without scientific notation

Leave a Comment