در پایگاه‌های داده، ذخیره‌سازی اطلاعات متنی فقط به نگهداری حروف محدود نمی‌شود، بلکه نحوه مقایسه، مرتب‌سازی و جست‌وجوی این داده‌ها نیز اهمیت بسیار زیادی دارد. یکی از مفاهیم کلیدی که این رفتارها را کنترل می‌کند، مفهومی به نام Collation در SQL است.

Collation مشخص می‌کند که داده‌های متنی چگونه با یکدیگر مقایسه شوند؛ برای مثال آیا حروف بزرگ و کوچک تفاوت دارند؟ آیا حروف فارسی، عربی یا لاتین چگونه مرتب می‌شوند؟ و آیا حساسیت به لهجه یا علائم نگارشی وجود دارد یا خیر.

در این مقاله، به‌صورت کامل بررسی می‌کنیم که Collation در SQL چیست، چگونه کار می‌کند، چه انواعی دارد و چه تأثیری بر مرتب‌سازی و جست‌وجوی داده‌ها می‌گذارد.

Collation در SQL چیست؟

به‌طور ساده، Collation مجموعه‌ای از قوانین است که نحوه مقایسه و مرتب‌سازی رشته‌های متنی (String) را در پایگاه داده مشخص می‌کند.

این قوانین تعیین می‌کنند:

• ترتیب حروف (Alphabetical Order)
• حساسیت یا عدم حساسیت به حروف بزرگ و کوچک
• حساسیت به لهجه‌ها (Accent)
• نحوه مقایسه کاراکترهای خاص و زبان‌های مختلف

مثال ساده

فرض کنید دو مقدار زیر را داریم:

"Ali"
"ali"

بسته به Collation:

• ممکن است این دو مقدار کاملاً برابر در نظر گرفته شوند
• یا دو مقدار متفاوت محسوب شوند

چرا Collation مهم است؟

Collation در SQL مستقیماً روی موارد زیر تأثیر می‌گذارد:

1. مرتب‌سازی داده‌ها (ORDER BY)
2. مقایسه رشته‌ها (= , < , >)
3. جست‌وجو (WHERE, LIKE)
4. ایندکس‌ها (Indexes)
5. عملکرد (Performance)

بدون انتخاب Collation مناسب، ممکن است:

• مرتب‌سازی نادرست باشد
• نتایج جست‌وجو غیرمنتظره شوند
• داده‌های تکراری به‌درستی تشخیص داده نشوند

اجزای اصلی Collation

هر Collation معمولاً از چند بخش تشکیل می‌شود. به‌عنوان مثال در SQL Server:

Latin1_General_CI_AI

1. Character Set (مجموعه کاراکتر)

مشخص می‌کند داده‌ها مربوط به چه زبان یا استانداردی هستند:

• Latin1_General
• Arabic
• Persian
• UTF8

2. Case Sensitivity (حساسیت به حروف بزرگ و کوچک)

• CI → Case Insensitive (حساس نیست)
• CS → Case Sensitive (حساس است)

مثال:

CI: Ali = ali ✅
CS: Ali ≠ ali ❌

3. Accent Sensitivity (حساسیت به لهجه)

• AI → Accent Insensitive
• AS → Accent Sensitive

مثال در زبان‌های لاتین:

AI: é = e
AS: é ≠ e

4. Kana و Width (در برخی زبان‌ها)

در زبان‌هایی مثل ژاپنی برای تفاوت کاراکترهای Kana یا عرض حروف استفاده می‌شود.

Collation در SQL چگونه روی مرتب‌سازی (ORDER BY) تأثیر می‌گذارد؟

فرض کنید داده‌های زیر را داریم:

apple
Banana
cherry

Collation حساس به حروف بزرگ (CS)

ORDER BY name

نتیجه:

Banana
apple
cherry

Collation غیرحساس به حروف بزرگ (CI)

apple
Banana
cherry

در حالت CI، دیتابیس ابتدا حروف را یکسان در نظر می‌گیرد و سپس مرتب‌سازی منطقی‌تری انجام می‌دهد.

تأثیر Collation بر جست‌وجو (WHERE و LIKE)

مثال:

SELECT * FROM Users
WHERE Username = 'admin'

• اگر Collation از نوع CI باشد:
  • Admin، ADMIN و admin همگی پیدا می‌شوند
• اگر CS باشد:
  • فقط admin دقیقاً مطابق پیدا می‌شود

این موضوع به‌خصوص در:

• سیستم‌های احراز هویت
• نام کاربری
• ایمیل‌ها

بسیار مهم است.

Collation در SQL و ایندکس‌ها

Collation روی نحوه ساخت و استفاده از ایندکس‌ها نیز اثر دارد.

• اگر Collation ستون تغییر کند:
  • ایندکس‌ها ممکن است نیاز به بازسازی داشته باشند
• Collation ناسازگار بین ستون‌ها:
  • باعث خطا در JOIN یا مقایسه می‌شود

مثال خطای رایج:

Cannot resolve the collation conflict between...

سطح‌های مختلف تعریف Collation

Collation را می‌توان در چند سطح مختلف تعریف کرد:

1. سطح سرور (Server Level)

• Collation پیش‌فرض برای کل سرور
• هنگام نصب SQL Server تعیین می‌شود

2. سطح دیتابیس (Database Level)

ALTER DATABASE MyDB
COLLATE Persian_100_CI_AI

3. سطح جدول یا ستون

CREATE TABLE Users (
    Username NVARCHAR(50) COLLATE Persian_100_CS_AI
)

4. سطح Query

SELECT *
FROM Users
ORDER BY Username COLLATE Persian_100_CI_AI

Collation در زبان فارسی

در دیتابیس‌های فارسی، انتخاب Collation مناسب اهمیت ویژه‌ای دارد:

• تفاوت «ی» و «ي»
• تفاوت «ک» و «ك»
• ترتیب صحیح حروف فارسی

Collation‌های پیشنهادی:

• Persian_100_CI_AI
• یا Collation‌های مبتنی بر UTF-8

استفاده از Collation نامناسب ممکن است باعث:

• جست‌وجوی ناقص
• مرتب‌سازی اشتباه حروف فارسی
• ناسازگاری داده‌ها شود

تفاوت Collation و Encoding

این دو مفهوم اغلب با هم اشتباه گرفته می‌شوند:

ممکن است دو ستون Encoding یکسان داشته باشند اما Collation متفاوت.

بهترین روش‌ها (Best Practices)

• ✅ در ابتدای طراحی دیتابیس Collation را مشخص کنید
• ✅ برای پروژه‌های چندزبانه از UTF-8 استفاده کنید
• ✅ از Collation یکسان در JOINها استفاده کنید
• ✅ برای داده‌های حساس (مثل رمز یا کد) از CS استفاده کنید
• ❌ تغییر Collation در دیتابیس‌های بزرگ بدون بررسی انجام ندهید

جمع‌بندی

Collation یکی از مفاهیم اساسی اما کمتر دیده‌شده در SQL است که نقش حیاتی در مرتب‌سازی، جست‌وجو و مقایسه داده‌های متنی دارد. انتخاب Collation مناسب می‌تواند:

• دقت نتایج را افزایش دهد
• خطاهای منطقی را کاهش دهد
• عملکرد دیتابیس را بهبود ببخشد

در مقابل، انتخاب نادرست آن می‌تواند منجر به نتایج اشتباه، خطاهای Join و مشکلات چندزبانه شود. بنابراین، در هر پروژه SQL، Collation نه یک جزئیات کوچک، بلکه یک تصمیم معماری مهم است.