Header RSS-подписка на обновления сайта eMail-подписка на обновления сайта

Понимаете ли вы коллейшены? Часть 3/8.

















Коллейшен уровня отдельного запроса.

Наконец, мы достигаем «дна» иерархии коллейшенов и выходим на финальный отрезок и уровень отдельного запроса. Для такого варианта назначения коллейшена студия не может предложить ничего (кроме своего редактора, разумеется), а поэтому мы сразу переходим к работе с кодом. Как вы уже заметили, при работе с персональными коллейшенами баз и/или колонок вся хитрость заключалась в написании ключевого слова COLLATE и имени любого валидного коллейшена после него. Совершенно тот же подход возможен и для любого выражения символьной строки. Под символьной строкой следует понимать буквально все, содержащее строковые данные: колонки тех 6-ти типов о которых мы говорили в начале статьи, константы/литералы тех же типов, переменные, функции возвращающие «подходящие» значения (точнее — типы этих значений)... В общем везде где в нашем T-SQL коде строка видна явна или подразумевается исходя из типа объекта мы можем смело писать ...COLLATE [collation_name]. Вот вам пара-другая синтаксически корректных примеров:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
DECLARE @x varchar(20), @y varchar(10)
SELECT 1 WHERE @x COLLATE [collation_name1] + @y COLLATE [collation_name2] = 'sqlCMD'
--
SELECT 1
WHERE SESSION_USER COLLATE [collation_name1] + HOST_NAME() COLLATE [collation_name2] ='sqlCMD'
--
SELECT * FROM T1
WHERE Name = 'SqLcMd' COLLATE [collation_name];
--
SELECT * FROM T1 AS t1 INNER JOIN T2 AS t2
ON t1.Name=t2.Name COLLATE [collation_name];
--
SELECT * FROM T1
WHERE Name='sqlCMD' COLLATE [collation_name1] AND EmailAddress='SqLcMd' COLLATE [collation_name2]
--
SELECT * FROM T1
WHERE (Name COLLATE [collation_name1])='sqlCMD' AND EmailAddress='SqLcMd' COLLATE [collation_name2]
--
SELECT * FROM T1
WHERE Name COLLATE [collation_name1]='sqlCMD' COLLATE [collation_name2] AND EmailAddress='SqLcMd' COLLATE [collation_name3]
--
SELECT * FROM T1
ORDER BY Name COLLATE [collation_name]
--
SELECT * FROM T1
ORDER BY Name COLLATE [collation_name1], EmailAddress COLLATE [collation_name2]

Однако, обратите внимание, автор не зря выделил слово синтаксически говоря о корректности приведенных примеров. Он вовсе не утверждает, что после подстановки валидных имен коллейшенов все они прямо так успешно выполнятся, отнюдь! Все зависит от того какие именно значения получат все эти [collation_name1] и [collation_name2]. В зависимости от этого команды могут быть успешны, а могут и нет. Это все оттого, что SQL Server имеет строгие (и довольно запутанные) правила разбора сочетаний двух (и более) коллейшенов участвующих в одном T-SQL выражении. К правилам этим мы сейчас перейдем, но сначала пару примеров не являющихся корректными даже с синтаксической точки зрения:

1
2
3
4
5
6
--COLLATE нужно применять к СТРОКОВОМУ ВЫРАЖЕНИЮ, а не к результату их сравнения
SELECT * FROM T1
WHERE (Name='SqLcMd') COLLATE [collation_name1]
--выражение может иметь 1 коллейшен максимум (и минимум тоже, заметьте)
SELECT * FROM T1
WHERE Name='SqLcMd' COLLATE [collation_name1],[collation_name2]

Вот это — совершенно точно неверно по причинам указанным в комментариях к каждому отрывку. Здесь конкретные значения коллейшенов уже не играют никакой роли.

Очередность и приоритет коллейшенов.

