Что делает COLLATE SQL_Latin1_General_CP1_CI_AS?

У меня есть SQL-запрос для создания базы данных в SQLServer, как показано ниже:

create database yourdb
on
( name = 'yourdb_dat',
  filename = 'c:\program files\microsoft sql server\mssql.1\mssql\data\yourdbdat.mdf',
  size = 25mb,
  maxsize = 1500mb,
  filegrowth = 10mb )
log on
( name = 'yourdb_log',
  filename = 'c:\program files\microsoft sql server\mssql.1\mssql\data\yourdblog.ldf',
  size = 7mb,
  maxsize = 375mb,
  filegrowth = 10mb )
COLLATE SQL_Latin1_General_CP1_CI_AS;
go

Он работает нормально.

В то время как остальная часть SQL ясна, я довольно запутался в функциональности COLLATE SQL_Latin1_General_CP1_CI_AS.

Может кто-нибудь объяснить это мне? Кроме того, я хотел бы знать, является ли создание базы данных таким образом наилучшей практикой?

Ответы

Ответ 1

Он устанавливает, как сервер базы данных сортирует (сравнивает фрагменты текста). в этом случае:

SQL_Latin1_General_CP1_CI_AS

разбивается на интересные части:

  1. latin1 заставляет сервер обрабатывать строки, используя charset latin 1, в основном ascii
  2. CP1 обозначает кодовую страницу 1252
  3. CI сравнения без учета регистра, поэтому "ABC" будет равно "abc"
  4. AS чувствителен к акценту, поэтому 'ü' не равно 'u'

P.S. Для получения более подробной информации обязательно прочитайте ответ @solomon-rutzky.

Ответ 2

Помните, что принятый ответ немного неполон. Да, на самом базовом уровне Collation обрабатывает сортировку. НО, правила сравнения, определенные выбранным сопоставлением, используются во многих местах вне пользовательских запросов к пользовательским данным.

Если "Что делает COLLATE SQL_Latin1_General_CP1_CI_AS?" означает "Что делает предложение COLLATE в CREATE DATABASE?", то:

Предложение COLLATE {collation_name} оператора CREATE DATABASE определяет сортировку базы данных по умолчанию, а не сервера; Параметры сортировки по умолчанию для базы данных -level и сервера -level контролируют разные вещи.

Сервер (т.е. экземпляр) -level контролирует:

  • База данных -level Сортировка для системных баз данных: master, model, msdb и tempdb.
  • Благодаря контролю параметров сортировки БД -level для tempdb, в этом случае используется параметр сортировки по умолчанию для строковых столбцов во временных таблицах (глобальных и локальных), но не в переменных таблицы.
  • Благодаря контролю параметров сортировки БД -level для master, в таком случае они используются для данных сервера -level, таких как имена баз данных (т.е. столбец name в sys.databases), имена входа и т.д.
  • Обработка имен параметров/переменных
  • Обработка имен курсоров
  • Обработка ярлыков GOTO
  • Сортировка по умолчанию, используемая для вновь создаваемых баз данных, когда отсутствует условие COLLATE

База данных -level контролирует:

  • Параметры сортировки по умолчанию, используемые для вновь созданных строковых столбцов (CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT и NTEXT - но не используйте TEXT или NTEXT), когда Предложение COLLATE отсутствует в определении столбца. Это относится как к операторам CREATE TABLE, так и к ALTER TABLE ... ADD.
  • Сортировка по умолчанию, используемая для строковых литералов (т.е. 'some text') и строковых переменных (т.е. @StringVariable). Это сопоставление всегда используется только при сравнении строк и переменных с другими строками и переменными. При сравнении строк/переменных со столбцами будет использоваться сопоставление столбца.
  • Сортировка, используемая для метаданных базы данных -level, таких как имена объектов (то есть sys.objects), имена столбцов (то есть sys.columns), имена индексов (то есть sys.indexes) и т.д.
  • Параметры сортировки, используемые для объектов базы данных -level: таблиц, столбцов, индексов и т.д.

