Конфлікти схем зіставлення (collation) в Microsoft SQL Server 2000

Лимонов Олексій

Вступ

Опрацювання та зберігання символьних даних на сервері MS SQL 2000 здійснюється за допомогою схем зіставлення (collation). Схеми містять шаблони кожного символу, правила сортування та порівняння. У попередніх версіях сервера MS SQL необхідно було окремо вказувати кодову сторінку і порядок сортування символьних даних, причому ці налаштування діяли відразу на всі об'єкти сервера. В MS SQL 2000 схеми зіставлення дозволили більш гнучко підходити до роботи з текстовими даними. У даній статті розглядаються основні принципи роботи схем, а також їх застосування в російських умовах.

Призначення collation

Символьні дані

У машинному поданні будь-який символ або знак представляє різні комбінації бітів. Відповідно, використання одного байти для зберігання символу дає можливість визначити до 256 різних символів. Якщо збільшити обсяг даних до двох байт, з'явиться можливість розпізнавати 65 536 символів.

Кодова сторінка є ні що інше, як набір різних комбінації станів бітів (всього 256) в байтове структурі. Ці комбінації визначають символи верхнього та нижнього регістрів, цифри, спеціальні символи. При перенесенні даних між комп'ютерними системами з різними кодовими сторінками необхідно виконати перетворення. Символи, бітова комбінація яких відсутня в схемі призначення, в результаті будуть втрачені.

Для усунення подібних ситуацій міжнародна організація стандартів ISO і група Unicode розробили стандарт Unicode.

Порядок сортування визначає правила порівняння та подання символів. Наприклад, символ «а» більше символу «б». Крім того, порядок сортування встановлює правила зіставлення символів верхнього і нижнього регістрів.

Опис схем зіставлення

Схеми зіставлення collation визначають способи зберігання і обробки символьних даних сервера. Кожна схема встановлює:

порядок сортування для даних з кодуванням Unicode;

порядок сортування для даних з кодуванням не-Unicode;

кодову сторінку для даних з кодуванням не-Unicode.

Для MS SQL 2000 не треба вказувати всі три параметри, достатньо вибрати ім'я схеми та порядок сортування.

На сервері реалізовані дві групи схем - Windows collations і SQL collations. Перша група схем зіставлення реалізована на сервер для підтримки регіональних налаштувань Windows. Рекомендується працювати саме з цією групою. Друга група, SQL collations, використовується для сумісності з попередніми версіями сервера MS SQL. Її вибір може бути виправданий, якщо ви плануєте обмінюватися даними з серверами MS SQL 6.5 або MS SQL 7.0, або якщо додаток, що працює з даними, розроблено з урахуванням схем зіставлення попередніх версій сервера.

На різних рівнях можуть використовуватися різні схеми зіставлення:

Рівень сервера. Схема зіставлення вибирається при установці сервера. Обрана схема буде використана за замовчуванням для всіх системних баз і користувацьких баз даних, а також всіх об'єктів кожної бази. При необхідності змінити схему на рівні сервера використовується утиліта Rebuild Master.

Рівень бази даних. Схему зіставлення можна вказати при створенні бази. Всі об'єкти бази будуть використовувати цю схему за замовчуванням. Також обрана схема буде використовуватися для символьних змінних і параметрів. Змінити схему зіставлення бази даних можна за допомогою команди ALTER DATABASE.

Рівень поля таблиці. При створенні таблиці можна вказати власну схему зіставлення.

На рівні об'єктів бази (таблиць) схема не вказується.

Практичне застосування

Як не дивно, на схеми зіставлення, як і на тригери, часто не звертають належної уваги. Точніше - про схеми згадують тільки під час виникнення помилки «Cannot resolve collation conflict». Для рішення виникаючих проблем необхідно розуміти причини їх виникнення і шляхи їх можливого рішення.

Розглянемо варіант роботи на ОС Windows 2000 Server з регіональними настройками Russian. При установці MS SQL 2000 програма пропонує встановити схему collation Cyrillic_General_CI_AS. Перша частина схеми «Cyrillic_General» визначає кодову сторінку. Далі йдуть правила сортування, наприклад, CI (case-insensitive) - нечутлива до регістру, AS (accent-sensitive) - чутлива до наголосу. Можна отримати повний список схем зіставлення з розшифровкою, виконавши запит        

select * from:: fn_helpcollations ()     

При виборі Cyrillic_General_CI_AS всі системні бази даних, у тому числі база TempDB, будуть використовувати саме цю схему порівняння. Як зазначено вище, все знову створювані для користувача бази і таблиці по замовчуванням будуть мати таку саму схему. Однак ніщо не заважає при установці вибрати іншу схему і так само з нею працювати.

Коли ви працюєте в рамках однієї структури collation, проблем не виникає. Найчастіше вони з'являються, коли ви приєднуєте або встановлюєте базу з іншою схемою зіставлення. У більшості випадків це схема SQL_Latin1_General_CP1251_CI_AS. Вона представляє собою схему зіставлення виду SQL collations, що дісталася в спадок від версії MS SQL 7.0. Наприклад, зазначена схема встановлюється, якщо ви виконуєте оновлення сервера або переносите БД з версії MS SQL 7.0 на MS SQL 2000. Тут слід відзначити, що хоч за змістом схеми SQL_Latin1_General_CP1251_CI_AS і Cyrillic_General_CI_AS схожі, насправді для сервера це різні схеми зіставлення. І при їх одночасному використанні складно уникнути помилок.

