באיזה סוג משתנה טקסטואלי כדאי לבחור?

05/02/2012

מה ההבדל בין Char / NChar / Varchar / NVarchar?
מתי נבחר במשתנה התומך ביוניקוד (NChar / NVarChar) ומתי לא (Char / Varchar)?
מתי נבחר במשתנה קבוע באורכו (Char / NChar) ומתי בבעל גודל משתנה (Varchar / NVarchar)?
כיצד נדע באיזה גודל משתנה לבחור- גודל מוגבל או לא (Max)?

לרוב הצרכים המעשיים- שימוש ב-Char או ב-Varchar אמור להספיק בהנחה שה-Collation הוא עברי ואנחנו מעוניינים לכתוב בעברית ובאנגלית כולל נִיקוּד בעברית ואותיות גדולות/קטנות ב-English, אך לא בתווים לטיניים מיוחדים האופייניים לחלק מהשפות האירופאיות.
סוגי נתונים כדוגמת NChar או NVarchar עושים שימוש ב-Unicode שהוא תקן המאפשר שימוש בכל השפות במחיר הכפלת גודל השדה. כזכור- לכל תו יש ערך Ascii משלו, אלא שאותו ערך יכול לייצג אותיות שונות בשפות שונות, למשל:

Use ReportServer;

Go

 

Select Char(233);

Go

 

Use tempdb;

Go

 

Select Char(233);

Go

clip_image002

כלומר- בדטבייס הראשון שה-Collation שלו הוא Latin1_General_CI_AS_KS_WS (אינו תומך בעברית), הערך מתאים לתו é הקיים בשפות כמו צרפתית וספרדית,

ובדטבייס השני שה-Collation שלו הוא Hebrew_CI_AS (תומך בעברית וגם באנגלית אך לא בתווים מיוחדים כמו é), הערך מתאים לתו י העברי.

מי שרוצה תמיכה גם בעברית וגם בצרפתית (למשל)- יצטרך להשתמש ביוניקוד, כלומר- בסוגי נתונים כדוגמת NChar / NVarchar, כשרב-השימושיות הזו מתאפשר במחיר של הכפלת גודל הנתון ואז מספר התווים הנתמכים אינו 256 כמו ב-Ascii אלא 65536=256*256; אך כפי שציינתי- ברוב המקרים אין זה כך.

כדאי לציין פונקציות מערכת כדוגמת SP_ExecuteSQL שמקבלות פרמטרים מסוג NVarchar, גם אם קוד ה-SQL הוא באנגלית..

השימוש ב-Char (או לחילופין ב-NChar בסביבות רב לשוניות) שאורכו קבוע מתאים למקרים בהם בכל הנתונים אותו מספר תווים, או הפרש שלא יעלה על 3-4 תווים. למשל- המיקוד בארץ (5 תווים), קידומת של מספר טלפון (2-3 תווים) ועוד. במקרה זה גודל הטקסט יהיה תמיד כגודל המשתנה- גם אם הוא קצר יותר, אבל יחסך הצורך ב-Header לכל נתון בו יצויין מה אורכו.

כלומר- משתנה מסוג Varchar (או NVarchar) מקצה מקום רק לטקסט אותו הוא שומר, אולם בכל שורה (אם מדובר בעמודה מסוג Varchar) הוא ישמור מידע בגודל קבוע בנוסף לטקסט עצמו.

מכל זה אפשר להבין ש-Varchar בגודל קטן מ-5 הוא מיותר..