Так вот, если у нас в T-SQL команде всего один коллейшен (не важно, указан он явно или «выведен» из опции столбца/базы) — команда выполняется по его правилам и дело с концом. Но, как легко себе представить, в изрядном количестве команд (если рассматривать их полностью, а не по клаузулам) коллейшенов будет больше чем 1, иногда значительно больше. Плюс к тому, еще эти явные/подразумеваемые коллейшены... В общем, винегрет получается еще тот. При этом каждое обособленное строковое выражение должно иметь четко один (не больше! но и не меньше) коллейшен. Что такое обособленное выражение? Вот вам пример:

1
SELECT * FROM T1 WHERE A>B AND C>D OR E=F

Здесь буквы A-F клаузулы WHERE обозначают что угодно (константы, колонки, функции, ...) лишь бы это «что-то» давало на выходе именно строку. Так вот, давайте мыслить просто и логично: может ли сущность A обладать одним коллейшеном, а B — другой? Нет, очевидно. Как вы себе это представляете? Одно правило говорит что сначала идет буква «ю», потом «я», а другое — что наоборот? Движку, понятно, все-равно, как оно на самом деле, но не может же он одновременно следовать обоим правилам! Либо слово из колонки A впереди, либо из B. Аналогично и в парах C-D, E-F. Ну а вот если A-B сравниваются по правилу «ю впереди», а пара E-F по правилу «я впереди»? Вот так можно? Ну а почему нет-то! Правила не пересекаются и друг-друга не отрицают, вполне себе можно. Ну а если бы у нас в последней части клаузулы фигурировала бы не колонка F, а снова A (E=A)? Возможно что бы A с B сравнивались по одним правилам, а A-E — по другим? И что бы все это происходило в рамках одной команды записанной на языке T-SQL? Снова, ничего сложного: рассуждайте исходя из посыла «вижу ли я в таком раскладе непреодолимые логические противоречия?» и вы получите правильный ответ. Ухватили смысл термина обособленное в его приложении к сочетанию коллейшенов? Тогда двигаемся дальше.

Теперь, наверно, самая «неприятная» часть: именно обособленное выражение имеет 2 коллейшена-претендента. Что делать нам и, даже важнее, что делать движку? Правила на этот счет есть, они совершенно строги, но назвать их «ясными как день» никак не получится, как я и предупреждал они несколько... гм-м... «тяжеловаты» для восприятия. Возьмем для пояснения обособленное выражение состоящее из 2-х компонентов, A и B. Для начала выяснения вопроса «кто здесь главный» каждое из этих выражений получает т.н. метку параметров сортировки. Этот официальный термин мне совершенно не нравится т.к. только усугубляет запутанность ситуации, но, что бы не плодить уже и без того многочисленные сущности, я ничего своего выдумывать не буду, а просто сокращу этот термин до простой метки. Итак, каждый компонент в начале приобретает одну из 3-х меток. На примере компонента A:

  • А получит метку умолчание (обозначу ее А-умолчание) если А не колонка и не имеет явно назначенного коллейшена. Т.е. такую метку получают все константы, переменные, строки на «выходе» функций и т.д. которым мы не указали коллейшен с помощью ключевого слова COLLATE как это, к примеру, имело место быть в предыдущем разделе. Значением же такой метки станет коллейшен той базы в контексте которой происходит работа с A;
  • А получит метку явно (обозначу ее А-явно) если А имеет явно назначенный коллейшен с помощью ключевого слова COLLATE в тексте запроса (и только там!). Иными словами назначенный коллейшен должен быть самого низкого «ранга» — на уровне отдельного запроса. Будет ли A колонкой, или переменной, или еще чем в данном случае безразлично. Значением метки, разумеется, станет именно тот коллейшен что указан вслед за COLLATE. За примерами снова обратитесь к предыдущему разделу;
  • А получит метку неявно (обозначу ее А-неявно) если А является колонкой и только колонкой и не имеет явно назначенного в выражении коллейшена. Вот тут нюанс, так нюанс. Нюансище, я бы сказал! Итак, исходя что A — колонка и только колонка: метка А-умолчание отметается как невозможная в принципе; метка А-явно будет ей дана только если в выражении (и только там!) где A фигурирует указано ключевое слово COLLATE. Например, ...WHERE A COLLATE [collation_name]='sqlCMD'. Вот это — «зачет», метка А-явно получена. Во всех остальных случаях, в т.ч. если при создании таблицы которой принадлежит A в определении этой колонки фигурировало тоже самое ключевое слово COLLATE — не «зачет», метка будет А-неявно. Еще раз, для этой метки COLLATE должно фигурировать в выражении где упоминается A, не в команде которой эта колонка была создана. Разумеется если оно применяется и там, и там — нет проблем, тоже А-явно. Значением метки становится коллейшен колонки.

