{"id":70501,"date":"2017-04-06T16:15:05","date_gmt":"2017-04-06T16:15:05","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=70501"},"modified":"2021-08-24T13:39:25","modified_gmt":"2021-08-24T13:39:25","slug":"questions-sql-server-collations-shy-ask","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/questions-sql-server-collations-shy-ask\/","title":{"rendered":"Questions About SQL Server Collations You Were Too Shy to Ask"},"content":{"rendered":"
    \n
  1. What is a SQL Server collation?<\/a><\/li>\n
  2. How do I find the collations supported on a SQL Server instance?<\/a><\/li>\n
  3. How do I identify Unicode-only collations?<\/a><\/li>\n
  4. What are the differences between Windows collations and SQL Server collations?<\/a><\/li>\n
  5. How do I set the collation at the server level?<\/a><\/li>\n
  6. How do I configure a database\u2019s default collation?<\/a><\/li>\n
  7. How do I assign a collation to a column?<\/a><\/li>\n
  8. How do I specify a collation when querying data?<\/a><\/li>\n
  9. How do I query columns with different collations?<\/a><\/li>\n
  10. How do I create a foreign key on columns with different collations?<\/a><\/li>\n
  11. How do collations affect temporary tables?<\/a><\/li>\n
  12. How do I know what collations to use?<\/a><\/li>\n<\/ol>\n

    <\/a>1. What is a SQL Server collation?<\/h2>\n

    A collation is a configuration setting that determines how the database engine should treat character data at the server, database, or column level. SQL Server includes a large set of collations for handling the language and regional differences that come with supporting users and applications in different parts of the world.<\/p>\n

    Each collation serves two purposes. The first is to provide a character set that defines the bit pattern associated with each character.<\/p>\n

    SQL Server stores character data using either one byte or two bytes per character, depending on the column\u2019s data type and assigned collation. For example, European languages require only a single-byte character set, which supports up to 256 bit patterns. On the other hand, many Asian languages include thousands of characters and require a double-byte character set, which supports up to 65,536 bit patterns.<\/p>\n

    Collations are intricately tied to the data types assigned to character columns. In SQL Server, you can configure a character column with a Unicode data type (nchar<\/strong>, nvarchar<\/strong>, or ntext<\/strong>) or non-Unicode data type (char<\/strong>, varchar<\/strong>, or text<\/strong>). For Unicode types, the character bit patterns conform to international standards that define a double-byte encoding scheme for mapping most of the world\u2019s written languages, ensuring that the same bit pattern is always associated with the same character, regardless of the underlying environment.<\/p>\n

    Most collations support both Unicode and non-Unicode character data, although some support only Unicode data. Each collation that supports non-Unicode data is associated with a code page that defines the bit patterns for the non-Unicode characters. The collation must point to a code page supported by the underlying operating system; however, multiple collations can point to the same code page if they share the same character set. Unicode-only collations do not require code pages because they conform to the universal encoding model.<\/p>\n

    To view the code page associated with a collation, you can use the COLLATIONPROPERTY<\/strong> system function, as shown in the following example:<\/p>\n

    SELECT COLLATIONPROPERTY('Modern_Spanish_100_CS_AS', 'CodePage');<\/pre>\n
    The function takes two arguments. The first is the name of the collation, in this case, Modern_Spanish_100_CS_AS<\/strong>, and the second is the CodePage<\/strong> property. The SELECT<\/strong> statement returns the value 1252<\/strong>, which is the code page identifier. On my system, 894 collations share this code page. (I\u2019m running SQL Server 2016 Developer Edition on Windows 10).<\/p>\n
    The second purpose of the SQL Server collation is to specify the rules that govern how character data is sorted and compared, taking into account not only language and regional conventions, but also the options specific to a collation, such as case-sensitivity and accent-sensitivity.<\/p>\n
    Each collation enforces its own sorting and comparison rules, whether or not it points to the same code page as other collations. For this reason, query results can differ when accessing identical data in two columns configured with different collations, even if those collations are based on the same language.<\/p>\n
    In SQL Server, you can specify a collation at the server level, database level, or column level, and you can specify collations within your queries. Keep in mind, however, that you can run into query and performance issues when working with multiple collations at the same time.<\/p>\n
    <\/a>2. How do I find the collations supported on a SQL Server instance?<\/h2>\n
    To view a list of the available collations, you can use the sys.fn_helpcollations<\/strong> system table function to retrieve the collation names and their descriptions, as shown in the following example:<\/p>\n
    SELECT name, description\r\n  FROM sys.fn_helpcollations();<\/pre>\n
    On my system, the statement returns 3,887 collations that support a wide range of languages, with numerous collations for each language family. For example, the following table shows a number of the collations available to the Tibetan family (but not all):<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    Name<\/p>\n<\/td>\n
    		\n
    description<\/p>\n<\/td>\n<\/tr>\n<\/thead>\n
    \n
    Tibetan_100_CI_AI_SC<\/p>\n<\/td>\n
    		\n
    Tibetan-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive, supplementary characters<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tibetan_100_CI_AI_WS_SC<\/p>\n<\/td>\n
    		\n
    Tibetan-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive, supplementary characters<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tibetan_100_CI_AI_KS_SC<\/p>\n<\/td>\n
    		\n
    Tibetan-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive, supplementary characters<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tibetan_100_CI_AI_KS_WS_SC<\/p>\n<\/td>\n
    		\n
    Tibetan-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive, supplementary characters<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tibetan_100_CI_AS_SC<\/p>\n<\/td>\n
    		\n
    Tibetan-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive, supplementary characters<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tibetan_100_CI_AS_WS_SC<\/p>\n<\/td>\n
    		\n
    Tibetan-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive, supplementary characters<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tibetan_100_CI_AS_KS_SC<\/p>\n<\/td>\n
    		\n
    Tibetan-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive, supplementary characters<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tibetan_100_CI_AS_KS_WS_SC<\/p>\n<\/td>\n
    		\n
    Tibetan-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive, supplementary characters<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tibetan_100_CS_AI_SC<\/p>\n<\/td>\n
    		\n
    Tibetan-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive, supplementary characters<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tibetan_100_CS_AI_WS_SC<\/p>\n<\/td>\n
    		\n
    Tibetan-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive, supplementary characters<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Most language families include numerous collations, each configured with specific options, such as width-sensitivity and accent-sensitivity. When specifying a collation in your T-SQL script, you must use the name exactly as it is returned by the sys.fn_helpcollations<\/strong> function. The collation name includes the supported language and, in some cases, the collation version. For example, the collations in the table above are all specific to the Tibetan language and are all version 100.<\/p>\n
    The remaining codes in each collation name (such as _CS_AI_WS_SC<\/strong>) indicate the options preconfigured on the collation. For example, CI<\/strong> means case-insensitive and AS<\/strong> means accent-sensitive. The description<\/strong> column shows what the option codes mean. For more detailed information, see the Microsoft article \u201cCollation and Unicode Support<\/a>.\u201d<\/p>\n
    <\/a>3. How do I identify Unicode-only collations?<\/h2>\n
    Some collations support only Unicode characters and consequently can be used only with the nchar<\/strong>, nvarchar<\/strong>, or ntext<\/strong> data types, either within column definitions or when querying data from those columns. Unfortunately, you cannot tell from the collation name whether or not it is Unicode-only, but you can use a combination of the sys.fn_helpcollations<\/strong> and COLLATIONPROPERTY<\/strong> system functions to retrieve a list of Unicode-only collations:<\/p>\n
    SELECT name, description\r\n  FROM sys.fn_helpcollations()\r\n  WHERE COLLATIONPROPERTY(name, 'CodePage') = 0;<\/pre>\n
    On my system, the SELECT<\/strong> statement returns 510 rows. You can, of course, narrow down the search to specific languages, but you get the idea. You use the COLLATIONPROPERTY<\/strong> system function to find the collations with a CodePage<\/strong> value of 0<\/strong>. Because a Unicode-only collation does not point to a code page, the database engine simply returns a 0<\/strong>, rather than a NULL<\/strong> value or some obscure number, or worse still, an error.<\/p>\n
    <\/a>4. What are the differences between Windows collations and SQL Server collations?<\/h2>\n
    SQL Server supports two collation types: Windows collations and SQL Server collations. You can tell them apart by their names. All SQL Server collation names begin with SQL_<\/strong>.<\/p>\n
    Each collation type supports both character-based collations and binary-based. Once again, you can distinguish them by their names. Binary-based collations end with either _BIN<\/strong> or _BIN2<\/strong>.<\/p>\n
    Character-based Windows collations represent the bulk of the collations available to SQL Server. The collations are based on the Windows locales<\/em> available to the host operating system where SQL Server is installed. A locale is a collection of information specific to a location or culture. Windows uses locales to customize the interface for regional groups of users.<\/p>\n
    Windows collations in SQL Server use the same code pages as the Windows locales, as well as the same sorting and comparison rules. However, SQL Server provides collations for only a subset of Windows locales, and each collation supports only a subset of the attributes available to the Windows locale. The Windows collations in SQL Server are concerned with locales only in terms of data management.<\/p>\n
    Windows collations use the same algorithm for sorting and comparing both Unicode and non-Unicode data, making it easier to work with character data stored with different data types. You can retrieve a list of the character-based Windows collations available to your system by running the following SELECT<\/strong> statement:<\/p>\n
    SELECT name, description\r\n  FROM sys.fn_helpcollations()\r\n  WHERE name NOT LIKE 'sql_%' AND name NOT LIKE '%_bin%';<\/pre>\n
    All we\u2019re doing here is taking advantage of how collations are named by eliminating any names that start with sql_<\/strong> and end with _bin<\/strong> or _bin2<\/strong>. On my system, I came up with 3,552 character-based Windows collations.<\/p>\n
    Binary-based collations represent the other type of Windows collations. They are similar to the character-based collations except in how the data is sorted. For non-Unicode data, sorting is based on the coded numerical values (code points) defined by the Windows locale. For Unicode data, sorting is based on the code points defined by the Unicode standard, so locale is not a factor. In either case, the sort order is simpler to enforce, helping to improve application performance.<\/p>\n
    SQL Server supports the two types of binary-based collations:<\/p>\n