وحید نصیری وحید نصیری
یافتن تداخلات Collations در SQL Server
اندازه‌ی قلم متن
تخمین مدت زمان مطالعه‌ی مطلب: دو دقیقه

اگر دیتابیس خود را در طی چند سال از یک نگارش به نگارشی دیگر یا از یک سرور به سروری دیگر منتقل کرده باشید، به احتمال زیاد به مشکلات Collations هم برخورده‌اید. یکی از فیلدها Arabic_CI_AS است (بجا مانده از دوران قبل از SQL Server 2008) در یک جدول و در جدولی دیگر فیلدی تازه‌ای با Collation از نوع Persian_100_CI_AS تعریف شده است. Collations نحوه ذخیره سازی و مقایسه رشته‌ها را کنترل می‌کنند. زمانیکه یک جدول جدید را در SQL Server ایجاد می‌کنیم، اگر Collation فیلدها به صورت صریح ذکر نگردند، بر مبنای همان Collation پیش فرض دیتابیس تعریف خواهند شد.
بنابراین اگر پس از استفاده از SQL Server 2008 و تنظیم Collation پیش فرض دیتابیس به Persian_100_CI_AS ، به این موارد دقت نکنیم، دیر یا زود دچار مشکل خواهیم شد.
عملیات مرتب سازی با وجود تداخلات Collations مشکل ساز نمی‌شود (خطایی دریافت نمی‌کنید)، اما ممکن است الزاما صحیح عمل نکند. مشکل از آنجایی آغاز می‌شود که قصد داشته باشیم داده‌ها را مقایسه کنیم یا join ایی بین این دو جدول با فیلدهای ناهمگون از لحاظ Collation ایجاد نمائیم. در این حالت حتما خطاهای تداخل Collation را دریافت کرده و کوئری‌های ما اجرا نخواهند شد.
Cannot resolve collation conflict for equal to operation

یک راه حل این است که در حین join به صورت صریح collation هر دو فیلد ذکر شده را به صورت یکسان ذکر کنیم که بیشتر یک مرهم موقتی است تا راه حل اصولی. برای مثال:
SELECT ID
FROM ItemsTable
INNER JOIN AccountsTable
WHERE ItemsTable.Collation1Col COLLATE DATABASE_DEFAULT
= AccountsTable.Collation2Col COLLATE DATABASE_DEFAULT
راه دیگر این است که مشخص کنیم که Collation کدام فیلدها در دیتابیس با Collation پیش فرض دیتابیس تطابق ندارند. سپس بر اساس این لیست شروع به تغییر Collations نمائیم.
اسکریپت زیر تمام فیلدهای ناهمگون از لحاظ Collation دیتابیس جاری را برای شما لیست خواهد کرد:
DECLARE @defaultCollation NVARCHAR(1000)
SET @defaultCollation = CAST(
      DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS NVARCHAR(1000)
  )

SELECT C.Table_Name,
     Column_Name,
     Collation_Name,
     @defaultCollation DefaultCollation
FROM   Information_Schema.Columns C
     INNER JOIN Information_Schema.Tables T
          ON  C.Table_Name = T.Table_Name
WHERE  T.Table_Type = 'Base Table'
     AND RTRIM(LTRIM(Collation_Name)) <> RTRIM(LTRIM(@defaultCollation))
     AND COLUMNPROPERTY(OBJECT_ID(C.Table_Name), Column_Name, 'IsComputed') = 0
ORDER BY
     C.Table_Name,
     C.Column_Name
برای مثال جهت تغییر Collation فیلد Serial از جدول tblArchive از نوع nvarchar با طول 200 به Persian_100_CI_AS می‌توان از دستور T-SQL زیر استفاده کرد:
ALTER TABLE [tblArchive] ALTER COLUMN [Serial] nvarchar(200) COLLATE Persian_100_CI_AS not null

‫۱۴ سال و ۱۱ ماه قبل، پنجشنبه ۲۸ آبان ۱۳۸۸، ساعت ۱۹:۵۳
۱۳۷۱۹
۲
SQL Server
مطالب مشابه
  • #
    مهمان مهمان
    ‫۱۴ سال و ۱۱ ماه قبل، یکشنبه ۱ آذر ۱۳۸۸، ساعت ۲۰:۵۲
    آریان
    میشه اینطوری هم انجام داد
    SELECT sys.tables.name AS TableName , sys.columns.name AS ColumnName ,
    sys.columns.is_nullable , sys.columns.collation_name
    FROM sys.columns
    INNER JOIN sys.tables ON sys.columns.object_id=sys.tables.object_id
    WHERE sys.columns.collation_name<>(CAST(DATABASEPROPERTYEX(DB_NAME() , 'Collation') AS NVARCHAR(1000)))
  • #
    احمد رجبی احمد رجبی
    ‫۶ سال و ۸ ماه قبل، یکشنبه ۱ بهمن ۱۳۹۶، ساعت ۱۴:۰۷
    تهیه اسکریپت تغییر Collation  تمامی  فیلدهای ناهمگون در  دیتابیس جاری : 
    DECLARE @defaultCollation NVARCHAR(1000)
SET @defaultCollation = CAST(
      DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS NVARCHAR(1000)
  )


select 'ALTER TABLE ' 
+ QUOTENAME(C.TABLE_SCHEMA)
+'.'+ QUOTENAME(C.Table_Name) 
+' ALTER COLUMN ' +' [' +Column_Name+'] ' 
+  DATA_TYPE+'('+cast(character_maximum_length as varchar(10))+')' +' COLLATE Persian_100_CI_AS ' 
+(case IS_NULLABLE when 'YES' then 'NULL' else 'NOT NULL' end )+';'

FROM   Information_Schema.Columns C
     INNER JOIN Information_Schema.Tables T
          ON  C.Table_Name = T.Table_Name
WHERE  T.Table_Type = 'Base Table'
     AND RTRIM(LTRIM(Collation_Name)) <> RTRIM(LTRIM(@defaultCollation))
 --AND DATA_TYPE='nvarchar'
 AND character_maximum_length>0
ORDER BY
     C.Table_Name,
     C.Column_Name