Еще раз, обратите внимание: есть тип метки. Их всего три и все их только что мы рассмотрели. А есть значение метки. Это любое валидное имя коллейшена, рассказ о которых у нас впереди. Метка всегда и без исключения обладает типом, и почти всегда (за одним исключением — метка типа X, рассматриваемая ниже) — значением.

После этого те же рассуждения применяются к B. Для него тоже выводится тип метки (обязательно) и ее значение (когда это возможно). Итого мы имеем 3x3=9 возможных сочетаний этих типов меток от двух компонентов одного выражения. Что является результатом слияния 2-х меток? Да снова метка, разумеется! Тип и значение она получит от «победившей» в сравнении метки. Ведь выражение может быть более чем 2-х компонентное, к примеру A+B>C. Тогда нам нужно просчитать «промежуточно-итоговую» метку для суммы двух строк что бы с нею повторить процедуру для просчета «гранд-финальной» метки всего выражения, под «управлением» которой оно и будет исполнено. Итого, наша финальная цель — оставить одного «управляющего» из набора претендентов. Помните, я говорил что каждое обособленное строковое выражение должно иметь четко один (не больше, но и не меньше) коллейшен? Вот все наши усилия на то и направлены. Итак, вот какие результирующие типы и значения меток будут иметь различные сочетание A и B:

  А-умолчание А-неявно А-явно
B-умолчание А/B-умолчание А-неявно А-явно
B-неявно B-неявно X А-явно
B-явно B-явно B-явно Error

Какие тонкости надо иметь в виду при чтении данной таблицы?

  • записи в таблице выше нужно понимать так (на примере пересечения исходных меток А-умолчание+B-неявно): указанное сочетание меток даст итоговую метку с типом неявно и со значением взятым как коллейшен для части B анализируемого выражения, что и записывается как B-неявно на пересечении двух исходных меток; однако — есть два особых случая, X и Error, о них ниже;
  • если метки (читай — коллейшены) A и B совпадают (тут как раз имеются в виду не типы меток, а их значения, т.е. имена коллейшенов) — никакие таблицы не нужны вообще. Значение финальной метки равно общему коллейшену, не важно как A и B получили свои исходные метки (иными словами, тип метки роли не играет). А вот типом этой метки будет тип наиболее «весомый» из двух (о «весе» меток — см. далее);
  • две метки умолчания дают в слиянии всегда ту же самую метку, и по типу, и по значению — без вариантов. Это потому, что они совпадают изначально;
  • Error в таблице выше — это самая натуральная ошибка, т.е. выполнение команды прекращается и студия «выплевывает» нам красные строчки с ее расшифровкой. Однако! Не забудьте учесть второй пункт этих примечаний, вполне возможно что дело обойдется и без ошибок;
  • X в таблице выше обозначает собой особый случай — отсутствующую метку. Иными словами итоговый коллейшен такого выражения не известен (тот самый особый случай упомянутый ранее). Если вычисление всего выражения продолжается, т.е. теперь у нас с одной стороны эта особая метка X а с другой одна из 3-х возможных меток колонки C и требуется просчитать метку их слияния, то единственный вариант который может «перекрыть» XC-явно. В этом и только в этом случае финальная метка будет равна последней. При метках C-умолчание/C-неявно финальная метка вновь будет X. В этом случае поведение всей команды в которую входит обсуждаемое многокомпонентное выражение, чья метка просчитана по итогу в метку X, зависит от типа самой этой команды. В большинстве случаев мы опять будем получать ошибку выполнения такой команды. Однако некоторые команды будут при этом выполняться без каких либо проблем. Это потому, что все команды (даже более технически корректно будет сказать «операторы и функции», но в рамках этого абзаца я буду сокращать их до «команды») делятся на «с учетом X» и «без учета X». Первых намного, намного больше: любой оператор сравнения строк, ключевые слова LIKE, IN, команда SELECT... Все они откажутся работать с выражением с меткой X и вернут ошибку. А «без учета X» команд намного меньше. Собственно, автору известны только: = (присваивание, а не сравнение!), + (конкатенация строк), CASE да UNION ALL — это, пожалуй, и все. С совсем небольшим преувеличением можно сказать, что метка X — гарантированная ошибка выполнения;
  • «генеральная» концепция: метка-умолчание < метка-неявно < метка-явно. Эта формула почти точно отражает приведенную ранее таблицу без учета особых случаев. Так же она является формулой «веса» типа метки о котором автор упоминал во втором пункте этих примечаний. Финальная метка всегда берет наиболее «весомый» тип из двух возможных;
  • приведение типов самих операндов и приведение типов их меток — абсолютно самостоятельные процессы, никак не влияющие друг на друга. Более чем вероятно, что итоговым типом выражения A+B станет тип операнда A, в то время как тип и значение метки того же выражения будут взяты от B.

