مغایرت Collation در دیتابیس SQL Server

مغایرت Collation در سطح دیتابیس یا سرور، SQL Server به ما این امکان رو میده تا  Collation های متنوعی برای دیتابیس هامون انتخاب کنیم. ما میتونیم اون ها رو در سطح عمومی سرور یا به ازای هر دیتابیس تنظیم کنیم. اما وقتی نیاز باشه بین دو دیتابیس Join بزنیم این ماجرا ممکنه کمی مشکل ساز بشه و یا حتی وقتی temp table  درست کنیم و نیاز باشه دیتا اون رو با جداول ایجاد شده مقایسه کنیم هم ممکنه توی Collation های مختلف  مشکل پیش بیاد.

چگونه مغایرت Collation در دیتابیس را برطرف کنیم

در مرحله اول میتونیم Collation سطح دیتابیس رو به کمک T-SQL با SSMS تغییر بدیم، اما این تغییر روی تمام Object های داخل دیتابیس اعمال نمیشه و ممکنه همچنان مشکل شما باقی بمونه، متاسفانه تغییر Collation تمام دیتا کمی دردسر سازه.

تا اینجا مشخص شد Collation چند سطح داره :

1- سطح سرور

• موقع نصب Instance تعیین میشه.

2- سطح دیتابیس

موقع ایجاد یا بازگردانی دیتابیس مشخص میشه و میتونه ارث بری کنه از Collation سطح سرور.

3- سطح Object

• موقع ایجاد Object از Collation سطح دیتابیس ارث بری میکنه اما در صورت تغییر Collation DB تغییر  نمیکنه و نیاز هست به صورت مجزا تنظیم بشه.

دقت کنید که Collation در سطح Table برای SQL Server بی معنی هست ، یعنی از سطح دیتابیس مستقیم میره سراغ Object های موجود توی Table ، مثلا برای هر Column تعریف شده در جدول میتونه معنی داشته باشه.

اما راه حل های موجود برای رفع مغایرت یا عدم همخوانی Collation

فرض کنیم قرار هست دو جدول از دو دیتابیس مختلف با هم Union بشه، با اجرا کوئری خطا Msg 468  که مربوط به Collation conflict هست به وجود خواهد اومد.

Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS” and “Arabic_CI_AS” in the UNION operation.

اولین قدم اینکه بریم سراغ یک از دیتابیس ها و به کمک T-SQL یا SSMS اقدام به تغییر Collation اون کنیم.

کد T-SQL :

USE master
GO
ALTER DATABASE MyOptionsTest COLLATE French_CI_AS
GO

و با این کد از تغییر Collation مطمئن بشیم :

–Verify the collation setting.
SELECT name, collation_name FROM sys.databases
WHERE name = N‘MyOptionsTest’
GO

حالا  اقدام به اجرا Query می کنیم. مشاهده خواهید کرد که همچنان Msg 468 وجود داره، چرا ؟

همونطور که گفته شد تغییر Collation تو سطح دیتابیس نمیتونه تمام Collation های Object های اون دیتابیس رو تغییر بده و خودتون باید برای این کار اقدام کنیم. برای این کار کوئری های نوشته شده که به سراغ تک تک Object  ها میره و اونها رو تغییر میده.

نمونه کوئری :

DECLARE @collation NVARCHAR(64)
SET @collation = 'Latin1_General_CI_AS'

SELECT 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] ' + 'ALTER COLUMN [' + COLUMN_NAME + '] ' + DATA_TYPE + '(' + CASE CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN 'MAX'
ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END + ') ' + 'COLLATE ' + @collation + ' ' + CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' ELSE 'NULL' END FROM INFORMATION_SCHEMA.columns
WHERE COLLATION_NAME IS NOT NULL AND TABLE_NAME IN
( SELECT TABLE_NAME from information_schema.tables WHERE table_type = 'BASE TABLE' ) AND COLLATION_NAME <> @collation

نتیجه اجرا کوئری :

و در نهایت نیاز خواهد بود کوئری های ALTER ایجاد شده رو بر روی دیتابیس مورد نظر اجرا کنید تا Collation مربوط به تمام Object ها تغییر کنه .