Для прикладу розглянемо ситуацію, коли сервер встановлений з collation Cyrillic_General_CI_AS, є база даних NEW_BASE з серверної схемою зіставлення Cyrillic_General_CI_AS, і база даних OLD_BASE для роботи зі старим додатком зі схемою SQL_Latin1_General_CP1251_CI_AS. За базу NEW_BASE можна не турбуватися - в рамках серверної схеми зіставлення всі запити будуть коректно обробляти символьні дані. Інша справа, коли необхідні дані з OLD_BASE.

Помилка «Cannot resolve collation conflict» буде з'являтися:

При з'єднаннях JOIN або UNION з таблицями з бази з іншою схемою зіставлення.

Під час роботи з тимчасовими таблицями в контексті робочої бази даних. Тимчасові таблиці створюються в базі TempDB, де, як було вже зазначено, використовується серверна схема зіставлення, і символьні дані в цьому випадку коректно порівняти не вдається.

Найбільший загальний випадок - коли намагаються порівняти значення символьних полів різних схем зіставлення (навіть у межах однієї таблиці або даних бази).

Повідомлення про помилку говорить сама за себе - сервер не в стані порівняти символи з різних схем зіставлення. Рішення напрошується наступне: привести дані до однієї схемою collation.

Якщо у запитах до БД OLD_BASE йде робота з тимчасовими таблицями, або змінними табличного типу, то при їх створенні треба явно вказувати потрібну схему collation для кожного символьного поля. Наприклад:        

create table # t   

(f1 int not null,   

f2 char (5) collate   SQL_Latin1_General_CP1251_CI_AS,   

f3 varchar (150) collate   SQL_Latin1_General_CP1251_CI_AS)     

Далі, виконати з'єднання між полями з різними схемами безпосередньо не можна. Відповідно, не можна зробити JOIN або UNION для таблиць з різними схемами collation з однієї або різних баз. Інакше знову буде видане повідомлення про помилку. У цьому випадку об'єднуються поля також необхідно призвести до однією схемою за допомогою перетворення схеми зіставлень. Припустимо, з'єднання таблиць OLD_BASE і NEW_BASE можна виконати так:        

select *   

from NEW_BASE.dbo.Report as A   

join OLD_BASE.dbo.Report as B   

on A.char_key = B.char_key   collate Cyrillic_General_CI_AS     

а запит на об'єднання так:        

select int_data, date_data, char_key   

from NEW_BASE.dbo.Report   

union all   

select int_data, date_data, char_key collate Cyrillic_General_CI_AS   

from OLD_BASE.dbo.Report     

Перетворення схем зіставлення полів можна робити в різних варіантах з'єднань. Але писати кожен раз подібні запити, з явним зазначенням схеми collation - не найкраще проведення часу. Тоді можна розглянути варіант приведення всіх баз до єдиною схемою - серверної. Для зміни схеми collation, що використовується в БД за замовчуванням, служить команда        

alter   database OLD_BASE collate Cyrillic_General_CI_AS     

Однак це ще не змінить схему для символьних полів у базі. Міняти їх потрібно або вручну через Enterprise Manager, або написати подібний запит:        

alter   table Report alter column char_key char (5) collate Cyrillic_General_CI_AS     

При цьому є ряд обмежень - не можна змінити Схеми для обчислюваних полів, індексованих полів, полів з обмеженням CHECK або зовнішніх ключів. Необхідно спочатку видалити їх, а після зміни схеми зіставлення заново створити. Так що робота тут може бути проведена велика і серйозна.

Якщо ви не в змозі привести нову базу до серверної схемою, і у вас немає можливості змінювати код у придбаному додатку - треба міняти серверну схему і схему всіх ваших баз даних (знову-таки, якщо це не призведе до зупинки роботи інших програм і баз). Самий надійний і простий спосіб заміни серверної схеми - перевстановлення всього сервера, що в принципі рівносильно використання утиліти Rebuild Master. Після цього треба відтворити структури баз (але не дані в них!) вже з новою схемою collation, потім імпортувати дані в оновлену структуру.

Якщо стара БД прив'язана до певною схемою collation, а нова база використовує іншу схему, то залишається один спосіб -- поставити новий сервер або встановити іменований примірник (instance) SQL-сервера. Щоправда, ще не ясно, скільки піде ресурсів на реалізацію іменованої установки сервера, і чи підтримує придбане додаток взагалі таку конфігурацію. Цілком можливо, що простіше буде встановити окремий сервер зі своєю схемою collation на окремій машині.

Висновок

Як ви могли переконатися - вибір схеми зіставлення може істотно вплинути на розробку та супровід серверних рішень. Тому необхідно визначитися з оптимальним вибором схеми collation на початковому етапі відповідно до вимог існуючих програм та стратегією розвитку системи в цілому.

Список літератури

Для підготовки даної роботи були використані матеріали з сайту http://www.rsdn.ru/