Вот и все, несложно же? :roll: Хорошо, пара-другая примеров как применять полученные знания.

1
2
DECLARE @x varchar(20)
SELECT @x+HOST_NAME()

Согласно 1-му пункту списка типов меток и переменная @x (компонент A в наших теоретических выкладках), и функция HOST_NAME() (компонент B) получают метки-умолчания. Значения меток, согласно тому же пункту — коллейшен базы в которой происходит выполнение инструкций. Согласно 2-му пункту примечаний таблица сочетаний в данном случае не требуется, итоговая метка выражения имеет тот же тип и значение. Вся инструкция выполняется без проблем.

1
2
DECLARE @x varchar(20), @z varchar(10)
SET @z='nnn' COLLATE [collation_name] + @x

Снова, согласно тому же 1-му пункту списка типов меток переменная @x получает метку B-умолчание, а литерал 'nnn' получает метку А-явно. Согласно таблице сочетаний все выражение в целом имеет метку-явно со значением [collation_name]. Выполнение, опять же, проблем не вызывает.

1
2
3
4
5
6
CREATE TABLE T1 (
   Col1 varchar(20) collate [collation_name1],
   Col2 varchar(30) collate [collation_name2],
)
GO
SELECT * FROM T1 WHERE Col1=Col2

Здесь уже, согласно 3-му пункту списка типов меток обе колонки получают метки-неявно, однако значения этих меток отличаются (т.к. имена коллейшенов двух колонок различны). Отсюда, п.2 примечаний неприменим и мы смотрим в таблицу. На этот раз итоговая метка — X. Оператор сравнения не относится к операторам «без учета X» и выполнение всей команды завершается сообщением:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "[collation_name2]" and "[collation_name1]" in the equal to operation.
Все ли я правильно понял в отношении очередности и приоритета коллейшенов?

Ну, скажем так — автор в вас верит! :) Что бы и вы поверили в себя он приготовил для вас небольшие тест-вопросы. В контексте всего этого раздела-«викторины» [collation_nameN] означает любое, но валидное значение коллейшена. Коллейшены с совпадающей цифрой N — совпадают ([collation_name3]=[collation_name3]), с отличающейся — различны ([collation_name1]<>[collation_name3]). Для всех отрывков кода вопросы идентичны:

  1. При запуске указанного отрывка будет ошибка или выполнение завершится успешно?
  2. Если успешно — каков будет финальный тип метки (типы, если команда состоит из нескольких обособленных выражений) и ее значение (значения)?
  3. Если выполнение завершается с ошибкой — сочетание каких типов меток к ней приведет?