Также:

  • ASCII - это 8-битная кодировка (для обычного использования; технически ASCII - 7-битная с символьными значениями 0–127, а "ASCII Extended" - 8-битная с символьными значениями 0–255). Эта группа одинакова для разных культур.
  • Кодовая страница является "расширенной" частью Extended ASCII и контролирует, какие символы используются для значений 128 - 255. Эта группа варьируется в зависимости от культуры.
  • Latin1 не означает "ASCII", поскольку стандартный ASCII охватывает только значения 0–127, и все кодовые страницы (которые могут быть представлены в SQL Server и даже NVARCHAR) отображают те же 128 значений на одни и те же символы.

Если "Что делает COLLATE SQL_Latin1_General_CP1_CI_AS?" означает "Что делает этот конкретный сопоставление?", то:

  • Поскольку имя начинается с SQL_, это сопоставление SQL Server, а не сопоставление Windows. Они определенно устарели, даже если официально не устарели, и в основном для совместимости до SQL Server 2000. Хотя, к сожалению, SQL_Latin1_General_CP1_CI_AS очень распространен из-за того, что он используется по умолчанию при установке на ОС с использованием американского языка в качестве языка. Эти сопоставления следует избегать, если это вообще возможно.

    Параметры сортировки Windows (имена которых не начинаются с SQL_) являются более новыми, более функциональными, имеют согласованную сортировку между VARCHAR и NVARCHAR для одинаковых значений и обновляются с помощью дополнительных/исправленных весов сортировки и сопоставлений в верхнем и нижнем регистре., Эти сопоставления также не имеют потенциальной проблемы с производительностью, которую имеют сопоставления SQL Server: Влияние на индексы при смешивании типов VARCHAR и NVARCHAR.

  • Latin1_General - это культура/локаль.
    • Для данных NCHAR, NVARCHAR и NTEXT это определяет лингвистические правила, используемые для сортировки и сравнения.
    • Для данных CHAR, VARCHAR и TEXT (столбцы, литералы и переменные) это определяет:
      • лингвистические правила, используемые для сортировки и сравнения.
      • кодовая страница, используемая для кодирования символов. Например, параметры сортировки Latin1_General используют кодовую страницу 1252, параметры сортировки Hebrew используют кодовую страницу 1255 и т.д.

  • CP{code_page} или {version}

    • Для параметров сортировки SQL Server: CP{code_page} - это 8-битная кодовая страница, которая определяет, какие символы соответствуют значениям 128 - 255. В то время как есть четыре кодовых страницы для двухбайтовых наборов символов (DBCS), которые могут использовать 2-байтовые комбинации для создать более 256 символов, они не доступны для параметров сортировки SQL Server.

    • Для сопоставлений Windows: {version}, хотя и присутствует не во всех именах сопоставлений, относится к версии SQL Server, в которой было введено сопоставление (по большей части). Параметры сортировки Windows без номера версии в имени - версия 80 (имеется в виду SQL Server 2000, то есть версия 8.0). Не все версии SQL Server поставляются с новыми параметрами сортировки, поэтому в номерах версий есть пробелы. Некоторые из них - 90 (для SQL Server 2005, версия 9.0), большинство - 100 (для SQL Server 2008, версия 10.0), а небольшой набор имеет 140 (для SQL Server 2017, версия 14,0).

      Я сказал "по большей части", потому что параметры сортировки, заканчивающиеся на _SC, были введены в SQL Server 2012 (версия 11.0), но базовые данные не были новыми, они просто добавили поддержку дополнительных символов для встроенных функций. Таким образом, эти окончания существуют для сопоставлений версий 90 и 100, но только начиная с SQL Server 2012.

  • Далее у вас есть чувствительность, которая может быть в любой комбинации из следующих, но всегда указывается в следующем порядке:
    • CS = с учетом регистра или CI = без учета регистра
    • AS = чувствительный к акценту или AI = нечувствительный к акценту
    • KS = Kana чувствительна к типу или отсутствует = Kana нечувствительна к типу
    • WS = чувствительный к ширине или отсутствующий = нечувствительный к ширине
    • VSS = чувствительно к селектору вариаций (доступно только в сопоставлениях версии 140) или отсутствует = нечувствительно к селектору вариаций

  • Необязательный последний кусок:

    • _SC в конце означает "Поддержка дополнительных символов". "Поддержка" влияет только на то, как встроенные функции интерпретируют суррогатные пары (как кодируются дополнительные символы в UTF-16). Без _SC в конце (или _140_ в середине) встроенные функции не видят ни одного дополнительного символа, а вместо этого видят две бессмысленные кодовые точки, которые составляют суррогатную пару. Это окончание может быть добавлено в любой двоичный файл non-, версия 90 или 100.
    • _BIN или _BIN2 в конце означает "двоичную" сортировку и сравнение. Данные по-прежнему хранятся так же, но нет лингвистических правил. Это окончание никогда не сочетается ни с одной из 5 чувствительности или _SC. _BIN - более старый стиль, а _BIN2 - более новый, более точный стиль. Если используется SQL Server 2005 или новее, используйте _BIN2. Подробнее о различиях между _BIN и _BIN2 см. в разделе Различия между различными двоичными сопоставлениями (культуры, версии и BIN против BIN2).

    • _UTF8 является новой опцией в SQL Server 2019. Это 8-битная кодировка, которая позволяет хранить данные Unicode в типах данных VARCHAR и CHAR (но не в устаревшем типе данных TEXT). Эта опция может использоваться только для сопоставлений, которые поддерживают дополнительные символы (то есть сопоставления версии 90 или 100 с _SC в их имени и сопоставления версии 140). Существует также одно двоичное сопоставление _UTF8 (_BIN2, а не _BIN).

      ПОЖАЛУЙСТА, ОБРАТИТЕ ВНИМАНИЕ: UTF-8 был разработан/создан для совместимости со средами/кодом, которые настроены для 8-битных кодировок, но хотят поддерживать Unicode. Несмотря на то, что есть несколько сценариев, в которых UTF-8 может обеспечить до 50% экономии пространства по сравнению с NVARCHAR, это является побочным эффектом и приводит к незначительному снижению производительности во многих/большинстве операций. Если вам это нужно для совместимости, то стоимость приемлема. Если вы хотите это для экономии места, вам лучше пройти тест и снова протестировать. Тестирование включает в себя все функциональные возможности и не только несколько строк данных. Имейте в виду, что параметры сортировки UTF-8 работают лучше всего, когда ВСЕ столбцы и сама база данных используют данные VARCHAR (столбцы, переменные, строковые литералы) с сопоставлением _UTF8. Это естественное состояние для всех, кто использует это для совместимости, но не для тех, кто надеется использовать его для экономии места. Будьте осторожны при смешивании данных VARCHAR с использованием параметров сортировки _UTF8 с данными VARCHAR с использованием параметров сортировки non- _UTF8 или данных NVARCHAR, так как вы можете столкнуться со странным поведением/потерей данных. Дополнительные сведения о новых сопоставлениях UTF-8 см. в разделе: Собственная поддержка UTF-8 в SQL Server 2019: Спаситель или Лжепророк?

Ответ 3

CP1 означает "Code Page 1" - технически это переводится на кодовую страницу 1252

Ответ 4

Ключевое слово COLLATE определяет, какой набор символов и правила (порядок, конфронтационные правила) используются для строковых значений.

Например, в вашем случае вы используете латинские правила с нечувствительным к регистру (CI) и чувствительным к акценту ( AS)

Вы можете обратиться к этой Документации

Ответ 5

Указывает сопоставление по умолчанию для базы данных. Каждое текстовое поле, которое вы создаете в таблицах в базе данных, будет использовать эту сортировку, если вы не укажете другую.

База данных всегда имеет сортировку по умолчанию. Если вы не указали какие-либо значения, используется сортировка по умолчанию экземпляра SQL Server.

В названии используемой сопоставления показано, что он использует кодовую страницу Latin1, не чувствителен к регистру (CI) и чувствителен к акценту (AS). Эта сортировка используется в США, поэтому она будет содержать правила сортировки, которые используются в США.

Количественное решение решает, как сравниваются текстовые значения для равенства и подобия, и как они сравниваются при сортировке. Кодовая страница используется при хранении данных, отличных от юникода, например. varchar.