SQL Server default character encoding

Question

By default - what is the character encoding set for a database in Microsoft SQL Server   How can I see the current character encoding in SQL Server

User · Answer

I think this is worthy of a separate answer  although internally unicode data is stored as UTF-16 in Sql Server this is the Little Endian flavour  so if you re calling the database from an external system  you probably need to specify UTF-16LE

User · Answer

You can see collation settings for each table like the following code   SELECT t name TableName  c name ColumnName  collation name   FROM sys columns c   INNER JOIN sys tables t on c object id   t object id where t name    name of table

User · Answer

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  mdash  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   mdash  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  mdash  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    Windows Collation Name SQL Server Collation Name   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  amp  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  amp  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

User · Answer

SELECT DATABASEPROPERTYEX  DBName    Collation   SQLCollation   Where DBName is your database name

User · Answer

The default character encoding for a SQL Server database is iso 1  which is ISO 8859-1  Note that the character encoding depends on the data type of a column  You can get an idea of what character encodings are used for the columns in a database as well as the collations using this SQL   select data type  character set catalog  character set schema  character set name  collation catalog  collation schema  collation name  count    count from information schema columns group by data type  character set catalog  character set schema  character set name  collation catalog  collation schema  collation name    If it s using the default  the character set name should be iso 1 for the char and varchar data types   Since nchar and nvarchar store Unicode data in UCS-2 format  the character set name for those data types is UNICODE

User · Answer

If you need to know the default collation for a newly created database use   SELECT SERVERPROPERTY  Collation    This is the server collation for the SQL Server instance that you are running

[sql-server] SQL Server default character encoding

Examples related to sql-server

Examples related to character-encoding

Examples related to collation