SQL Server: набор символов (не сопоставление)

Question

Как установить набор символов по умолчанию для полей при создании таблиц в SQL Server? В MySQL это делается так:

CREATE TABLE tableName (
    name VARCHAR(128) CHARACTER SET utf8
) DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

Обратите внимание, что я установил набор символов здесь дважды. Это избыточно, я добавил оба способа только для демонстрации.

Я установил параметры сортировки также, чтобы продемонстрировать, что параметры сортировки - это нечто другое. Я не спрашиваю об установке параметров сортировки. На большинство вопросов, касающихся наборов символов и кодировок в SQL Server, даются ответы с сопоставлением, что не одно и то же.

Answer 1

Как указано в BOL

Каждое сопоставление SQL Server определяет три свойства:

• Порядок сортировки для типов данных Unicode (nchar, nvarchar и ntext). Порядок сортировки определяет последовательность, в которой сортируются символы, и способ оценки символов в операциях сравнения.
• Порядок сортировки, используемый для символьных типов данных, отличных от Unicode (char, varchar и text).
• Кодовая страница, используемая для хранения данных не-Unicode символов.

Выше цитата из 2000 документов. Смотрите также эту ссылку 2008 года. Ниже также демонстрирует это.

DECLARE @T TABLE 
(
     code TINYINT PRIMARY KEY,
     Arabic_CS_AS CHAR(1) COLLATE Arabic_CS_AS NULL,
     Cyrillic_General_CS_AS CHAR(1) COLLATE Cyrillic_General_CS_AS NULL,
     Latin1_General_CS_AS CHAR(1) COLLATE Latin1_General_CS_AS NULL
);

INSERT INTO @T(code) VALUES (200),(201),(202),(203),(204),(205)

UPDATE @T 
  SET Arabic_CS_AS=CAST(code AS BINARY(1)),
      Cyrillic_General_CS_AS=CAST(code AS BINARY(1)),
      Latin1_General_CS_AS=CAST(code AS BINARY(1))

SELECT * 
FROM @T   

Результаты

code Arabic_CS_AS Cyrillic_General_CS_AS Latin1_General_CS_AS
---- ------------ ---------------------- --------------------
200  ب            И                      È
201  ة            Й                      É
202  ت            К                      Ê
203  ث            Л                      Ë
204  ج            М                      Ì
205  ح            Н                      Í

Answer 2

To expand on @Martin's answer:

How you set a "character set" in SQL Server depends on the datatype that you are using. Если вы используете:

• NVARCHAR, NCHAR, а также NTEXT (NTEXT is deprecated and shouldn't be used as of SQL Server 2005) all use the Unicode character set and this cannot be changed. These datatypes are all encoded as UTF-16 LE (Little Endian) – a 16-bit encoding with each "character" being either 2 or 4 bytes – and this too cannot be changed. For these datatypes, the Collation being used only affects the locale (as determined by the LCID of the Collation) which determines the set of rules used for sorting and comparison.

• XML, like the N-prefixed types, uses the Unicode character set and is encoded as UTF-16 LE (Little Endian), and neither of those can be changed. But unlike the other string datatypes, there is no Collation associated with XML data as it cannot be sorted or compared (at least not without first converting it to NVARCHAR(MAX) [preferred] or VARCHAR(MAX)).

• VARCHAR, CHAR, а также TEXT (TEXT is deprecated and shouldn't be used as of SQL Server 2005) are all 8-bit encodings with each "character" being either 1 or 2 bytes. The character set is determined by the Code Page associated with each Collation. The sorting and comparison rules depend on the type of Collation being used:

  • SQL Server Collations: These all have names starting with SQL_ and have been deprecated since SQL Server 2000, though are (unfortunately) still in wide use today. These use simple rules indicated as the "SQL Server Sort Order" number as found in the description field returned by sys.fn_helpcollations(),
  • Windows Collations: These all have names that do not start with SQL_, These Collations allow the non-Unicode string data to use the Unicode sorting and comparison rules indicated by the LCID of the Collation.

That being said, to find out which character set (for CHAR, VARCHAR, а также TEXT – ie non-Unicode – data) is being used, run the following query and pay close attention to the CodePage поле. LCID field indicates the locale used for sorting and comparison rules for the N-prefixed – ie Unicode – types as well as the non-Unicode types if using a Windows Collation:

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

The Code Page IDs can be translated into something more meaningful via the MSDN page for Code Page Identifiers.

---

Regarding the OP's comment on @Martin's answer:

It is unfortunate that they chose the misleading/incomplete term "collation" which clearly refers to sort order: collate definition.

While it is true that Microsoft could have done better when choosing a name, there is unfortunately a general, industry-wide confusion over terms such as "encoding", "character set", "collation", etc. Microsoft's use (or misuse) of "Collation" has merely contributed to the mass confusion. But, that confusion is also evident in MySQL as shown in this question, given that "utf8" is specifically not a character set;-).

UTF-8 - одна из нескольких кодировок для набора символов Unicode. UTF-16 и UTF-32 являются двумя другими кодировками. Все три из этих кодировок представляют один и тот же набор символов Unicode, только по-разному. Глядя на список наборов символов MySQL - 11.1.10 Поддерживаемые наборы символов и сопоставления - кодировки "ucs2", "utf8", "utf8mb4", "utf16", "utf16le", "utf32" на самом деле не являются наборами символов. само по себе, но различные представления набора символов Unicode. Но, учитывая совпадение понятий "набор символов" и "кодировка", было бы трудно не иметь такой путаницы. На странице 11.1.10.1 Наборы символов Unicode указывается, что кодировки "utf8mb4", "utf16", "utf16le" и "utf32" являются полными наборами символов Unicode, тогда как "ucs2" и "utf8" являются подмножествами набора символов Unicode, в частности, первые 65 536 кодовых точек (или Базовая многоязычная плоскость (BMP)).

Для получения дополнительной информации о сопоставлении в различных СУБД см. Мой ответ на следующий вопрос о DBA.StackExchange:

Есть ли в какой-либо СУБД параметры сортировки, которые чувствительны к регистру и не чувствительны к акценту?

---

ОБНОВЛЕНИЕ 2018-10-02

Хотя этот вариант пока не подходит, в SQL Server 2019 появилась встроенная поддержка UTF-8 в VARCHAR / CHAR типы данных. В настоящее время в нем слишком много ошибок, чтобы его можно было использовать, но если они исправлены, то это вариант для некоторых сценариев. Пожалуйста, ознакомьтесь с моим постом " Поддержка UTF-8 в SQL Server 2019: Спаситель или Лжепророк?" Для подробного анализа этой новой функции.