Сразу хочу согласится с придирчивыми читателями —
да, да, вы правы, строго говоря, слова вынесенного в заголовок статьи просто не существует. Ни один словарь мира не скажет вам что за невидаль такая — «коллейшен»? Ну нету такого! В тоже время, даже юные падаваны только вставшие на путь постижения SQL Server (впрочем, в данном случае — любой СУБД), не говоря уже о заматеревших джедаях познавших Силу его, сразу же сориентируются: ну так это ж collation — набор правил сопоставления двух строковых значений! И будут совершенно правы —
как раз о таких правилах и собирается сегодня вести рассказ автор данной статьи и данного блога. «Проблемное» слово нравится ему оттого, что корректный и буквальный перевод английского термина (все эти «сличение», «сравнение», «сопоставление» и т.п.) имеет слишком уж отдаленное отношение к сути происходящего. Ну а повторять «политически грамотное» выражение
«набор правил сопоставления двух строковых значений» из абзаца в абзац... 
Нет уж, давайте сэкономим немного бумаги тем, кто будет читать печатную версию данной заметки и ограничимся всем понятным и без перевода коллейшеном. Тем более что в неофициальном словаре SQL-сленга данный термин устоялся давным-давно и никого не шокирует, ровно как не вызывает истерик тот же «резалт-сет», бывший в девичестве result set-ом. А, допустим, те же кракозябры, не только не являются «легальным» словом, но и даже непонятно кем были в оригинале. И ничего — всем компьютерщикам (еще одно разговорное словечко, до кучи) понятно, о чем речь. Кстати говоря, будете плохо обращаться с коллейшеном — вполне возможно получите в резалт-сете кракозябры, улавливаете мою мысль? 
Так вот —
коллейшены...
На первый взгляд кажется вопрос выеденного яйца не стоит. Ну чего там сложного? Сравниваем хотим с учетом регистра, а хотим — без, вот и вся «наука». Не торопитесь! Знаете ли вы что коллейшены существуют аж на 4-х (!) разных уровнях? А как эти «старшие»/«младшие» коллейшены друг на друга влияют объяснить сможете? А как сменить его на каждом из уровней? А если сравнивать два значения (например — из двух колонок) с разными коллейшенами — каков будет результат? Наконец, тот факт что разных коллейшенов много (как много в мире языков) это, конечно, известно всем. А то, что все эти многочисленные коллейшены разделяются на 2 большие группы с, пусть не принципиальными (хотя — это как посмотреть), но определенно весомыми различиями вам известно? Сможете обосновать выбор для конкретной базы данных коллейшена из той или иной группы? А выбирать придется, тем более что эти группы процентов на 75 перекрываются по своему содержимому, и конкретное правило сравнения можно взять как отсюда, так и оттуда. Одним словом — не сомневайтесь, по вопросу можно написать полновесную книжную главу, а то и вовсе книгу. Осветить все аспекты коллейшенов всего в одной статье автор, разумеется, не способен, но выжать максимум из пространства под эту статью отводимую — постарается.
Уровни, имена, группы и чем коллейшен не является.
Прежде всего — очертим границы нашего рассказа, и набросаем его «план-схему». Сразу обозначим что один конкретный коллейшен можно назначить:
- серверу (или, более формально, экземпляру)
- базе данных
- колонке таблицы (а вот всей таблице — нельзя!)
- отдельному выражению содержащему символьные литералы и/или переменные
Собственно, первые 4 раздела статьи освещают как раз эти пункты и отвечают на вопросы «как назначить», «как сменить», «как проверить текущее значение» и т.п. В них же войдет еще одна, и довольно, будем честны, непростая для понимания тема — сочетание нескольких коллейшенов внутри одного выражения и разрешение их конфликтов.
Далее и отдельно рассмотрим вопрос какие вообще есть коллейшены? Т.е. «огласите, пжалста, весь список» — как раз про этот раздел. Его вопросы: «как формируется имя отдельного коллейшена», «составные части имени», «возможные сочетания частей» и т.д.
Наконец, автор вроде «грозил» что будет 2 больших группы коллейшенов? Так и есть, вот они:
- Windows коллейшены
- SQL Server коллейшены
«В чем разница», «какой подходит мне», «юникод или кодовая страница» — вот темы заключительного раздела статьи.
Мы практически готовы двинуться по обозначенному пути, однако сначала маленькое замечание от том где не надо искать коллейшены. Прежде всего — хоть это и очевидно, но зафиксирую эту мысль — коллейшен по своему определению может оказать влияние только на программные сущности строковых типов. Char/nchar, varchar/nvarchar, text/ntext — вот все 6 его «клиентов». Всякие там int, datetime, bit, и прочие float-ы не оказывают на него ровно никакого влияния. Ну а он — на них, соответственно. А во вторых, с удивительной для такого очевидного вопроса (впрочем, когда знаешь, вообще все вопросы очевидны) частотой на форумах задается вопрос: «кодовая страница (code page) и collation — одно и то же»? Разумеется нет, и только нет! Разве способна кодовая страница «приказать» строкам SqLcMd и sqlcmd быть равными? А коллейшен легко, для того и придуман. Разве может кодовая страница «сказать» что Ä, Ö, Ü и прочие буквы с диакритическими знаками равны их «базовым» символам (A, O, U в нашем примере)? А для коллейшена — рядовой вопрос. Ну а так, что бы в ряде случаев показанные только что символы с диакритическими знаками «засчитывались» бы за двухсимвольные сочетания (т.е. Ä=AE, Ö=OE, Ü = UE) — так кодовой странице «слабо»? Ой, слабо... Ну а про коллейшен вы вновь правильно догадались, ему не слабо! Это все потому, что code page есть ничто иное как таблица перевода «байтиков» которые и есть каждая буква для компьютера, в какой-то визуальный образ, распознаваемый именно как буква человеком. Что там чему «равно», что на что можно «заменить» — кодовая страница не ведает и не может по факту своего назначения. А вот коллейшен — не только может, но и вовсю этим пользуется, потому что его заботит вопросы «а кто здесь первый?» и «кто кому равен?». В тоже время — не впадайте в другую крайность, не говорите, как это делают многие отвечающие на форумах «коллейшен — это ни разу не кодовая страница, это таблица правил!». И эта крайность тоже неверна, хоть коллейшен это, действительно — таблица правил, прежде всего. Однако коллейшен, если говорить не по формальной «науке», а по результатам его воздействия на базу данных или колонку таблицы, это и кодовая страница в том числе, помимо правил. Правда, если мы работаем только с юникодными буквами/символами то кодовая страница, хоть и продолжает быть «кодированной» в имени коллейшена (т.е. является составной частью его имени), перестает играть хоть какую-то роль (это-то, правда, не открытие никакое, юникод вообще придумали как альтернативу и замену этих самых страниц). Т.е. коллейшен местами кодовая страница и таблица правил, а местами — только таблица. Не очень понятно? Не беспокойтесь, все, безусловно, прояснится когда мы подойдем к той части нашего плана в которой речь пойдет про две большие группы коллейшенов, Windows и SQL Server. Пока суммируем факты этого вводного раздела:
- коллейшен не имеет никакого воздействия (ни алгоритмического, ни визуального) на данные любых типов кроме char/nchar, varchar/nvarchar, text/ntext
- коллейшен при любых раскладах оказывает непосредственное влияние на результат сопоставления двух строковых объектов
- иногда коллейшен влияет на то, как визуально будет выглядеть информация символьного типа
Ну а вот теперь — поехали.
Коллейшен уровня экземпляра.
Итак, первым нам на рассмотрение попадает коллейшен самого высокого ранга. Он «присваивается» целому серверу! В рамках этих первых четырех разделов, напомню, мы не задаемся вопросами «какие коллейшены бывают», «в чем разница», «какой выбрать» и т.д. Для этих разделов коллейшен всего один и его роль выполняет замещающая строка [collation_name]. Т.е. в реальном коде на ее месте будет название одного конкретного коллейшена и у нас еще будет отдельный раздел статьи рассматривающий все богатство выбора. Но сейчас мы сосредотачиваемся исключительно на вопросах управления этим условным коллейшеном.
Как установить значение для нового экземпляра.
Это, как раз, почти самое простое. В процессе стандартной установки, в визарде, у вас будет окошко как на иллюстрации справа (все изображения статьи кликабельны). Переходите на соответствующую вкладку визарда, вызываете несложный диалог настройки коллейшена, выбираете его самого из выпадающего списка и, возможно, помечаете чек-боксами его дополнительные опции. О самих коллейшенах (их именах) и об упомянутых опциях разговор у нас впереди. «OK» в диалоге — и установку можно продолжать, значение коллейшена зафиксировано.
Как проверить текущее значение.
Если хотите воспользоваться интерфейсом студии: в окне Object Explorer вызываете окно свойств того экземпляра чей коллейшен требуется проверить. На первой же странице открывшегося окна видите примерно такую информацию как на картинке слева и чуть ниже. Если же значение текущего коллейшена требуется программно, в резалт-сете, то:
1 | SELECT SERVERPROPERTY('Collation') |
Вам вернется резалт-сет из единственной ячейки в которой и будет требуемое значение.
Как изменить значение для существующего экземпляра.
О! А вот это, быть может, самый сложный раздел статьи. Нет, описать-то его не так что бы затруднительно было, и даже можно сказать пары строк хватит, а вот реализовать, да на работающей системе, с полусотней (хотя бы) подключенных пользователей... В общем, установка значения для нового экземпляра на два порядка проще, чем ее смена для экземпляра существующего. А отсюда вывод? Правильно — очень тщательно думаем и выбираем какой collation нас устроит еще до (ну или, самое позднее, во время) процесса инсталяции. Потому что если мы ошибемся, и система проработает в нормальной эксплуатации хотя бы пару недель прежде чем мы это осознаем, то... судите сами.
«Засада» здесь будет в том, что по факту требуемый параметр после инсталяции не меняется. Ну то есть в принципе. Поэтому пути у вас 2, и оба сводятся к пересозданию всех системных БД включая master:
- если хотите «по-простому», то просто де-инсталл ошибочного экземпляра, а потом его ре-инсталл с верным значением коллейшена. Что такое снести сервер, а потом восстановить его в прежнем состоянии вы, должно быть, и сами знаете. Это даже при наличии всех бэкапов, тщательном планировании процесса и т.п.
- если хотите сделать вид что «так и было задумано» — берете DVD диск с дистрибутивом SQL Server (тот самый с которого инсталлировали экземпляр с ошибочным коллейшеном), и перейдя в командной строке к нему в корень пишите:
Все в одну строку, само собой. И запускаете. По окончанию имеете первый же вариант: «чистенький» инстанс с верным коллейшеном и исключительно системными БД. Ни юзерских баз, ни настроек сервера (если вы их меняли), ни логинов, ни связанных серверов (linked server), ни работ (job), ни репликаций... Можете начинать восстановление.Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=[имя_целевого_экземпляра]
/SQLSYSADMINACCOUNTS=[админская_учетка] /[ SAPWD=[ее_пароль] ] /SQLCOLLATION=[правильный_collation_name]
Что вы говорите? «Лучше уж я сразу подумаю»? Так а я про что!