מתי נשתמש ב-(Varchar(Max? לכאורה, אם הגענו למסקנה שיש להשתמש ב-Varchar ו"שילמנו" את מחיר ה-Header, מה איכפת לנו אם ה-Varchar יהיה יותר גדול ואף מקסימלי בגודלו? בכל מקרה הוא ישמור את מה שצריך, וגם אם יהיה נתון בלתי צפוי באורכו- שום דבר לא ישתבש.. רבים מקצים בטבלת העובדים 50-20 תווים לשם העובד, אבל מה יקרה אם מלך ספרד יחליט לעבוד אצלנו בארגון ויתעקש שנכתוב את שמו המלא על שלל תאריו?..

זו סיבה טובה לשקול את הנושא בכובד ראש, אם כי יש פוסטים באינטרנט הטוענים שהדבר עלול לפגוע בביצועים בפעולות עדכון או הוספה..

לכל זה מצטרפים שיקולים נוספים לכאן או לכאן, למשל- כאשר משרשרים שני נתונים שאורכם המצטבר מעל 8000 תווים (4000 במקרה של NVarchar) שזה אורכו המקסימלי של Varchar שאינו Max – המחרוזת עלולה להיחתך בתו ה-8000:

Declare @S1 Varchar(4500),

        @S2 Varchar(4500),

        @S3 Varchar(Max);

 

Select  @S1=Replicate('#',4500),

        @S2=Replicate('#',4500),

        @S3=@S1+@S2;

 

Select  Len(@S1),

        Len(@S2),

        Len(@S3);

Go

 

Declare @S1 Varchar(Max),

        @S2 Varchar(Max),

        @S3 Varchar(Max);

 

Select  @S1=Replicate('#',4500),

        @S2=Replicate('#',4500),

        @S3=@S1+@S2;

 

Select  Len(@S1),

        Len(@S2),

        Len(@S3);

Go

clip_image004

(פונקציית Replicate משכפלת תו נתון מספר נתון של פעמים)

במקרה הראשון החיבור של שתי המחרוזות באורך 4500 כל אחת יצר מחרוזת באורך 8000 (שזו שגיאה),

ובמקרה השני החיבור של שתי המחרוזות יצר מחרוזת באורך 9000 (שזה נכון בזכות השימוש ב-(Varchar(Max)

מנגד יש פונקציות מערכת שאינן מקבלות (Varchar(Max אלא רק Varchar מוגבל בגודלו, למשל XP_CmdShell (הראשון יצליח והשני יכשל):

Declare @S Varchar(8000)='Ver';

Exec XP_CmdShell @S;

Go

 

Declare @S Varchar(Max)='Ver';

Exec XP_CmdShell @S;

Go

clip_image006

נימוק כבד משקל שיש לקחת בחשבון הוא נושא האינדקסים: עמודות מסוג (Varchar(Max או (NVarchar(Max לא ניתן לאנדקס. נכון שניתן לכלול אותן באינדקס על ידי האופרטור Include (במקרה של Covered Index), אך הן יכללו מבלי להיות מאונדקסות.

וכעת לדוגמאות:

Create Table T_Char_20_20(S Char(20));

Create Table T_VarChar_20_20(S VarChar(20));

Create Table T_VarChar_Max_20(S VarChar(Max));

Create Table T_Char_24_20(S Char(24));

Create Table T_VarChar_24_20(S VarChar(24));

Go

 

Insert Into T_Char_20_20 Select Replicate('#',20) From sys.messages;

Insert Into T_VarChar_20_20 Select Replicate('#',20) From sys.messages;

Insert Into T_VarChar_Max_20 Select Replicate('#',20) From sys.messages;

Insert Into T_Char_24_20 Select Replicate('#',20) From sys.messages;

Insert Into T_VarChar_24_20 Select Replicate('#',20) From sys.messages;

Go

 

DBCC ShowContig ('T_Char_20_20') With TableResults;

DBCC ShowContig ('T_VarChar_20_20') With TableResults;

DBCC ShowContig ('T_VarChar_Max_20') With TableResults;

DBCC ShowContig ('T_Char_24_20') With TableResults;

DBCC ShowContig ('T_VarChar_24_20') With TableResults;

Go

clip_image008

יצרנו 5 טבלאות, ובכל אחת עמודת טקסט ו-97526 שורות בנות 20 תווים כל אחת:

T_Char_20_20, T_Char_24_20 – עמודת טקסט מסוג Char, באחת בגודל 20 ובאחרת בגודל 24.

T_VarChar_20_20, T_VarChar_24_20 – עמודת טקסט מסוג VarChar, באחת בגודל 20 ובאחרת בגודל 24.

T_VarChar_Max_20 – עמודת טקסט מסוג VarChar בגודל Max.

וכעת על פי הסדר בצילום המסך:

הטבלה הקטנה ביותר היא T_Char_20_20 ש"תפורה" בדיוק לפי המידה, גודלה 350 Pages, וגודל כל רשומה 27 Bytes בלבד.

הטבלה השניה היא מסוג (Varchar(20, היא מחזיקה אותו מידע כמו הקודמת, אך בגלל הצורך ב-Header בכל שורה היא גדולה יותר- 399 Pages וכל רשומה 31 Bytes.

הטבלה השלישית היא מסוג (Varchar(max אבל מבחינת הגודל היא זהה לקודמת (399 Pages, 31 Bytes): העובדה שבחרנו ב-Max ולא ב-20 לא עלתה לנו בנפח אחסון.

הטבלה הרביעית היא מסוג (Char(24 למרות שהיא מחזיקה טקסטים בגודל 20. הדבר כרוך בבזבוז של מקום מיותר, וכתוצאה מכך הגודל שלה זהה לזה של (Varchar(20 וכפי שתיכף נראה – גם לזה של (Varchar(24. ה-Header של ה-Varchar שקול בגודלו ל-4 תווים.

הטבלה החמישית מסוג (Varchar(24, ואין הבדל בגודל בינה לבין (Varchar(20 ו-(Varchar(Max (אני מזכיר שבכל הטבלאות נשמרו טקסטים בגודל 20 תווים).

הערה- כאשר בעמודה בגודל 24 נשמר טקסט בגודל 20, ארבעת התווים המיותרים גורמים לא רק לעלות אחסון מיותרת, אלא עלולה לגרום לשיבושים בעת שירשור מחרוזות, וראוי "לקצץ" את הזנב באמצעות הפונקציה RTrim.

לסיכום הבדיקה בארבע הטבלאות: אין הבדל מבחינת האחסון בין (Varchar(Max ו-Varchar רגיל,

ו-Char בגודל קבוע חסכוני ביחס ל-Varchar כל עוד מדובר במחרוזות בגודל שלו או קטנות בפחות מ-4 תווים.

אני אישית הייתי משתמש בו רק כשהמחרוזות באורך קבוע (למשל- מיקוד): החסכון הזניח בכמה בייטים אינו שווה את ההתעסקות בלקצר את המחרוזות מהתווים הריקים המתווספים אליהם.

לסיום, אינדקסים- נוסיף לטבלה עם ה-(Varchar(Max עמודת Identity וננסה לאנדקס אותה בשלוש דרכים:

אינדקס על עמודת ID,

אינדקס על ID ועל S (עמודת ה-Varchar),

אינדקס על ID הכולל (Include) את S.

Alter Table T_VarChar_Max_20 Add ID Int Identity;

Go

 

Create Index Idx1_T_VarChar_Max_20 On T_VarChar_Max_20(ID);

Go

 

Create Index Idx2_T_VarChar_Max_20 On T_VarChar_Max_20(ID, S);

Go

 

Create Index Idx3_T_VarChar_Max_20 On T_VarChar_Max_20(ID) Include (S);

Go

image

הודעת השגיאה היא לגבי הנסיון ליצור אינדקס על ID ועל S,

וניתן להבין מכך של-(Varchar(Max לאניתן ליצור אינדקס, ולכן עמודות תיאור (שם ישוב, שם פרטי וכו’) ראוי שיהיו מסוג (Varchar(n כלשהו.

לסיכום:

1. נפח האחסון של Varchar רגיל ו-(Varchar(Max – זהה.

2. השימוש ב-Char מומלץ כשכל הנתונים בעלי אורך זהה.

3. לא ניתן לאנדקס (Varchar(Max, אלא רק לכלול (Include) אותו.

4. שירשור שתי מחרוזות מסוג Varchar רגיל שאורכן המצטבר מעל 8000 יצור מחרוזת באורך 8000.

5. NChar / NVarchar הם החלופות היוניקודיות של Char / Varchar וחלים עליהם אותן מגבלות פחות או יותר.

6. האורך המקסימלי של NChar / NVarchar (שאינו Max) הוא 4000 מכיוון שנפח האחסון של כל תו- כפול.

7. משתנים יוניקודיים תומכים בכל השפות, ללא תלות ב-Collation.

8. יתכן שעדכון או הוספה לעמודות (Varchar(Max איטיים יותר. לא בדקתי.

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *