Что делает 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.

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


database sql-server tsql collation
person Thunder    schedule 18.02.2011    source источник

Ответы (5)


arrow_upward
277
arrow_downward

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

SQL_Latin1_General_CP1_CI_AS

распадается на интересные части:

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

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

person Kris    schedule 18.02.2011
comment
Какая разница между этим и SQL_Latin1_General_CI_AS. В частности, меня заинтересовал CP1. - person Kad; 21.01.2014
comment
@Kad: Кажется, нет SQL_Latin1_General_CI_AS. Скорее, есть Latin1_General_CI_AS. См. SELECT * FROM fn_helpcollations() where name IN ('SQL_Latin1_General_CP1_CI_AS','Latin1_General_CI_AS','SQL_Latin1_General_CI_AS');. Существуют тонкие различия в отношении сортировки и сравнения между двумя сопоставлениями. См. olcot.co.uk/sql -блоги/. - person Riley Major; 21.04.2014
comment
@Kad: CP1 означает кодовую страницу 1252. Кодовая страница — это таблица поиска для сопоставления шестнадцатеричного значения с определенным символом в наборе символов. CP1 — это сокращение от CP1252 в субкультуре Microsoft. Windows — единственная платформа, которая изначально использует CP1252, так как это пережиток времен DOS. Хотя он очень похож на ISO 8859-1, это не одно и то же. Существуют различия в отображаемых символах, таких как евро и некоторые другие, которых нет в ISO 8859-1. - person slartibartfast; 04.02.2017
comment
безупречный ответ @Kris! - person Gaurav; 04.03.2019
comment
@Kris Есть ли альтернатива UTF-8 для SQL_Latin1_General_CP1_CI_AS в SQL2019? - person Chanky Mallick; 18.04.2020
comment
@RileyMajor архивировать ссылку, поскольку оригинал не работает - person Arno Hilke; 25.05.2021
comment
@Kad Как упомянула Райли, такого сопоставления не существует. Имя представляет собой сочетание двух типов сопоставлений: 1) сопоставления SQL Server, имена которых начинаются с SQL_ и включают номер кодовой страницы в имя (например, CP1, CP1255). В них используются старые правила сортировки/сравнения, отличные от Unicode, для VARCHAR данных. 2) Параметры сортировки Windows не имеют в своем имени ни SQL_, ни номера CP. Они используют правила сортировки/сравнения Unicode для VARCHAR данных. См.: sqlquantumleap.com/2019/11/22/ и collations.info - person Solomon Rutzky; 07.06.2021
comment
@slartibartfast Я только что опубликовал статью о том, что CP1 на самом деле является ошибкой, которую не обнаружили достаточно рано, чтобы исправить, как в HTTP-заголовке с ошибкой referer. Это означает ISO-8859-1, хотя эта кодовая страница нигде в SQL Server не поддерживается, но кто-то изначально думал, что это синоним Windows-1252. Для всех, кому интересно, этот пост: Что означает «CP1» в «SQL_Latin1_General_CP1_CI_AS»?. Кроме того, 1252 не из дней DOS, это будут 437 и 850. - person Solomon Rutzky; 07.06.2021
comment
@Chanky В зависимости от того, что именно вы подразумеваете под альтернативой, вы, вероятно, ищете сопоставление Latin1_General_100_CI_AS_SC_UTF8. Также см. мой пост о сопоставлении UTF-8: Встроенная поддержка UTF-8 в SQL Server 2019: спаситель или лжепророк?. - person Solomon Rutzky; 07.06.2021
comment
@SolomonRutzky Да, чувак, прошел 1 год, мы пытались с помощью сопоставления SQL2019 utf-8 заставить приложение поддерживать языки, отличные от английского, без изменения существующих столбцов varchar, но это было невозможно, ограничивать приложение только определенной версией 2019 года было плохой идеей, потом мы все это дело конвертировали в NVARCHAR, это было жестко по сравнению с utf-8, но достойно. - person Chanky Mallick; 01.07.2021

arrow_upward
97
arrow_downward

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

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

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

Уровень сервера (т. е. экземпляра) управляет:

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

Элементы управления на уровне базы данных:

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

Также:

  • ASCII — это 8-битная кодировка (для общего использования; технически «ASCII» — 7-битная со значениями символов от 0 до 127, а «ASCII Extended» — 8-битная со значениями символов от 0 до 255). Эта группа одинакова в разных культурах.
  • Кодовая страница является «расширенной» частью расширенного 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: -nvarchar-types/" rel="noreferrer">Влияние на индексы при смешивании типов VARCHAR и NVARCHAR.

  • Latin1_General is the culture / locale.
    • For NCHAR, NVARCHAR, and NTEXT data this determines the linguistic rules used for sorting and comparison.
    • For CHAR, VARCHAR, and TEXT data (columns, literals, and variables) this determines the:
      • linguistic rules used for sorting and comparison.
      • кодовая страница, используемая для кодирования символов. Например, параметры сортировки Latin1_General используют кодовую страницу 1252, параметры сортировки Hebrew используют кодовую страницу 1255 и т. д.

  • CP{code_page} or {version}

    • For SQL Server collations: CP{code_page}, is the 8-bit code page that determines what characters map to values 128 - 255. While there are four code pages for Double-Byte Character Sets (DBCS) that can use 2-byte combinations to create more than 256 characters, these are not available for the SQL Server collations.

    • Для параметров сортировки 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.

  • Next you have the sensitivities, that can be in any combination of the following, but always specified in this order:
    • CS = case-sensitive or CI = case-insensitive
    • AS = с учетом акцента или AI = без учета акцента
    • KS = кана чувствительна к типу или отсутствует = кана нечувствительна к типу
    • WS = чувствителен к ширине или отсутствует = нечувствителен к ширине
    • VSS = селектор вариантов чувствителен (доступно только в сопоставлениях версии 140) или отсутствует = селектор вариантов нечувствителен

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

    • _SC at the end means "Supplementary Character support". The "support" only affects how the built-in functions interpret surrogate pairs (which are how supplementary characters are encoded in UTF-16). Without _SC at the end (or _140_ in the middle), built-in functions don't see a single supplementary character, but instead see two meaningless code points that make up the surrogate pair. This ending can be added to any non-binary, version 90 or 100 collation.
    • _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, использующими сопоставление без _UTF8, либо с данными NVARCHAR, так как вы можете столкнуться со странным поведением или потерей данных. Дополнительные сведения о новых сопоставлениях UTF-8 см. по адресу: Встроенная поддержка UTF-8 в SQL Server 2019: спаситель или лжепророк?

person Solomon Rutzky    schedule 01.12.2016
comment
Хотя я проголосовал за то, что он содержит так много информации и усилий, мой ответ определенно не является неправильным (базы данных хранят данные, серверы баз данных обрабатывают эти данные, действует сортировка). Я предпочел краткость полной математической точности, потому что ОП, вероятно, искал достаточно, а не всю возможную информацию. - person Kris; 14.09.2017
comment
Привет @Крис. Спасибо. Честно говоря, я не сказал, что ваш ответ был совершенно неправильным, просто ужасно неполным. Я обновил, чтобы, надеюсь, прояснить это. Я понимаю, о чем вы говорите, но ОП спросил, что делает пункт COLLATE в CREATE DATABASE. Вы сказали одну из нескольких вещей, которые он делает. Почему вы предполагаете, что ОП хочет знать только 10% ответа? Если вся информация представлена, каждый человек может решить, сколько из нее взять. Но если дается только какая-то информация, то выбор сделан за них. Я предпочитаю предоставлять как можно больше информации, потому что большая ее часть малоизвестна. (продолжение) - person Solomon Rutzky; 15.09.2017
comment
Когда дело доходит до сортировки (и кодирования), большая часть того, что там есть, либо неполна, либо неверна. Поэтому большинство людей уходят, не зная достаточно или думая, что они что-то знают, но будучи совершенно неправы. Люди принимают лучшие решения, когда у них есть вся информация, поэтому я считаю, что лучше всего дать как можно более полный ответ. Выбирая краткость, вы потенциально можете сбить читателей с толку, когда они получат ошибки синтаксического анализа и т. Д. В БД с регистрозависимой или двоичной сортировкой, поскольку разрешение имен не упоминалось. Итак, хотя вы правы насчет сортировки, я чувствую, что она сама по себе вводит в заблуждение. - person Solomon Rutzky; 15.09.2017
comment
Я думаю, что понимаю, что вы имеете в виду, но я стремлюсь дать достаточно информации, а не слишком много. слишком много информации быстро становится слишком сложным для многих людей. и когда я не могу предоставить достаточно информации для каких-либо обстоятельств, я ожидаю дополнительных вопросов. (я тоже не ожидал такого внимания к теме) - person Kris; 16.09.2017
comment
@Kris Я давно хотел сказать спасибо! за проявление такой зрелости и профессионализма. Я несколько привык к тому, что люди обижаются на кого-то, кто говорит, что они не правы, а потом с ними становится трудно (или даже еще труднее) взаимодействовать. Но ваш сдержанный ответ на мой принятый ответ НЕВЕРНЫЙ вдохновил меня смягчить вступление и должен послужить примером для других, как правильно общаться и продуктивно ????. - person Solomon Rutzky; 14.08.2018
comment
Пожалуйста, и приятно слышать, что я каким-то образом оказал положительное влияние, но мне нравится ошибаться, это открывает возможности для изучения новых вещей, и это здорово! - person Kris; 14.08.2018

arrow_upward
24
arrow_downward

CP1 означает «Кодовая страница 1» — технически это означает кодовую страницу 1252.

person Chris Halcrow    schedule 29.04.2013

arrow_upward
16
arrow_downward

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

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

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

person il_guru    schedule 18.02.2011

arrow_upward
9
arrow_downward

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

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

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

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

person Guffa    schedule 18.02.2011
comment
неправильно (вы не можете not указать сопоставление, хотя вы можете принять значение по умолчанию) неправильно (это также используется для данных Unicode) - person RichardTheKiwi; 18.02.2011
comment
@Richard aka Cyberkiwi: проверьте документацию: msdn.microsoft.com/en-us /library/ms176061.aspx Указание параметров сортировки является необязательным. Кодовая страница не используется для хранения данных Unicode, поскольку они хранятся в виде 16-битных кодовых точек Unicode, а не в виде индексов 8-битных кодовых страниц. - person Guffa; 18.02.2011
comment
Я неправильно прочитал ваш ответ, но он все еще неверен. База данных всегда имеет сопоставление по умолчанию = сопоставление SERVER, а не конкретно Latin1_General_CI_AS. Теперь я прочитал это неправильно, потому что я наполовину ожидал, что утверждение будет о сопоставлении SERVER, которое действительно требует принятия значения по умолчанию в пользовательском интерфейсе. Что касается 2-го пункта, вы, кажется, подразумеваете, что сопоставление не используется для сортировки данных Unicode (даже если вы переключаетесь с sorting на storing в последних двух предложениях). Текстовые данные Unicode также подчиняются параметрам сортировки. - person RichardTheKiwi; 18.02.2011
comment
@Richard aka cyberkiwi: я изменил абзац о сопоставлении по умолчанию, чтобы он соответствовал конкретной документации, на которую я ссылался. (Различается в зависимости от версии сервера.) Что касается второго пункта, то я не вижу, как его можно было бы прояснить. В тексте говорится, что кодовая страница используется при хранении данных, отличных от Unicode. Кодовая страница не используется для определения сортировки ни для данных Unicode, ни для данных не Unicode. - person Guffa; 18.02.2011