Исходная тестовая таблица всех вопросов также идентична:

1
2
3
4
5
CREATE TABLE T1 (
   Col1 varchar(10) collate [collation_name1],
   Col2 varchar(20) collate [collation_name2],
   Col3 varchar(10)
)

И еще одна информация вам безусловно потребуется для ответа, по крайней мере, на часть вопросов теста. Вот она: коллейшен той базы данных которой принадлежит тест-таблица T1 равен [collation_name0]. А вот теперь — успехов!

1
SELECT * FROM T1 WHERE Col1 COLLATE [collation_name4] = Col2
Смотреть ответ

выполнение: успешно

компоненты выражения: Col1=A, Col2=B

начальные метки/значения: А-явно/collation_name4, B-неявно/collation_name2

финальная метка/значение: А-явно+B-неявно=А-явно/collation_name4

1
SELECT * FROM T1 WHERE Col1 COLLATE [collation_name4] = Col3
Смотреть ответ

Ответы полностью идентичны предыдущему вопросу за исключением того факта, что изначальное значение метки B-неявно не [collation_name2], а [collation_name0]

1
SELECT * FROM T1 WHERE Col1 COLLATE [collation_name4] = Col2 COLLATE [collation_name2]
Смотреть ответ

выполнение: конфликт коллейшенов, error

компоненты выражения: Col1=A, Col2=B

начальные метки/значения: А-явно/collation_name4, B-явно/collation_name2

финальная метка/значение: А-явно+B-явно=Error при несовпадающих значениях

1
SELECT * FROM T1 WHERE Col1+Col2>Col3
Смотреть ответ

выполнение: конфликт коллейшенов, error

компоненты выражения: Col1=A, Col2=B, Col3=C

начальные метки/значения: А-неявно/collation_name1, B-неявно/collation_name2, C-неявно/collation_name0

промежуточные метка/значение: А-неявно+B-неявно=X; однако оператор + (конкатенация строк) является «без учета X» и выполнение продолжается

финальная метка/значение: Error т.к. оператор «больше» НЕ является «без учета X» и не может работать если с любой его стороны находится такая метка

1
2
DECLARE @q varchar(15)
SELECT * FROM T1 WHERE Col1+Col2>Col3+@q
Смотреть ответ

выполнение: конфликт коллейшенов, error

компоненты выражения: Col1=A, Col2=B, Col3=C, @q=D

начальные метки/значения: А-неявно/collation_name1, B-неявно/collation_name2, C-неявно/collation_name0, D-умолчание/collation_name0

промежуточные метка/значение: А-неявно+B-неявно=X; однако оператор + (конкатенация строк) является «без учета X» и выполнение продолжается

промежуточные метка/значение: C-неявно+D-умолчание=C-неявно/collation_name0; таблица сочетаний для вычисления значения метки не нужна, т.к. они совпадают, тип же ее берется наиболее «весомый» из двух вариантов

финальная метка/значение: Error т.к. оператор «больше» НЕ является «без учета X» и не может работать если с любой его стороны находится такая метка

1
2
DECLARE @q varchar(15)
SELECT * FROM T1 WHERE Col1+@q>Col2+@q COLLATE [collation_name5]
Смотреть ответ

выполнение: успешно

компоненты выражения: Col1=A, @q=B, Col2=C, @q=D

начальные метки/значения: А-неявно/collation_name1, B-умолчание/collation_name0, C-неявно/collation_name2, D-явно/collation_name5

промежуточные метка/значение: А-неявно+B-умолчание=А-неявно/collation_name1

промежуточные метка/значение: C-неявно+D-явно=D-явно/collation_name5

финальная метка/значение: финальная метка/значение: А-неявно+D-явно=D-явно/collation_name5