ה-Collation הוא מאפיין המגדיר אילו תווים נתמכים על ידי עמודות Char או Varchar לסוגיהם, וכיצד מתבצעות ההשוואות בין עמודות טקסטואליות. למשל- האם משתנה Varchar או עמודת Varchar יוכלו להכיל מחרוזות עם אותיות בעברית או ברוסית או בסינית וכו', וכיצד תתבצע ההשוואה בינהם - למשל האם "A" שווה ל-"a".
ניצור לצורך התרגול 4 דטבייסים:
Create Database CIAI Collate SQL_Latin1_General_CP1_CI_AI;
Create Database CIAS Collate SQL_Latin1_General_CP1_CI_AS;
Create Database CSAS Collate SQL_Latin1_General_CP1_CS_AS;
Create Database HCSAS Collate Hebrew_CS_AS;
שימו לב שלכל אחד הגדרנו Collation קצת שונה, והתאמנו את שמות הדטבייסים ל-Collation שלהם כדי להתמצא בקלות.
מה המשמעות של ה-Collation?
נתחיל מהראשון SQL_Latin1_General_CP1_CI_AI: הוא תומך באותיות לטיניות לסוגיהן, גם a B c אבל גם á Ñ ç,
אבל לא באותיות קיריליות (רוסית) או עבריות וכו'.
הסיומת CI_AI מלמדת שהוא Case Insensitive Accent Insensitive, הווה אומר - בהשוואה בין מחרוזות A שווה ל-a (כלומר Case Insensitive) וגם a שווה ל-á (כלומר Accent Insensitive).
בדטבייסים השני והשלישי ה-Collation הוא Accent Sensitive ולכן a שונה מ-á ו-n שונה מ-ñ,
אלא שהשני הוא Case Insensitive כמו הראשון
והשלישי הןא Case Sensitive ולכן מבחין בין q ל-Q (כל השלושה שומרים r קטנה בתור קטנה ו-R גדולה בתור גדולה, ורק ההשוואה בינהם יכולה להוביל לתוצאות שונות).
אם אבצע חיפוש בטבלת עובדים אחר כאלה ששמם הפרטי הוא Geri, האם אקבל גם את אלה ששמם הפרטי נכתב geri או GERI או gErI? זה תלוי בשאלה אם ה-Collation שם הוא Case Sensitive או לא.
ומה עם מי שכותב את שמו כך Géri? זה כבר תלוי אם הוא Accent Sensitive או לא.
נעבור לדטבייס האחרון, זה שה-Collation שלו הוא Hebrew_CI_AS: הוא תומך באותיות עבריות ולטיניות, אבל לטיניות פשוטות כמו באנגלית, ולא עם "תוספות" כמו בשפות אירופאיות אחרת (נניח- u & U כן, אבל ü לא).
ומה לגבי ה-CS_AS במקרה זה?
לגבי אותיות לטיניות זה כמו בדטבייס השני, אך לגבי אותיות ומחרוזות בעברית: Case Sensitive מציין ש-נ & ן (נון רגילה ונון סופית) אינן זהות והוא רגיש להבדלים, ולגבי אותיות עם ניקוד בטקסטים מנוקדים יש הבדל בין ג & גְּ (כלומר- גימל ללא ניקוד וגימל עם שווא ודגש).
לעבודה: ניצור 5 טבלאות זהות - 4 בארבעת הדטבייסים ואחת טבלה זמנית, ונכניס לתוכן תוכן:
Use CIAI;
Go
Drop Table If Exists T;
Create Table T(Txt Varchar(10));
Go
Truncate Table T;
Insert Into T(Txt) Values('גרשון'),('גֶּרְשוֹן'),('גרשונ'),('Geri'),('GERI'),('Géri');
Go
Use CIAS;
Go
Drop Table If Exists T;
Create Table T(Txt Varchar(10));
Go
Truncate Table T;
Insert Into T(Txt) Values('גרשון'),('גֶּרְשוֹן'),('גרשונ'),('Geri'),('GERI'),('Géri');
Go
Use CSAS;
Go
Drop Table If Exists T;
Create Table T(Txt Varchar(10));
Go
Truncate Table T;
Insert Into T(Txt) Values('גרשון'),('גֶּרְשוֹן'),('גרשונ'),('Geri'),('GERI'),('Géri');
Go
Use HCSAS;
Go
Drop Table If Exists T;
Create Table T(Txt Varchar(10));
Go
Truncate Table T;
Insert Into T(Txt) Values('גרשון'),('גֶּרְשוֹן'),('גרשונ'),('Geri'),('GERI'),('Géri');
Go
Use tempdb;
Go
Drop Table If Exists #T;
Create Table #T(Txt Varchar(10));
Go
Truncate Table #T;
Insert Into #T(Txt) Values('גרשון'),('גֶּרְשוֹן'),('גרשונ'),('Geri'),('GERI'),('Géri');
Go
כשנשלוף את הנתונים מהטבלאות, משלוש הטבלאות הראשונות נקבל את הפלט הזה:
ומשני הדטבייסים האחרונים את הפלט הזה:
למה?
שלושת הראשנים אינם תומכים בעברית, אלא רק באותיות לטיניות לסוגיהן, ולכן כל אותיות העבריות הפכן לסימני שאלה.
שני האחרונים תומכים בעברית ובאותיות לטיניות בלי תוספות, ולכן רק המחרוזת האחרונה שיש בה é השתבשה.
למה T# האחרונה תומכת בעברית? טבלאות זמניות נמצאות בדטבייס tempdb ש-Collation שלו הוא כמו של ה-SQL Server עצמו, ואת זה נוכל לראות כך:
SELECT SERVERPROPERTY('collation') AS SQLServerCollation
Select * From sys.databases;
למעלה מופיע ה-Collation של ההתקנה הזו של SQL Server שהוגדרה בעת התקנתו, זה יהיה גם ה-Collation של כל דטבייס שלא הוגדר עבורו במפורש Collation, וגם של tempdb - כפי שניתן לראות למטה.
בשרתים אחרים צפוי להיות Collation שונה לשרת ול-tempdb.
נמחק מכל הטבלאות את המחרוזות שהשתבשו ושכוללות כעת סימני שאלה:
Delete From CIAI..T Where Txt Like '%?%';
Delete From CIAS..T Where Txt Like '%?%';
Delete From CSAS..T Where Txt Like '%?%';
Delete From HCSAS..T Where Txt Like '%?%';
Delete From #T Where Txt Like '%?%';
וכעת 3 הראשונות ו-2 האחרונות יראו כך בהתאמה:
שלוש שורות בכל אחד משלושת הדטבייסים הראשונים וחמש שורות בכל אחד משני האחרונים. נשלוף אותם בעזרת פקודת Distinct על עמודת Txt
Select Distinct Txt From CIAI..T;
Select Distinct Txt From CIAS..T;
Select Distinct Txt From CSAS..T;
Select Distinct Txt From HCSAS..T;
Select Distinct Txt From #T;
ונקבל, ראו זה פלא:
בדטבייס הראשון שהוא CI_AI המערכת התייחסה לשלוש הגרסאות של Geri כאל אחת והתעלמה מהבדלי ה-Case וה-Accent. בדטבייס השני שהוא CI_AS המערכת איחדה את אלו שנבדלו רק באותיות גדולות\קטנות והפרידה את זה עם ה-é בגלל רגישותה ל-Accent. בדטבייס השלישי היא הפרידה בין שלושת ה-Geri מכיוון שהדטבייס הוא CS_AS.
כאן כדאי לציין לפרוטוקול שמה שקובע זה לא ה-Collation של הדטבייס אלא זה של העמודה בטבלה בה מדובר, אבל ברוב המקרים כשמקימים טבלה עם עמודות טקסטואליות - לא מציינים את ה-Collation וזה של הדטבייס הוא ברירת המחדל.
מה קורה עם שני הדטבייסים האחרונים?
בראשון מבין השניים שהוא CS_AS נשמרה הפרדה בין 5 הערכים וכל אחד מהם מופיע בנפרד. באחרון שהוא CI_AS שני ה-Geri שנבדלים רק באותיות גדולות\קטנות אוחדו, שני ה-גרשון ללא הניקוד (שנבדלים רק באותן נ רגילה\סופית) אוחדו (בגלל ה-CI), וזה המנוקד הופרד (בשל ה-AS).
בשלב הזה תיזרק שאלה מקהל המאזינים: מה היה קורה לו הגדרנו את עמודות Txt הנ"ל בתור NVarchar במקום Varchar?
במקרה כזה היינו צריכים להוסיף לכל מחרוזת N משמאל, וכך לא היינו מאבדים תווים שאינם נתמכים על ידי ה-Collation, אבל מבחינת ההשוואה והקיבוץ בעזרת Distinct עדיין היינו נדרשים לנושא, וגם ב-NVarchar ה-Collation קובע אם Geri שווה או לא ל-GERI.
אילו בעיות יכולות לצוץ?
בואו נסתכל על השליפה הבאה:
Select *
From CIAI..T T1
Inner Join CIAS..T T2
On T1.Txt=T2.Txt;
המערכת אינה יודעת כיצד להשוות בין עמודות להן Collation-ים שונים: האם ה-Accent הוא Sensitive או Insensitive?
הבעייה הזו קורית לא פעם כשמדובר בשימוש בטבלאות זמניות כאשר ה-Collation של tempdb שונה מזה של הדטבייס בו עובדים.
יש לבחור ב-Collation מתאים, ואחת האפשרויות הנוחות היא להשתמש בזה של הדטבייס בו נמצאים באופן הבא:
Use CIAI;
Select *
From CIAI..T T1
Inner Join CIAS..T T2
On T1.Txt Collate Database_Default=T2.Txt Collate Database_Default;
Use CIAs;
Select *
From CIAI..T T1
Inner Join CIAS..T T2
On T1.Txt Collate Database_Default=T2.Txt Collate Database_Default;
התוצאות שהתקבלו הן שונות: בראשון ה-Collation הוא מתירני CI_AI ולכן כל אחד מתאים לכל אחד וקיבלנו מכפלה קרטזית. בשני ה-Collation הוא CI_AS ולכן ב-Accent נדרשה התאמה מלאה והתקבלו פחות תוצאות.
כשנעזרים ב-Collate Database_Default יש להפעיל את זה על עמודות מדטבייסים אחרים שה-Collation שלהם שונה מהמקומי. על טבלאות מקומיות שממילא ה-Collation שלהם הוא המקומי - לא חייבים.
לבסוף - הבעייה תקרה בהשוואה בין עמודות טקסטואליות, אך לא בין סוגי נתונים אחרים כמו מספרים או תאריכים.
בעיית אי התאמת ה-Collations תקרה גם בשליפות Union:
לכאורה תאמרו מה נפשך: הרי ב-Union All לא מתבצעת השוואה או Distinct והערכים מהטבלאות השונות מופיעים זה מתחת לזה; אלא שכשם שהמערכת לא הייתה מאפשרת לנו לבצע Union All לו בעמודה אחת היו תאריכים ובשנייה מספרים, כך גם לא תקבל Collation-ים שונים. מבחינתה לעמודה של הסט שנוצר אמור להיות Collation אחד!
דוגמה נוספת:
Use CIAI;
Create Table T1(Txt Varchar(20) Primary Key);
Go
Insert
Into T1(Txt)
Select Txt
From CSAS..T
Group By Txt;
יצרנו ב-CIAI טבלה כנ"ל עם Primary Key על עמודת Txt,
וניסינו לייבא נתונים מהטבלה שב-CSAS,
אלא שמ-CSAS חזרו כל מיני Geri-ים שמבחינתה הם שונים,
אך לא מבחינת CIAI שעבורה הם שונים,
והפקודה נפלה על Primary Key Violation.
מה יקרה אם נשנה את ה-Collation של דטבייס המקור?
Alter Database CIAI Set Single_User With Rollback Immediate;
Alter Database Current Collate SQL_Latin1_General_CP1_CS_AS;
Alter Database CIAI Set Multi_User;
זו הדרך לשנות Collation של דטבייס, אלא שהבעייה לא תיפתר מכיוון שה-Collation של עמודת המקור לא השתנה: לו היינו משנים את הדטבייס לפני יצירת הטבלה הבעייה הייתה נמנעת, אך לאחר שהטבלה נוצרה - היא כבר לא תלוייה בדטבייס מבחינת ה-Collation ולכן יש לשנות את העמודה:
Alter Table T1 Drop Constraint PK_T1;
Alter Table T1 Alter Column Txt Varchar(20) Collate SQL_Latin1_General_CP1_CS_AS Not Null;
Alter Table T1 Add Constraint PK_T1 Primary Key(Txt);
ולסיום, ננסה לשלוף את רשימת הדטבייסים משני דטבייסים שונים, רשימה שאמורה להיות זהה:
ניתן לראות שהפקודה הראשונה הצליחה (13 rows affected) והשנייה נכשלה.
למה? הטבלה נקראת sys.databases אבל כתבתי sys.Databases (D גדולה). ב-CIAS הנושא לא הפריע לו "כי זה אותו הדבר", אך ב-CSAS יש הבדל מבחינתו ולכן הפקודה נפלה.
לסיכום- ה-Collation משפיע על שני דברים עיקריים:
התווים הנתמכים על ידי עמודות ומשתני Varchar או Char.
ההשוואה בין עמודות טקסטואליות.
שימוש ב-NVarchar או NChar יפתור את הבעייה הראשונה ויאפשר תמיכה בכל סוגי התווים,
אך עדיין נידרש ל-Collation לצורך השוואה בין מחרוזות.