February 2012 - Posts
פוסט זה עוסק בדרך בה ניתן לגבות את הדטבייסים ולשחזר אותם בכפוף לאופן בו הם מוגדרים.
אנחנו אמורים לכלכל מעשינו כך שכשקורית תקלה במערכת המידע- נהיה ערוכים לקראתה במידת האפשר, ונהיה מסוגלים להחזיר את המערכת לפעולה תקינה במהירות סבירה. כמובן שלכל דבר יש מחיר במשאבים, ביצועים, עבודה וכו'; ומנגד הצרכים יכולים לנוע בין מערכות קריטיות שאסור בשום פנים ואופן שיושבתו לבין כאלו שחשיבותן שולית ואין צורך להקדיש להן תשומת לב מיוחדת. לצורך כך יש טכנולוגיות שונות שבהן מככבים מושגים כמו High Availability (מניעת השבתה של המערכת), Data Recovery (התאוששות מהירה במקרה של תקלה) ועוד.
Recovery Models עוסק בגיבויים ושיחזורים ברמת הדטבייס: זה אינו פתרון ברמת השרת, וגם לא ברמת הטבלה.
כשיוצרים דטבייס ניתן להגדיר לו באופן כללי אחד משניים:
Simple Recovery Model – במקרה זה יש לבצע לדטבייס גיבוי מלא או דיפרנציאלי אחת לזמן מה; וניתן לשחזר במקרה של תקלה לאחת מנקודות הגיבוי.
Full Recovery Model – במקרה זה יש לבצע לדטבייס גיבוי מלא או דיפרנציאלי, ובנוסף לגבות את הלוג; וניתן לשחזר במקרה של תקלה לכל נקודת זמן בין כל גיבוי מלא לגיבוי הלוג האחרון שלו (את היכולת האחרונה אינני מדגים בפוסט הזה).
לפרוטוקול, קיימת אופציה שלישית – Bulk Logged – שהיא גרסה של Full שנועדה להתמודד בקלות עם משימות כמו Bulk Insert, Insert Into, יצירת אינדקסים ועוד, במחיר של ויתור על אופציית השיחזור לכל נקודת זמן. כלומר- אופציה זו מאפשר לגבות את הלוג, אך לשחזר אותו במלואו ולא עד לנקודה מסויימת.
גיבוי דיפרנציאלי הוא גיבוי של השינוי מאז הגיבוי המלא האחרון, ולשיחזור יש צורך בגיבוי מלא אחד ובגיבוי דיפרנציאלי אחד.
קובץ הלוג שיש לו תפקיד נכבד ב-Full ממלא שני תפקידים במקביל:
1. כל טרנזקציה נכתבת אליו וקיימת בו כל עוד היא פתוחה.
2. במודל Full, הטרנזקציות שהסתיימו נשארות בלוג עד שמתבצע גיבוי.
מכל זה אפשר להבין שב-Simple הלוג גדל רק בגלל טרנזקציות פתוחות, אבל ב-Full גם בגלל טרנזקציות סגורות שטרם גובו (הלוג מתרוקן רק לאחר הגיבוי).
במקרה של שיחזור Full יש צורך בגיבוי מלא, בגיבוי דיפרנציאלי אחריו (אופציונאלי), ובכל גיבויי הלוג לאחר הגיבוי המלא/הדיפרנציאלי עד לנקודת הזמן אליה רוצים לשחזר.
סיכום ביניים: במקרה "רגיל" (עד כמה שתקלה המחייבת שיחזור יכולה להיות רגילה..) ב-Simple יש לשחזר את הגיבוי המלא האחרון והדיפרנציאלי האחרון שאחריו. כל מה שקורה לאחר הגיבוי הדיפרנציאלי האחרון- הולך לאיבוד.
ב-Full יש לשחזר את הגיבוי המלא האחרון, את הדיפרנציאלי האחרון, ואת כל גיבויי הלוג מאז הדיפרנציאלי האחרון. גם כאן כל מה שקורה לאחר הגיבוי האחרון הולך לאיבוד, אבל אם יש הפרדה בין הדיסקים של ה-Data (קבצי mdf) והלוג (קבצי ldf) – יש סיכוי שנוכל ליצור קובץ לוג חדש אם קרס הדיסק של הלוג (כאשר ב-Data נשמר כל המידע) או לגבות את הלוג ולשחזר בעזרתו ובעזרת שאר הגיבויים את המערכת עד לנקודת הזמן של התקלה אם הדיסק של ה-Data קרס. ב-Simple לא תצמח לנו כל תועלת מקובץ הלוג אם קובץ ה-Data אבד.
הדגמה: להלן נדגים מספר מצבי קטסטרופה לדטבייס ונראה כיצד להתמודד איתם. למותר לציין שלא מתרלגים זאת על שרת הייצור, אבל מי שכן- לא אחסום אותו בדישו..
ניצור דטבייס חדש ב-Simple Recovery, וניצור בו טבלה:
Use master;
Go
If DB_ID('MyDatabase') Is Not Null Drop Database MyDatabase;
Go
Create Database MyDatabase;
Go
Alter Database MyDatabase Set Recovery Simple;
Go
Create Table MyDatabase.dbo.MyTable(MyDate DateTime Default GetDate() Primary Key Clustered,
MyText Varchar(Max));
Go
נזין לטבלה שורה אחת, נגבה את הדטבייס, ונזין שורה נוספת (אינה נכללת בגיבוי):
Insert
Into MyDatabase.dbo.MyTable(MyText)
Select 'Simple Recovery Database was created';
Backup Database MyDatabase To Disk='C:\Tmp\MyBackup01_Simple.bak';
Go
Insert
Into MyDatabase.dbo.MyTable(MyText)
Select 'Backup 01 Simple has been done';
Select *
From MyDatabase.dbo.MyTable;
את מיקומם של הקבצים על פי טבלת המערכת sys.sysfiles כדאי להעתיק ולשמור בצד להמשך (לא בטוח שהוא זהה לזה שאצלי).
כעת ניזום תקלה בה קובץ הלוג הולך לאיבוד בגלל תקלה:
נעצור את ה-SQL Server על ידי קליק ימני ב-SSMS, בחירה ב-Stop ואישור:
ניגש למחיצה בה הקבצים נמצאים, ואת קובץ MyDatabase_Log.ldf נמחוק ברשעות (עדיף ליתר בטחון להעביר למחיצה צדדית), ונפעיל שוב את ה-Service של SQL Server, הפעם על ידי קליק ימני כנ"ל ו-Start.
כדאי לשים לב שלאחר שמבצעים Restart לשרת- יש להתחבר שוב בחלון ה-Query מכיוון שהוא התנתק כשה-Service ירד.
כעת אנחנו עלולים לגלות "לחרדתנו" שהדטבייס נדפק ולא ניתן לגשת אליו (יתכן והמערכת תיצור בעצמה קובץ לוג חדש ולא תהיה כל בעייה ויתכן שלא נוכל לגשת לדטבייס ולשלוף מהטבלה שיצרנו). במקרה הראשון נמהר לקנות כרטיס הגרלה של מפעל הפייס (Today is our lucky day נסביר לאראלה כשתתקשר אלינו), ובמקרה השני נריץ את הפקודות הבאות שיתקנו את הטעון תיקון:
Alter Database MyDatabase Set Emergency;
Alter Database MyDatabase Set Single_User;
DBCC CheckDB (MyDatabase,Repair_Allow_Data_Loss);
Alter Database MyDatabase Set Multi_User;
Alter Database MyDatabase Set Online;
מה קורה אם קובץ הנתונים MyDatabase.mdf אובד? במקרה זה נשחזר מהגיבוי את מה שניתן, וכל מה שקרה לאחר הגיבוי- ירד לטמיון:
If DB_ID('MyDatabase') Is Not Null Drop Database MyDatabase;
Go
Restore Database MyDatabase From Disk='C:\Tmp\MyBackup01_Simple.bak';
Go
Select *
From MyDatabase.dbo.MyTable;
ניתן לראות שהשורה השנייה שהזנו לטבלה- איננה; יהי זכרה ברוך!
נשנה כעת את הדטבייס ל-Full Recovery:
Alter Database MyDatabase Set Recovery Full;
Go
נוסיף שורה לטבלה ונבצע גיבוי מלא:
Insert
Into MyDatabase.dbo.MyTable(MyText)
Select 'The Database was altered to Full Recovery';
Backup Database MyDatabase To Disk='C:\Tmp\MyBackup02_Full.bak';
Go
נוסיף עוד שורה לטבלה ונבצע גיבוי דיפרנציאלי (של מה שהשתנה מאז הגיבוי האחרון):
Insert
Into MyDatabase.dbo.MyTable(MyText)
Select 'Backup 02 Full has been done';
Backup Database MyDatabase To Disk='C:\Tmp\MyBackup03_Differential.bak' With Differential;
Go
נוסיף עוד שורה לטבלה ונבצע גיבוי לוג (של מה שנכתב בלוג מאז הגיבוי הדיפרנציאלי/המלא האחרון):
Insert
Into MyDatabase.dbo.MyTable(MyText)
Select 'Backup 03 Differential has been done';
Backup Log MyDatabase To Disk='C:\Tmp\MyBackup04_Log.bak';
Go
ולבסוף נוסיף עוד שורה אחת שאינה מגובה בינתיים:
Insert
Into MyDatabase.dbo.MyTable(MyText)
Select 'Backup 04 Log has been done';
המצב כרגע:
Select *
From MyDatabase.dbo.MyTable;
בטבלה יש 5 שורות, כשכל אחת משלוש האחרונות נכתבה לאחר אחד הגיבויים (שוב- האחרונה אינה מגובה).
כעת נתחיל להתעלל מעט במערכת- קודם כל נמחק את קובץ הלוג כמו קודם: נבצע Stop למערכת, נמחק מהמחיצה את MyDatabase_Log.ldf (או נעתיק למחיצה צדדית), ונפעיל שוב את השרת על ידי Start.
הפעם מזלנו כבר לא יאיר לנו פנים- בקובץ הלוג יש מידע חשוב (שורה 5 בטבלה), ולא ניתן לגשת לדטבייס.
נריץ את הסקריפט שהוצע קודם ונקווה לטוב:
Alter Database MyDatabase Set Emergency;
Alter Database MyDatabase Set Single_User;
DBCC CheckDB (MyDatabase,Repair_Allow_Data_Loss);
Alter Database MyDatabase Set Multi_User;
Alter Database MyDatabase Set Online;
Select *
From MyDatabase.dbo.MyTable;
מכיוון שכל המידע היה בקובץ ה-Data והוא לא אבד- כל חמש השורות בטבלה.
במקרה אמת יש לבצע בהקדם גיבוי מלא!
מקרה חמור יותר- מה קורה אם קובץ ה-Data אובד?
נעצור את המערכת על ידי Stop, נעביר למחיצה צדדית את קובץ MyDatabase.mdf, ונפעיל על ידי Start.
כעת הבעייה קצת יותר חמורה- חלק מהמידע נמצא בקבצי הגיבוי וחלק בקובץ הלוג ששרד, ויש לפעול כך (כאשר הפעם מבצעים את כל הפעולות כחלק מתרגול של טיפול בתקלה ולא של ביום תקלה כמו קודם, אז לא להתבלבל):
1. לעצור את ה-Service על ידי Stop, להעביר את קובץ הלוג למחיצה צדדית (לא למחוק!) ולהפעיל שוב על ידי Start.
2. לבצע Drop לדטבייס שלא נותרו לו קבצים (ה-Data נמחק בשל תקלה והלוג הועבר הצידה כחלק מהטיפול בתקלה) וניצור מחדש:
If DB_ID('MyDatabase') Is Not Null Drop Database MyDatabase;
Go
Create Database MyDatabase;
Go
3. לעצור שוב את ה-Service על ידי Stop, למחוק את שני הקבצים החדשים שנוצרו MyDatabase.mdf, MyDatabase_Log.ldf, להחזיר את קובץ הלוג הקודם ששרד את התקלה (קובץ ה-Data "אבד" כזכור), ולהפעיל את המערכת על ידי Start (במציאות כדאי לשמור במקביל עותק שלו).
4. לגבות את קובץ הלוג (למרות שהדטבייס תקול כי יש רק קובץ לוג):
Backup Log MyDatabase To Disk='C:\Tmp\MyBackup05_Log.bak' With No_Truncate;
Go
5. לבצע Drop לדטבייס ולמחק את קובץ הלוג לאחר מכן, כדי שנוכל לשחזר בצורה מסודרת.
If DB_ID('MyDatabase') Is Not Null Drop Database MyDatabase;
Go –-למחוק כעת את קובץ הלוג
6. לשחזר את הדטבייס בעזרת ארבעת הגיבויים הרלוונטיים שיש כעת: הגיבוי המלא האחרון, הגיבוי הדיפרנציאלי האחרון, וכל גיבויי הלוג מאז הגיבוי המלא/הדיפרנציאלי האחרון (במקרה שלנו שניים- אחד לפני התקלה ואחד לאחר התקלה):
Restore Database MyDatabase From Disk = 'C:\Tmp\MyBackup02_Full.bak' With File = 1, NoRecovery;
Go
Restore Database MyDatabase From Disk ='C:\Tmp\MyBackup03_Differential.bak' With File = 1, NoRecovery;
Go
Restore Log MyDatabase From Disk = 'C:\Tmp\MyBackup04_Log.bak' With NoRecovery;
Go
Restore Log MyDatabase From Disk = 'C:\Tmp\MyBackup05_Log.bak' With Recovery;
Go
וכעת רק נותר לוודא שכל חמש השורות בטבלה:
Select *
From MyDatabase.dbo.MyTable;
![clip_image010[1]](http://blogs.microsoft.co.il/blogs/gerireshef/clip_image0101_thumb_3CE83DA2.jpg "clip_image010[1]")
הידד- הכל בסדר!
לסיום אני אדגים מה קורה אם הגרוע מכל קורה- נשארים ללא קבצי Data ולוג, ללא שרת, ללא גיבויים וללא DBA: אני אשים את הגיבויים בתיק הגב שלי, אקח את השרת ביד, ואקפוץ מהקומה העשירית:
אההההההה..... .... .. בום!
(עכשיו נראה אותם מסתדרים בלעדי)
סיכום: כשמשתמשים ב-Full Recovery יש סיכוי טוב יותר שנצליח לשחזר את הדטבייס במלואו אם אחד משני הקבצים (Data ולוג) שרד, ולכן כדאי במצב כזה להפריד בינהם פיזית. יש לכך גם יתרונות מבחינת הביצועים (בעת ביצוע Commit המערכת עובדת במקביל על שני הקבצים), אבל בראש ובראשונה מבחינת יכולת השחזור.
כשמשתמשים ב-Simple Recovery גם כדאי להפריד פיזית בין הקבצים, אבל רק משיקולי ביצועים: נוכל להתאושש באופן מלא מתקלה רק אם קובץ ה-Data שרד. בכל מקרה אחר- נצטרך לסמוך על מה שיש בגיבויים.
כמובן שלכל דבר יש מחיר והשימוש ב-Full Recovery מחייב נפח אחסון גדול יותר לקבצי הלוג.
לפני כשבועיים כתבתי פוסט לגבי שליפה אקראית של שורות מטבלה, ולמרבה הצער היו שתי אפשרויות שלכל אחת חסרונות משלה:
1. שימוש במיון בעזרת New_ID שמחזיר את התוצאה המבוקשת אבל כרוך במיון של טבלה שעלולה להיות גדולה מאוד.
2. שימוש בכלים מובנים של SQL Server ש”עולים” פחות אבל מחזירים מספר שורות שלא ניתן לצפות מראש במדוייק.
גרסת 2012 שעומדת להשתחרר ב-07/03/2012 כוללת את אופציית ה-Paging שיכולה לסייע לנו במקרה זה: נשלוף 20 שורות (או כל מספר אחר) החל מנקודה אקראית בטבלה. המחיר יהיה פניה לטבלת המערכת sys.sysindexes כדי לברר כמה שורות יש בטבלה, ו-Scan לטבלה ממנה שולפים עד לנקודה בה נשלוף רצף של 20 שורות.
לא פתרון מושלם – נקבל 20 שורות רצופות שעלולות להיות דומות או מאותו תאריך – אבל אופצייה מעניינת נוספת שכדאי להתחשב בה.
ניצור טבלה בת כ-230,000 שורות לצורך ההדגמה:
Use tempdb;
Go
If Object_ID('Messages') Is Not Null Drop Table Messages;
Go
Select *
Into Messages
From sys.messages;
וכעת לקוד:
Declare @Frm Int,
@Cnt Int=20;
Select Top (1) @Frm=1+Rand()*(rowcnt-@Cnt) From sys.sysindexes Where id=Object_ID('Messages');
Print @Frm;
Select *
From Messages
Order By GetDate()
Offset @Frm Rows Fetch Next @Cnt Rows Only;
ממספר השורות החסרנו 20 כדי שלא נתחיל לשלוף מנקודה בה כבר לא יניו עוד 20 שורות,
את המספר כפלמו במספר אקראי בין 0 ל-1,
והוספנו 1 למקרה שהמספר האקראי יהיה 0.
פקודת ה-Print באמצע היא לצורך בקרה וניתן לוותר עליה.
מי שרוצה את הכל בשליפה פשוטה אחת, ללא Declare למשתנים ופניה בנפרד לטבלת המערכת ולטבלת היעד, יכול גם כך:
Select *
From Messages M
Order By GetDate()
Offset Cast((Select 1+Rand()*(rowcnt-20) From sys.sysindexes Where id=Object_ID('Messages')) As Int) Rows Fetch Next 20 Rows Only;
במקרה זה חישוב נקודת ההתחלה (offset) מתבצע בשאילתת משנה.
בשני המקרים היה מדובר בשתי פעולות Scan זולות יחסית על שתי טבלאות,
ומהפלטים ניכר גם החסרון של השיטה הזו: פעם אחת קיבלנו שורות שכולן בפורטוגלית, ופעם אחת שורות שכולן ביוונית..
Ctrl X & Ctrl V כנראה לא יעבוד, אבל השימוש באופרטור Output יספק את הסחורה.
ניצור להדגמה טבלה אחת עם כמה רבבות שורות, וטבלה שניה ריקה אליה יש להעביר את הנתונים מהראשונה:
Use tempdb;
Go
If Object_Id('messages','U') Is Not Null Drop Table messages;
Go
Select *
Into messages
From sys.messages;
If Object_Id('messages_Old','U') Is Not Null Drop Table messages_Old;
Go
Select *
Into messages_Old
From sys.messages
Where 0=1;
שימוש ב-Select Into בצירוף תנאי שאינו מתקיים כמו 0=1 זו דרך פשוטה להעתיק סכימה של טבלה קיימת במקום להסתבך עם Create Table (שימושי בעיקר בהדגמות- במציאות עדיפה הדרך הארוכה והמסודרת ולא לסמוך על ברירות המחדל של המערכת).
וכעת נבטל מ-Message על ידי Delete ונכניס ל-Message_Old על ידי Output:
Delete
From messages
Output Deleted.*
Into messages_Old(message_id,language_id,severity,is_event_logged,text);
זה הכל? כן זה הכל!
יש משהו פשוט יותר? אם מדובר רק בלרוקן טבלה אחת ולמלא את השניה אז כן (במציאות נמחק את השורות הישנות מהשנים הקודמות מהטבלה הראשונה ונכניס לטבלה השניה בה נצברו כבר נתונים ממחיקות קודמות), וננצל פטנט לא כל כך מוכר כדי לחזור למצב הפתיחה בו הראשונה מלאה והשניה ריקה:
Alter Table messages_Old Switch To messages;
Go
מי שאינו מאמין- שיבדוק לפני ואחרי כמה שורות יש בכל טבלה:
Select Count(*) [messages] From messages;
Select Count(*) [messages_Old] From messages_Old;
כאשר מדובר בהעברה של מיליוני שורות – עדיף לעשות זאת במנות ולא בבת אחת כדי לא "לפוצץ" את הלוג ולתקוע את המערכת (כולל Rollback לאחר שנתייאש..), למשל כך:
Select @@RowCount; --מה שלא יהיה ערכו- כעת הוא מאותחל ל1
While @@RowCount<>0
Delete Top (10000)
From messages
Output Deleted.*
Into messages_Old(message_id,language_id,severity,is_event_logged,text);
משתנה המערכת RowCount@@ מציין כמה שורות נשלפו/נמחקו/נכנסו/השתנו בפעולה האחרונה, וה-Select הראשון נועד "לאתחל" אותו לערך 1.
במקרה זה המחיקה וההוספה הן טרנזקציה אחת גם אם לא ציינו זאת, וזה בוודאי יותר יעיל ובטוח מאשר לבצע פעם אחת Scan על טבלת המקור כדי להעתיק לטבלת היעד, לאחר מכן עוד Scan כדי למחוק, ולא לשכוח לציין במפורש שזו טרנזקציה על ידי Begin Tran & Commit כדי למנוע אי נעימויות במקרה של תקלה.
מהם Indexed Views? כיצד יוצרים אותם? מה התועלת בהם? אילו יתרונות, מגבלות, חסרונות וחלופות יש להם?
ולבסוף- האם ניתן ליצור יותר מ-Clustered Index אחד על טבלה? (ספויילר: התשובה היא "לא, אבל..")
מגרסת 2005 יש אפשרות ליצור אינדקסים על Views, או ליתר דיוק- ליצור Views כך שיהיה ניתן ליצור עליהם אינדקסים.
ניצור לצורך ההדגמה טבלה, ניצור עליה Clustered Index, ניצור View על הטבלה, וניצור גם עליו Clustered Index אבל שונה:
Use tempdb;
Go
If Object_Id('V_messages','V') Is Not Null Drop View V_messages;
Go
If Object_Id('T_messages','U') Is Not Null Drop Table T_messages;
Go
Select *
Into T_messages
From sys.messages
Go
Alter Table T_messages Add Constraint PK_T_messages Primary Key Clustered (message_id,language_id);
Go
Create View dbo.V_messages With SchemaBinding As
Select message_id,
language_id,
severity,
is_event_logged,
text
From dbo.T_messages;
Go
Create Unique Clustered Index IX_V_messages On dbo.V_messages(language_id,message_id);
Go
Select *
From T_messages;
Select *
From V_messages;
מעיון בקוד ובפלט כבר עולות מספר נקודות שיש לתת עליהן את הדעת:
1. ביצירת ה-View יש לציין בפורש את שמות העמודות, את הסכימה של הטבלה, ולהשתמש באופרטור With SchemaBinding שגורם לביצוע "נעילה" כך שלא ניתן לשנות את הטבלה באופן שאינו מתחשב ב-View. למשל- לא ניתן לבצע Drop לטבלה מבלי לבצע קודם Drop ל-View (בדרך כלל זה אפשרי לדאבוננו).
2. יש מגבלות נוספות לגבי המותר והאסור ב-Indexed Views, ולפירוט כדי לעיין ב-Books On Line או כאן.
3. הפלטים של השליפות מהטבלה וה-View נראים זהים מפני ששניהם מתבססים על ה-Clustered Index של הטבלה. בהמשך נראה שכשיש סיבה להשתמש ב-Clustered Index של ה-View – שניהם עשויים להשתמש בו.
4. ניתן ליצור עוד אינדקסים על ה-View, אבל הראשון חייב להיות Clustered Index.
כפי שניתן לשער- המערכת שומרת למעשה את הטבלה פעמיים- פעם בתור טבלה ופעם בתור View עם Clustered Index. כלומר- יצירת ה-Clustered Index על ה-View גורמת לכך שהנתונים ישמרו שוב בנפרד, ובהמשך שני העותקים יתוחזקו במקביל (הווה אומר- יש לזה גם מחיר):
Exec SP_SpaceUsed T_messages;
Exec SP_SpaceUsed V_messages;
ניתן לראות שלשני האובייקטים יש נתונים שתופסים מקום בנפח דומה פחות או יותר. מי שייצור – לצורך התרגול – View נוסף כנ"ל בשם אחר וללא אינדקס – יראה שהנפח שלו הוא 0 קילובייט. אנשי אוראקל קוראים לזה Materialized View, כלומר View שאינו רק משפט Select אלא הערכים שלו נשמרים בנפרד, אך הוא אינו טבלה כי אינו עצמאי אלא מסונכרן ותלוי בטבלה עליה נוצר.
נבדוק כעת ארבע שליפות מבחינת ה-Execution Plans שלהן:
Select *
From T_messages
Order By message_id,
language_id;
Select *
From T_messages
Order By language_id,
message_id;
Select *
From V_messages
Order By language_id,
message_id;
Select *
From V_messages (NoExpand)
Order By language_id,
message_id;
הערת הסתייגות: את ה-Execution Plan הזה צפויים לראות בעלי גרסאות Developer ו-Enterprise. בעלי גרסת Standart יקבלו את ה-Execution Plans המצופים רק בשאילתות הראשונה והאחרונה.
ניתן לבדוק את הגרסה כך:
Select ServerProperty('Edition') [Edition];
Go
בשליפה הראשונה מהטבלה- המערכת נעזרה כצפוי ב-Clustered Index שלה מכיוון שהשליפה ממויינת בדיוק כמוהו.
בשליפה השניה מהטבלה- המערכת ידעה לנצל את האינדקס של ה-View שהתאים למיון שלה.
בשליפה השלישית מה-View – המערכת השתמשה "כצפוי" באינדקס שלו (המרכאות מפני שזה עדיין לא כה ברור מאליו).
השליפה הרביעית היא לבקרה- השימוש ב-Hint NoExpand ב-Indexed View גורמת לכך שהמערכת תשתמש באינדקס שהוגדר עליו גם אם אין צורך בכך. בגרסאות שאינן Developer או Enterprise – זו הדרך היחידה להשתמש באינדקסים על Views.
האם תמיד אלו יהיו פני הדברים? ניצור מחדש את האובייקטים אבל בפחות שורות (Top 1000):
Use tempdb;
Go
If Object_Id('V_messages','V') Is Not Null Drop View V_messages;
Go
If Object_Id('T_messages','U') Is Not Null Drop Table T_messages;
Go
Select Top 1000 *
Into T_messages
From sys.messages
Go
Alter Table T_messages Add Constraint PK_T_messages Primary Key Clustered (message_id,language_id);
Go
Create View dbo.V_messages With SchemaBinding As
Select message_id,
language_id,
severity,
is_event_logged,
text
From dbo.T_messages;
Go
Create Unique Clustered Index IX_V_messages On dbo.V_messages(language_id,message_id);
Go
וכעת נבדוק שוב את ה-Execution Plans:
בשליפה הראשונה בה בכל מקרה היה כדאי להשתמש ב-Clustered Index של הטבלה ובשליפה הרביעית בה השתמשנו ב-NoExpand – התוכנית זהה לקודמת; אבל בשליפות השניה והשלישית לא, והמערכת מעדיפה לשלוף מהטבלה ולמיין למרות שזה הרבה פחות יעיל: ניתן לראות את המחירים היחסיים של השליפות באחוזים (16%,34%,34%,16% בהתאמה).
מה הסיבה לכך? המערכת הרי אמורה לבחור באפשרות הכי זולה!
התשובה נמצאת ב-Properties של ה-Select המתקבל מהעמדת העכבר על ה-Select ב-Execution Plan: כשהמחיר נמוך מ-5 (במקרה זה 0.051292) המערכת מוותרת מראש על שימוש באינדקס של ה-View מכיוון שהרווח ממנו זניח לאור עלותה הנמוכה יחסית של השליפה. נקודת הגבול המדוייקת משתנה מהתקנה להתקנה, ואצלי למשל (SQL Server 2005 Enterprise Edition SP2) היא בין 51972 ו-51973 שורות.
סיכום ביניים: אם אנחנו בגרסה המתאימה והטבלה מספיק גדולה – נוצר מצב בה יש לה באופן מעשי שני Clustered Indexes, והמערכת יכולה להחליט באיזה להשתמש בהתאם לשליפה, וזה כמובן יתרון גדול מאוד; מה גם שניתן ליצור על פי הצורך עוד Views ולכל אחד מהם Clustered Index משלו ולתת למערכת להחליט באיזה להשתמש בהתאם לנסיבות, כמובן – בכפוף לכך שלוקחים בחשבון את המחיר מבחינת אחסון ותחזוקה.
יחד עם זאת, לאור המגבלות השונות והעובדה שצריך ליצור View חדש עבור כל Clustered Index, היה עדיף להשתמש במקרים כאלו (שליפה פשוטה או מפולטרת) בפתרון נוח ופשוט יותר – Covered Index על הטבלה והעמודות היחודיות (Uniques), ו-Include לכל השאר: זה מתנהג בדיוק כמו Clustered Index (Indexed Views יש מגרסת 2005 ו-Covered Indexes מגרסת 2008).
אם כך- למה בכל זאת צריך Indexed Views? כשיוצרים שליפה עם Group By או עם Inner Join – זה הכלי היחידי המסוגל לעשות זאת (בכפוף לכל המגבלות):
If Object_Id('V_messages_2','V') Is Not Null Drop View V_messages_2;
Go
Create View dbo.V_messages_2 With SchemaBinding As
Select message_id,
Count_Big(*) Cnt
From dbo.T_messages
Group By message_id;
Go
Create Unique Clustered Index IX_V_messages_2 On dbo.V_messages_2(message_id);
Go
והמערכת גם משתמשת באינדקס החדש שיצרנו:
Select *
From V_messages_2;
Select message_id,
Count(*) Cnt
From dbo.T_messages
Group By message_id;
הנה מקור לטעויות: מנסים לעדכן טבלה אחת על פי השניה, בשעה שלכל שורה בראשונה יש מספר שורות מתאימות בשניה.
עוד לפני שנראה מה קורה- ברור שלא זה הדרך: בין אם הפקודה תעבוד ובין אם לא- כיצד נדע איך הטבלה הראשונה תתעדכן?
לאחר שנראה מה קורה נבין שכל שורה תתעדכן פעם אחת באופן אקראי, וכך לא עובדים.
דוגמה:
Use tempdb;
Go
If Object_Id('MyTbl1','U') Is Not Null Drop Table MyTbl1;
Go
Create Table MyTbl1(ID Int,
Txt Varchar(50));
Go
Insert
Into MyTbl1(ID)
Select 1 Union All
Select 2 Union All
Select 3;
If Object_Id('MyTbl2','U') Is Not Null Drop Table MyTbl2;
Go
Create Table MyTbl2(ID Int,
Txt Varchar(50) Not Null);
Go
Insert
Into MyTbl2(ID,Txt)
Select 1,'a' Union All
Select 1,'b' Union All
Select 1,'c' Union All
Select 2,'aa' Union All
Select 2,'bb' Union All
Select 2,'cc' Union All
Select 3,'aaa' Union All
Select 3,'bbb' Union All
Select 3,'ccc';
Select * From MyTbl1;
Select * From MyTbl2;
כפי שניתן לראות- לכל ID מהטבלה הראשונה יש שלוש שורות בטבלה השניה, וכעת ננסה לעדכן:
Update MyTbl1
Set Txt=MyTbl2.Txt
From MyTbl1
Inner Join MyTbl2
On MyTbl1.ID=MyTbl2.ID;
Select *
From MyTbl1;
אולי - חולף בנו הרהור - כל שורה התעדכנה שלוש פעמים, וזה הערך האחרון שנשאר?
ננסה אם כך לצבור בכל שורה בטבלה הראשונה את הערכים שהוכנסו לתוכה-
נאפס את עמודה Txt בטבלה הראשונה ונבדוק:
Update MyTbl1
Set Txt=Null;
Update MyTbl1
Set Txt=IsNull(MyTbl1.Txt,'')+MyTbl2.Txt
From MyTbl1
Inner Join MyTbl2
On MyTbl1.ID=MyTbl2.ID;
Select *
From MyTbl1;
אין כל שינוי.
מי שטיפה יתעמק- יוכל לראות שכל שורה קיבלה באופן מעשי את הערך הראשון שהגיע אליה, אבל פורמלית מדובר בבחירה אקראית של המערכת.
מי שעדיין יש לו ספק- יוכל לעיין ב-Execution Plan:
המערכת מבצעת אגרגציה ומחשבת Top לכל ID בטבלה השניה מכיוון שהיא מזהה שיש ריבוי של שורות לכל ID.
האם זה נכון? מצד אחד- התהליך לא נופל ומקבלים תוצאה שהיא לא יותר נכונה ולא פחות נכונה מהתוצאות האפשריות האחרות, מצד שני- אם יש כמה תוצאות אפשריות אזי כולן לא נכונות..
גרסת SQL Server 2012 מביאה לא מעט חידושים משמחים, ואחד מהם הוא הפונקציה Concat שמשרשרת מספר מחרוזות למחרוזת אחת, בדומה לשימוש באופרטור + לשירשור, אך באופן ידידותי יותר: אין צורך להפוך מספרים ותאריכים לטקסטים בעזרת Cast וגם לא להמיר ערכי Null למחרוזות ריקות פן יהפכו את השירשור כולו ל-Null (מקווה שהבעיות שציינתי שנפתרו- מוכרות).
בהחלט שווה!
דא עקא שפתרו הרבה בעיות קיימות, אבל הכניסו בדלת האחורית בעייה חדשה שלא הייתה קיימת עד כה.
נניח שאנחנו רוצים לקבל מדטבייס ReportServer רשימת טבלאות עם שם הסכימה משורשרת אליהן כמקובל, ופונים לשם כך לטבלת המערכת INFORMATION_SCHEMA.TABLES (הדטבייס אמור להיות קיים אצל כל מי שהתקין התקנה סטנדרטית, וטבלת המערכת שציינתי היא אחת מכמה אופציות שיש). את כל זה אנחנו עושים מדטבייס אחר, נניח tempdb.
בשיטה הישנה היינו נוהגים כך:
Use tempdb;
Go
Select TABLE_SCHEMA+'.'+TABLE_NAME [Table]
From ReportServer.INFORMATION_SCHEMA.TABLES;
אין בעיות אך גם אין סומכים על הנס, ולכן נשתמש כעת ב-Concat:
Use tempdb;
Go
Select Concat(TABLE_SCHEMA,'.',TABLE_NAME) [Table]
From ReportServer.INFORMATION_SCHEMA.TABLES;
עכשיו כן יש בעיות: בגלל שלשני הדטבייסים Collations שונים- המערכת אינה יכולה לשרשר את שמות העמודות שנלקחו מ-ReportServer שלו Collation אחד עם תו נקודה ‘.’ שנלקח מדטבייס tempdb שלו Collation שונה..
קצת מצער לגלות שהפונקציה הזו סלחנית לערכי Null או למספרים המשורשרים לטקסטים, אבל בעיית collation שהוחלקה באלגנטיות בעבר – פתאום הופכת לאבן נגף.
הערה – אני מעריך שהרוב יתקלו בבעייה, בהנחה שה-Collations בשני הדטבייסים הנ”ל שונים.
לפני כארבע שנים, אב”ג פרסם בטור שלו את חידת המשולשים (בסוף המאמר), חידה שכדוגמתה ניתן למצוא באינטרנט או בספרי חידות: צורה גיאומטרית המחולקת למשולשים קטנים שמהם נוצרים משולשים גדולים יותר, וצריך למצוא כמה משולשים יש בסה”כ. בדרך כלל מסתבכים, סופרים חלק מהמשולשים פעמיים, משולשים אחרים מפספסים, קשה לסמן את המשולשים, והשמחה רבה..
הפעם נפתור את הבעייה שאב”ג הציג: היא לא המסובכת מכולם, אבל אנחנו הרי בעסקי SQL..
ניתן שמות לנקודות השונות בציור, וניצור שלוש טבלאות):
טבלת נקודות
טבלת קווים (השם של כל קו יווצר מהנקודה הראשונה והאחרונה שלו)
טבלת קווים-נקודות (יחס רבים לרבים – N:N – בין שתי הנ”ל).
אפשר להסתפק בטבלה האחרונה, ולקבל במקרה הצורך את הקווים או הנקודות על ידי שליפת Distinct, אבל אנחנו נהיה מסודרים- מפתח ראשי לכל טבלה, ויחס 1:N בין שתי הטבלאות הראשונות לטבלת הקשר:
If Object_ID('T_Nekudot') Is Not Null Drop Table T_Nekudot;
Go
Create Table T_Nekudot(Nekuda Char(1) Primary Key);
Go
Insert
Into T_Nekudot
Select 'A' Union All
Select 'B' Union All
Select 'C' Union All
Select 'D' Union All
Select 'E' Union All
Select 'F' Union All
Select 'G' Union All
Select 'H' Union All
Select 'I' Union All
Select 'J' Union All
Select 'K' Union All
Select 'L';
If Object_ID('T_Kavim') Is Not Null Drop Table T_Kavim;
Go
Create Table T_Kavim(Kav Char(2) Primary Key);
Go
Insert
Into T_Kavim
Select 'AK' Union All
Select 'CE' Union All
Select 'FH' Union All
Select 'IK' Union All
Select 'AL' Union All
Select 'BF' Union All
Select 'EI' Union All
Select 'HL' Union All
Select 'BC' Union All
Select 'EF' Union All
Select 'HI' Union All
Select 'KL';
If Object_ID('T_KavimNekudot') Is Not Null Drop Table T_KavimNekudot;
Go
Create Table T_KavimNekudot(Kav Char(2) Not Null,
Nekuda Char(1) Not Null);
Alter Table T_KavimNekudot Add Constraint PK_T_Nekudot Primary Key Clustered (Kav,Nekuda);
Go
Alter Table T_KavimNekudot
Add Foreign Key (Kav) References T_Kavim(Kav);
Go
Alter Table T_KavimNekudot
Add Foreign Key (Nekuda) References T_Nekudot(Nekuda);
Go
Insert
Into T_KavimNekudot
Select 'AK','A' Union All
Select 'AK','B' Union All
Select 'AK','E' Union All
Select 'AK','H' Union All
Select 'AK','K' Union All
Select 'CE','E' Union All
Select 'CE','D' Union All
Select 'CE','C' Union All
Select 'FH','H' Union All
Select 'FH','G' Union All
Select 'FH','F' Union All
Select 'IK','K' Union All
Select 'IK','J' Union All
Select 'IK','I' Union All
Select 'AL','A' Union All
Select 'AL','C' Union All
Select 'AL','F' Union All
Select 'AL','I' Union All
Select 'AL','L' Union All
Select 'BF','B' Union All
Select 'BF','D' Union All
Select 'BF','F' Union All
Select 'EI','E' Union All
Select 'EI','G' Union All
Select 'EI','I' Union All
Select 'HL','H' Union All
Select 'HL','J' Union All
Select 'HL','L' Union All
Select 'BC','B' Union All
Select 'BC','C' Union All
Select 'EF','E' Union All
Select 'EF','F' Union All
Select 'HI','H' Union All
Select 'HI','I' Union All
Select 'KL','K' Union All
Select 'KL','L';
דרך ראשונה תחפש שלשות של קווים שיוצרים משולשים ןתהיה בעלת אופי גיאומטרי יותר:
Select *
From T_Kavim T1
Inner Join T_Kavim T2
On T1.Kav>T2.Kav
Inner Join T_Kavim T3
On T2.Kav>T3.Kav
Where Exists (Select 1
From T_KavimNekudot K1
Inner Join T_KavimNekudot K2
On K1.Nekuda=K2.Nekuda
Where K1.Kav=T1.Kav
And K2.Kav=T2.Kav)
And Exists (Select 1
From T_KavimNekudot K2
Inner Join T_KavimNekudot K3
On K2.Nekuda=K3.Nekuda
Where K2.Kav=T2.Kav
And K3.Kav=T3.Kav)
And Exists (Select 1
From T_KavimNekudot K1
Inner Join T_KavimNekudot K3
On K1.Nekuda=K3.Nekuda
Where K1.Kav=T1.Kav
And K3.Kav=T3.Kav)
And Not Exists (Select Nekuda
From T_KavimNekudot
Where Kav In (T1.Kav,T2.Kav,T3.Kav)
Group By Nekuda
Having Count(Kav)=3)
Order By 1,2,3;
ניצור Join עצמי כפול של טבלת הקווים כדי לקבל את כל הצירופים של שלושה קווים שונים,
נבדוק אם קיימת נקודה שנמצאת גם על הראשון וגם על השני,
נבדוק אם קיימת נקודה שנמצאת גם על השני וגם על השלישי,
נבדוק אם קיימת נקודה שנמצאת גם על השלישי וגם על הראשון,
ונוודא שלא מדובר בשלושת המקרים באותה נקודה (במקרה כזה לא נוצר משולש).
בסה”כ 38 משולשים (אם מישהו ניסה לפתור ידנית..).
הדרך השנייה תהיה לחפש שלשות של נקודות שנמצאות על שלושה קווים שונים, ומבחינה טכנית- קיימים שלושה קווים שעל כל אחד שתי נקודות מהשלוש. פתרון זה יותר טכני וגם הרבה יותר יעיל:
Select *
From T_Nekudot T1
Inner Join T_Nekudot T2
On T1.Nekuda>T2.Nekuda
Inner Join T_Nekudot T3
On T2.Nekuda>T3.Nekuda
Where Exists (Select Count(1)
From (Select Kav
From T_KavimNekudot KN1
Where Nekuda In (T1.Nekuda,T2.Nekuda,T3.Nekuda)
Group By Kav
Having Count(Nekuda)=2) T
Having Count(1)=3)
Order By 1,2,3;
התנאי קצת מסובך: יש לחפש קווים ששתיים משלוש הנקודות נמצאות עליהם (השאילתה הפנימית בתנאי),
ושיש שלושה כאלו (השאילתה החיצונית).
גם כאן יש בסה”כ 38 משולשים, רק שכאן מקבלים את הקודקודים שלהם ולא את הצלעות כמו קודם.
הדרך השלישית תשתמש בטכנולוגיה שונה לחלוטין- Spatial – שתחסוך מאיתנו את הצורך “ללכלך את הידיים” בגאומטריה ותעשה זאת בעצמה בעזרת המתודות המובנות בה. הפעם ניצור רק טבלה אחת ונכניס לתוכה את 12 הקווים שבבעייה – כל אחד עם הנקודות לאורכו.
לשם כך נציב את המשולש כולו במערכת צירים כדי שנוכל לתת את הקואורדינטות של כל הנקודות. לא חייבים לדייק “על הקשקש” ואפשר למפות בקירוב:
If Object_ID('T_Spatial') Is Not Null Drop Table T_Spatial;
Go
Create Table T_Spatial(ID Int Identity,
Kav geometry);
Go
Insert
Into T_Spatial(Kav)
Select geometry::STGeomFromText('LineString (3 6,5 6)', 0) Union All
Select geometry::STGeomFromText('LineString (2 4,6 4)', 0) Union All
Select geometry::STGeomFromText('LineString (1 2,7 2)', 0) Union All
Select geometry::STGeomFromText('LineString (0 0,8 0)', 0) Union All
Select geometry::STGeomFromText('LineString (0 0,4 1,7 2)', 0) Union All
Select geometry::STGeomFromText('LineString (1 2,4 3,6 4)', 0) Union All
Select geometry::STGeomFromText('LineString (2 4,4 5,5 6)', 0) Union All
Select geometry::STGeomFromText('LineString (0 0,1 2,2 4,3 6,4 7)', 0) Union All
Select geometry::STGeomFromText('LineString (4 7,5 6,6 4,7 2,8 0)', 0) Union All
Select geometry::STGeomFromText('LineString (3 6,4 5,6 4)', 0) Union All
Select geometry::STGeomFromText('LineString (2 4,4 3,7 2)', 0) Union All
Select geometry::STGeomFromText('LineString (1 2,4 1,8 0)', 0);
Select ID,
Kav.ToString()
From T_Spatial;
למשל- שורה מספר 8 מייצגת את השוק השמאלית של המשולש כולו החל ממפגש הצירים 0 0 וכלה בקודקוד העליון 7 4 דרך שלוש נקודות נוספות..
ניעזר במתודה STIntersects כדי לבדוק בשלשות הקווים שניצור אם הם נחתכים זה עם זה,
ובמתודה STIntersection לוודא שהם לא נחתכים באותה נקודה (קיימות לפחות שתי נקודות חיתוך שונות):
Select T1.Kav.STIntersection(T2.Kav).ToString(),
T2.Kav.STIntersection(T3.Kav).ToString(),
T3.Kav.STIntersection(T1.Kav).ToString()
From T_Spatial T1
Inner Join T_Spatial T2
On T1.ID>T2.ID
Inner Join T_Spatial T3
On T2.ID>T3.ID
Where T1.Kav.STIntersects(T2.Kav)=1
And T2.Kav.STIntersects(T3.Kav)=1
And T3.Kav.STIntersects(T1.Kav)=1
And T1.Kav.STIntersection(T2.Kav).ToString()<>T2.Kav.STIntersection(T3.Kav).ToString();
גם כאן 38 פתרונות, והפעם אנחנו מקבלים אותם כקואורדינטות.
לפי ה-Execution Plan – פתרון זה הרבה פחות יעיל מהשני.
מהפתרון של אב”ג עולה שהייתה אי הבנה טכנית- בחידה המקורית קו BC לא היה אמור להופיע.. הפתרון של אב”ג – 34 – הוא ללא BC; עם BC נוצרים עוד 4 משולשים (אלו שהוא חלק מהם) ובסה”כ 38.
פרוצדורות וסקריפטים אחרים כוללים אופציה לבצע אותם בזהות אחרת בעזרת אופציית Execute As, בעיקר בשל בעיית הרשאות. ננסה לראות איך זה נראה "מבפנים"..
ניצור Login ולו User עם הרשאות חלקיות, בשעה שאנחנו (כלומר- אני, זה שמריץ את הקודים עם ה-Login וה-User שהוגדרו לי) עם הרשאות sa בשרת ו-dbo בדטבייס בו נעבוד (tempdb),
ולמי שלא זוכר- Login הוא אובייקט ברמת השרת ואף הדומיין כולו, ו-User הוא אובייקט ברמת הדטבייס שמייצג בדרך כלל את ה-Login:
Use tempdb;
Go
Create Login MyLogin With Password='MyLogin',
Default_Database=tempdb,Check_Policy=Off;
Go
Create User MyUser For Login MyLogin With Default_Schema=MySchema;
Go
Exec SP_AddRoleMember 'DB_DDLAdmin', 'MyUser'
Go
Create Schema MySchema Authorization MyUser;
Go
הסבר: ה-Login הוא MyLogin, ה-User שלו ב-tempdb הוא MyUser;
ניצור בנוסף סכימה בשם MySchema בדטבייס tempdb,
ו-MyUser יהיה ה-Owner שלה והיא תהיה ברירת המחדל שלו.
לבסוף- ניתן ל-MyUser הרשאות מתאימות ליצור פרוצדורות, ובהמשך נוסיף לו עוד על פי הצורך (לא להתלהב- בשלב זה יש מעט מאוד דברים שהוא יכול לעשות או לראות בדטבייס..).
כעת ניצור טבלה מבלי לתת לו הרשאות:
If Object_ID('T','U') Is Not Null Drop Table T;
Go
Create Table T(I Int);
Go
Execute As User='MyUser';
Select * From T;
Revert;
הטבלה נוצרה, אבל ל-MyUser אין הרשאות לצפות בה (אין בה נתונים אבל זה לא משנה).
השתמשתי ב-Execute As כדי לבצע את הפקודה עם ההרשאות שלו, וחזרתי לעצמי בעזרת Revert.
לא ניתן ל-MyUser הרשאות באופן ישיר, אך ננחם אותו בשלוש פרוצדורות שייגשו בעצמן לטבלה וניתן לו הרשאות להריצן:
-----------------------------------------------------
If Object_ID('P_T1','P') Is Not Null Drop Proc P_T1;
Go
Create Proc P_T1 As
Select * From T;
Go
Grant Exec On P_T1 To MyUser;
Go
-----------------------------------------------------
If Object_ID('P_T2','P') Is Not Null Drop Proc P_T2;
Go
Create Proc P_T2 As
Exec('Select * From T;')
Go
Grant Exec On P_T2 To MyUser;
Go
-----------------------------------------------------
If Object_ID('P_T3','P') Is Not Null Drop Proc P_T3;
Go
Create Proc P_T3 With Execute As Owner As
Exec('Select * From T;')
Go
Grant Exec On P_T3 To MyUser;
Go
-----------------------------------------------------
Exec dbo.P_T1;
Exec dbo.P_T2;
Exec dbo.P_T3;
שלוש הפרוצדורות תקינות ומתבצעות בהצלחה עם ההרשאות שלי כ-sa (הגם שהן מחזירות סטים ריקים כי אין נתונים בטבלה).
כעת נריץ אותן עם ההרשאות של MyUser:
Execute As User='MyUser';
Exec dbo.P_T1;
Exec dbo.P_T2;
Exec dbo.P_T3;
Revert;
הפרוצדורות הראשונה והשלישית הצליחו, והשניה נכשלה.
הסבר: הראשונה הצליחה מכיוון של-MyUser ניתנה הרשאה להריץ את הפרוצדורה, ובעקיפין ובאופן כללי ניתנו בכך הרשאות לכל האובייקטים שהפרוצדורה מפעילה או ניגשת אליהם (זה לא תמיד כך- כפי שנראה, אבל באופן כללי כן).
הפרוצדורה השניה ביצעה Select באמצעות SQL דינאמי, במקרה זה ההרשאות אינן עוברות לקוד הדינאמי, ומכיוון של-MyUser אין הרשאות ישירות על הטבלה- ה-Select נכשל.
הפרוצדורה השלישית מופעלת עם הרשאות של dbo (הוא ה-Owner של הפרוצדורה כי היא בסכימה dbo שלו), ולכן ה-SQL הדינאמי מצליח כי הוא מתבצע עם הרשאות של dbo.
עד כאן מדוע יש צורך באופרטור Execute As בפרוצדורה במקרים בהם יש פעולות שאינן יורשות את ההרשאות של הפרוצדורה שמבצעת אותן, ויש להבדיל בין השימוש ב-Execute As כחלק מהפרוצדורה שמאפשר לשדרג את הרשאות המשתמש באופן נקודתי, לבין השימוש שאני עושה ב-Execute As כדי "לשנמך" את ההרשאות של עצמי ולהדגים מה קורה כשמשתמש "פשוט" מבצע אותן.
נתחיל את החלק הזה ביצירת שתי פרוצדורות הכוללות Execute As dbo – אחת בכל סכימה, וניתן ל-MyUser הרשאות על זו שב-dbo (על זו שב-MySchema אין צורך לתת כי הןא ה-Owner שלה):
---------------------------------------------------------------------------------------------------------------------------------
If Object_ID('P_Exec_dbo_1','P') Is Not Null Drop Proc P_Exec_dbo_1;
Go
Create Proc P_Exec_dbo_1 With Execute As 'dbo' As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Go
Grant Exec On P_Exec_dbo_1 To MyUser;
Go
---------------------------------------------------------------------------------------------------------------------------------
If Object_ID('MySchema.P_Exec_dbo_2','P') Is Not Null Drop Proc MySchema.P_Exec_dbo_2;
Go
Create Proc MySchema.P_Exec_dbo_2 With Execute As 'dbo' As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Go
---------------------------------------------------------------------------------------------------------------------------------
כעת נבצע את הראשונה- פעם עם הזהות שלי ופעם עם זו של MyUser;
ואת השניה- שוב, פעם עם הזהות שלי ופעם עם זו של MyUser:
Exec dbo.P_Exec_dbo_1;
Execute As User='MyUser';
Exec dbo.P_Exec_dbo_1;
Revert;
Exec MySchema.P_Exec_dbo_2;
Execute As User='MyUser';
Exec MySchema.P_Exec_dbo_2;
Revert;
כפי שניתן לראות- בכל ארבעת המקרים הפלט זהה: המערכת זיהתה את הסכימה של מי שמריץ אותה בתור dbo, את ה-User בתור dbo, ואת ה-Login (של ה-dbo) בתור sa; ללא קשר למי שבאמת מריץ ולסכימה שאליה הפרוצדורה שייכת.
כדאי להזכיר שב-SQL Server המונח dbo הוא גם סכימה וגם User.
מזה אפשר להבין שאם מבצעים Trace על הריצות של הפרוצדורות עלולה להיווצר כאן בעייה בשל ה-Impersonation: כיצד נדע מי באמת הריץ מה? ניתן להגיע לכך באמצעות הפונקציה Original_Login() שמציגה תמיד את ה-Login המקורי בו התחברו למערכת. אני אינני עושה בו כאן שימוש מפני שבכל מקרה הוא יציג את Login_Geri, גם במקרה בו אני מריץ את הפרוצדורה בתור MyUser.
אפשרות אחרת (להרצה בתור dbo) היא לתת לפרוצדורה לרוץ בהקשר (קונטקסט) של הבעלים (Owner) שלה, ובאופן דומה לנ"ל ניצור שתי פרוצדורות ונריץ כל אחת פעמיים- עם כל User בנפרד:
---------------------------------------------------------------------------------------------------------------------------------
If Object_ID('P_Exec_Owner_1','P') Is Not Null Drop Proc P_Exec_Owner_1;
Go
Create Proc P_Exec_Owner_1 With Execute As Owner As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Go
Grant Exec On P_Exec_Owner_1 To MyUser;
Go
---------------------------------------------------------------------------------------------------------------------------------
If Object_ID('MySchema.P_Exec_Owner_2','P') Is Not Null Drop Proc MySchema.P_Exec_Owner_2;
Go
Create Proc MySchema.P_Exec_Owner_2 With Execute As Owner As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Go
---------------------------------------------------------------------------------------------------------------------------------
במקרה זה התוצאות לא תלויות בשאלה מי הריץ באמת: הפרוצדורה זיהתה בכל ארבעת המקרים את ה-User בתור ה-Owner שלה (כלומר- הסכימה לה היא שייכת), את סכימה בתור הסכימה שלה, ואת ה-Login בתור זה של ה-User.
שוב- בפרוצדורה הראשונה dbo הוא גם הסכימה וגם ה-User (שניהם קיימים כברירת מחדל),
ובפרוצדורה השנייה- הסכימה היא MySchema וה-User הוא MyUser (שניהם נוצרו על ידינו בהתחלה).
לסיכום שני המקרים שבדקנו עד כה- פרוצדורה בסכימה dbo תתנהג באופן זהה אם נריץ אותה בתור dbo או בתור ה-Owner מפני שה-Owner הוא dbo. בסכימה MySchema יהיה כמובן הבדל אם נריץ בתור dbo או בתור ה-Owner; אבל בכל מקרה אין זה משנה מי הריץ באמת.
מקרה שלישי שנבדוק הוא הרצה בתור Self, כלומר- בתור זה שיצר את הפרוצדורה. במקרה זה נבדוק 4 פרוצדורות- שתיים בסכימה dbo שאחת נוצרה על ידי והשנייה על ידי MyUser, ושתיים בסכימה MySchema; ואת כל אחת מארבע הפרוצדורות נריץ פעמיים- פעם אחת על ידי ופעם אחת על ידי MyUser:
---------------------------------------------------------------------------------------------------------------------------------
If Object_ID('P_Exec_Self_dbo_Geri','P') Is Not Null Drop Proc
P_Exec_Self_dbo_Geri;
Go
Create Proc P_Exec_Self_dbo_GeriWith Execute As Self As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Go
Grant Exec On P_Exec_Self_dbo_Geri To MyUser;
Go
---------------------------------------------------------------------------------------------------------------------------------
Execute As User='MyUser';
If Object_ID('P_Exec_Self_dbo_MyUser','P') Is Not Null Drop Proc P_Exec_Self_dbo_MyUser;
Go
Create Proc dbo.P_Exec_Self_dbo_MyUser With Execute As Self As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Go
Revert;
Grant Exec On dbo.P_Exec_Self_dbo_MyUser To MyUser;
Go
---------------------------------------------------------------------------------------------------------------------------------
If Object_ID('MySchema.P_Exec_Self_MySchema_Geri','P') Is Not Null Drop Proc MySchema.P_Exec_Self_MySchema_Geri;
Go
Create Proc MySchema.P_Exec_Self_MySchema_Geri With Execute As Self As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Go
Grant Exec On MySchema.P_Exec_Self_MySchema_Geri To MyUser;
Go
---------------------------------------------------------------------------------------------------------------------------------
Execute As User='MyUser';
If Object_ID('MySchema.P_Exec_Self_MySchema_MyUser','P') Is Not Null Drop Proc MySchema.P_Exec_Self_MySchema_MyUser;
Go
Create Proc MySchema.P_Exec_Self_MySchema_MyUser With Execute As Self As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Go
Revert;
---------------------------------------------------------------------------------------------------------------------------------
Exec dbo.P_Exec_Self_dbo_Geri;
Execute As User='MyUser';
Exec dbo.P_Exec_Self_dbo_Geri;
Revert;
-----------------------------------------------------
Exec dbo.P_Exec_Self_dbo_MyUser;
Execute As User='MyUser';
Exec dbo.P_Exec_Self_dbo_MyUser;
Revert;
-----------------------------------------------------
Exec MySchema.P_Exec_Self_MySchema_Geri;
Execute As User='MyUser';
Exec MySchema.P_Exec_Self_MySchema_Geri;
Revert;
-----------------------------------------------------
Exec MySchema.P_Exec_Self_MySchema_MyUser;
Execute As User='MyUser';
Exec MySchema.P_Exec_Self_MySchema_MyUser;
Revert;
-----------------------------------------------------
מקווה שהצבעים עוזרים לשייך כל צמד פקודות לפלט שלהן.
ניתן לראות שבכל המקרים – המערכת זיהתה את מי שמריץ אותה כזה שייצר אותה, ולא כמי שמריץ "באמת" וגם לא כ-Owner שלה או של הסכימה אליו היא שייכת.
ברור שאני בודק כאן תסריטים שונים ומשונים, חלקם לא מציאותיים, וברוב המקרים הפרוצדורות נוצרות בסכימה dbo על ידי dbo; וכל התרגילים הטכניים נועדו להמחיש את משמעות האופציות השונות.
לסיום- יש אופציות נוספות לביצוע Impersonation, נדגים ונסביר:
---------------------------------------------------------------------------------------------------------------------------------
If Object_ID('dbo.P_1','P') Is Not Null Drop Proc dbo.P_1;
Go
Create Proc dbo.P_1 As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Go
---------------------------------------------------------------------------------------------------------------------------------
If Object_ID('dbo.P_2','P') Is Not Null Drop Proc dbo.P_2;
Go
Create Proc dbo.P_2 With Execute As Caller As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Exec dbo.P_1;
Go
---------------------------------------------------------------------------------------------------------------------------------
If Object_ID('dbo.P_3','P') Is Not Null Drop Proc dbo.P_3;
Go
Create Proc dbo.P_3 With Execute As 'MyUser' As
Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];
Exec dbo.P_2;
Go
Grant Exec On dbo.P_3 To MyUser;
Go
---------------------------------------------------------------------------------------------------------------------------------
Exec P_3;
Execute As User='MyUser';
Exec P_3;
Revert;
ההפעלה הראשונית היא של הפרוצדורה P_3 שמתבצעת בתור MyUser ללא קשר למי שבאמת הפעיל אותה.
P_3 מפעילה את P_2 שמתבצעת As Caller, כלומר כמי שהפעיל אותה ("מבחינתה"- זה מי ש- P_3 מזהה בתור המפעיל).
P_2 מפעילה את P_1 ללא כל חיווי מפורש לגבי "המפעיל".
כתוצאה מכך בשני המקרים הופעלו שלוש הפרוצדורות על ידי MyUser, וניתן לראות שאם לא מציינים במפורש מי מבצע הרי זה בעצם As Caller.
טריגר על פעולות DML בטבלה הוא כלי מוכר יחסית, אך לא כל האופציות מוכרות; ואולי מתבלבלים לעיתים בינו לבין טריגרים אחרים.
הפוסט הזה עוסק בטריגרים על טבלאות שמופעלים בעקבות ביצוע פעולות Delete / Update / Insert בטבלה עליה הם הוגדרו. הטריגרים אינם מופעלים בעת ביצוע פקודת Select או Truncate, שינוי סכימה של טבלה, או כל שינוי שהוא באובייקטים של המערכת; ולכל אלו יש פתרונות אחרים.
כדי לפשט את הדוגמאות- אשתמש בכל פעם בטבלה אחת בלבד, והטריגרים יפעילו פקודות Print או Select כדי להמחיש את הפעולה שלהם. במציאות הטריגרים אמורים להפעיל בדרך כלל פקודות Insert לטבלת לוג שמתעדת את השינויים בטבלה אליה הטריגר קשור, אם כי יכולים להיות להם שימושים נוספים.
כפי שציינתי- הטריגרים משמשים בדרך כלל לתיעוד השינויים לטבלת לוג, ולשם כך עליהם "ללכוד" את השורות שהשתנו. הטריגר הוא סקריפט (סדרת פקודות ב-TSQL) שמוגדר על טבלה ומופעל – בהתאם לאופן בו הוא הוגדר – בעקבות ביצוע פעולות Delete / Update / Insert (ניתן להגדיר טריגר על פעולה אחת או על מספר פעולות).
הטריגר יכול להיעזר בשני סטים יחודיים: Inserted שהוא סט השורות שהתווספו, ו-Deleted שהוא סט השורות שנמחקו. בפקודת Insert יש תוכן רק ב-Inserted, בפקודת Delete יש תוכן רק ב-Deleted, ובפקודת Update יש תוכן בשתיהן (Inserted עם השורות לאחר העדכון ו-Deleted עם השורות לפני העדכון).
ניצור טבלה להדגמה וניצור לה טריגר:
Use tempdb;
Go
If Object_Id('T_1','U') Is Not Null Drop Table T_1;
Go
Create Table T_1(ID Int Identity,
Txt Varchar(Max));
Go
Create Trigger Trg_T_1 On T_1
After Insert, Update, Delete As
Select Case When I.ID Is Null Then 'Deleted'
When D.ID Is Null Then 'Inserted'
Else 'Updated' End [Type],
IsNull(I.ID,D.ID) ID,
D.Txt OldTxt,
I.Txt NewTxt
From Inserted I
Full Outer Join Deleted D
On I.ID=D.ID;
Go
הטריגר שולף מ-Deleted ו-Inserted את כל השורות הרלוונטיות: בגלל שמדובר ב-Full Outer Join ישלפו במקרה של Insert כל ה-Inserted למרות שאין Deleted, להיפך במקרה של Delete, ובמקרה של Update ישלפו כולם; ולפי עמודת ה-ID נוכל לדעת באיזה סוג פעולה מדובר (עמודה Type);
כאשר אני מדגיש שוב שהשליפה למסך נועדה להמחיש כיצד הטריגר עובד: במציאות הפלט יופנה לטבלת לוג מתאימה.
Insert
Into T_1(Txt)
Select 'a' Union All
Select 'bb' Union All
Select 'ccc';
Update T_1
Set Txt='New bb'
Where ID=2;
Delete
From T_1
Where Txt='ccc';
אפשר לראות שכל אחת מהפעולות הפעילה את הטריגר, והוא שלף למסך את המידע השורות הרלוונטיות.
כמובן שניתן ליצור טריגר רק לפעולה אחת או שתיים, וניתן ליצור מספר טריגרים שיפעלו בטור זה אחר זה.
העובדה שהטריגר הנ"ל הוגדר כ-After (כדאי לעיין בקוד) היא ברירת המחדל, וניתן להשתמש באופרטור For במקומה והתוצאה תהיה זהה.
כדאי להדגיש שפעולת ה-DML והפעלת הטריגר מהווים טרנזקציה אחת: אם אחד מהם יכשל- כל הטרנזקציה תיפול, ושתי הפעולות לא יתבצעו. בנוסף- אם הטריגר איטי – כל פעולת ה-DML תהיה איטית ויקח זמן רב מהצפוי עד שנקבל אישור שהפעולה הסתיימה בהצלחה. בנוסף- החיווי בלשונית ה-Messages תתייחס גם לפעולת ה-DML וגם לפעולת הטריגר.
אופציה נוספת מיוחדת לטריגרים היא הפונקציה Update שבעזרתה ניתן לבדוק אילו עמודות השתנו:
Use tempdb;
Go
If Object_Id('T_2','U') Is Not Null Drop Table T_2;
Go
Create Table T_2(ID Int Identity,
Txt Varchar(Max));
Go
Create Trigger Trg_T_2 On T_2
After Insert, Update, Delete As
If Update(ID) Print 'ID column was updated';
If Update(Txt) Print 'Txt column was updated';
Go
Insert
Into T_2(Txt)
Select 'a' Union All
Select 'bb' Union All
Select 'ccc';
Update T_2
Set Txt='New bb'
Where ID=2;
Delete
From T_2
Where Txt='ccc';
בפקודת ה-Insert עודכנו שתי העמודות ולכן שתי פקודות ה-Print פעלו,
בפקודת ה-Update רק עמודה Txt עודכנה ולכן רק פקודת ה-Print "שלה" פעלה,
ובפקודת ה-Delete העמודות – לצורך העניין – לא התעדכנו ופקודות ה-Print לא פעלו.
האם חייבים לכתוב תנאי Update על כל עמודה וכמודה כדאי לדעת אילו התעדכנו? הרי בטבלה יכולים להיות עד 1024 עמודות (בהנחה שאין שימוש ב-Sparse).. לנוחיות ציבור הנוסעים קיים משתנה בשם Columns_Updated שהמידע הזה מקודד בו: מדובר במשתנה מסוג VarBinary שכל Byte בו מייצג שמונה עמודות על פי הסדר. כלומר- ערכו הבינארי יכול להיות למשל 01001001 ואז ניתן לדעת שעמודות מספר 2,5,8 בשמיניה שאותו Byte מייצג – התעדכנו.
בעזרת טבלת מספרים מ-1 ועד 128 (כי 1024 עמודות הן 128 שמיניות של עמודות), שימוש ב-& (ע"ע Bitwise And), פניה לטבלת המערכת sys.columns בה מופיעות כל העמודות ממוספרות על פי הסדר, הפונקציה DataLength שמחשבת כמה Bytes (שמיניות) יש ב-Columns_Updated ועוד כמה פירוטכניקות מגיעים לקוד הבא (טבלה, טריגר, ופעולות DML להמחשה):
Use tempdb;
Go
If Object_Id('T_3','U') Is Not Null Drop Table T_3;
Go
Create Table T_3(ID Int Identity,
Txt Varchar(Max));
Go
Create Trigger Trg_T_3 On T_3
After Insert, Update, Delete As
With Num As
(Select Top 128 Row_Number() Over(Order By DB_ID()) N --טבלת מספרים: מקסימום 128 שמיניות
From sys.messages)
Select Name --שם העמודה
From sys.columns --טבלת העמודות
Inner Join Num --טבלת המספרים הנ"ל
On column_id Between 8*(Num.N-1)+1 And 8*Num.N
Where Object_Name(object_id)='T_3' --רק העמודות של הטבלה לה שייך הטריגר
And Num.N<=DataLength(Columns_Updated()) --צד ימין מציין את מספר השמיניות
And Convert(Binary(1),Substring(Columns_Updated(),Num.N,1))&Power(2,column_id-(8*(Num.N-1)+1))>0 --אילו עמודות נמצאות בשמינייתן
Order By column_id; --מיון לפי סדר העמודות בטבלה
Go
Insert
Into T_3(Txt)
Select 'a' Union All
Select 'bb' Union All
Select 'ccc';
Update T_3
Set Txt='New bb'
Where ID=2;
Delete
From T_3
Where Txt='ccc';
שוב- הפלט למסך הוא רק כדי להמחיש מה המערכת זיהתה, ובמציאות המידע יופנה בדרך כלל לטבלה מתאימה.
האם קיים טריגר מסוג Before Insert? לא בדיוק: קיים Instead of Trigger שמתבצע במקום הפעולה (וממילא לפני שהיא מתבצעת..):
Use tempdb;
Go
If Object_Id('T_4','U') Is Not Null Drop Table T_4;
Go
Create Table T_4(ID Int Identity,
Txt Varchar(Max));
Go
Create Trigger Trg_T_4 On T_4
Instead of Update, Delete As
Return
Go
Insert
Into T_4(Txt)
Select 'a' Union All
Select 'bb' Union All
Select 'ccc';
Update T_4
Set Txt='New bb'
Where ID=2;
Delete
From T_4
Where Txt='ccc';
Select *
From T_4;
הטריגר הפעם הוא על פקודות Update & Delete ולא על Insert, ולכן פקודת ה-Insert התבצעה אך האחרות לא וניתן לראות זאת בפלט של פקודת ה-Select בסוף.
בטריגר עצמו מופיעה רק פקודת Return ולכן הוא אינו מבצע דבר במקום מה שאמור היה להתבצע.
כפי שציינתי- ניתן ליצור מספר טריגרים על אותה טבלה, אבל Instead of Triggers אינם יכולים לחפוף זה את זה (כלומר לא יכולים להיות שני Instead of Insert Triggers), וכשאחד מהם מופעל- הטריגרים האחרים (After Triggers) לא יתבצעו.
סדר ביצוע הטריגרים הוא אקראי אלא אם כן הגדרנו אחרת באמצעות SP_SetTriggerOrder:
Use tempdb;
Go
If Object_Id('T_5','U') Is Not Null Drop Table T_5;
Go
Create Table T_5(ID Int Identity,
Txt Varchar(Max));
Go
Create Trigger Trg_T_5_1 On T_5
After Insert, Update, Delete As
Print 'Trg_T_5_1';
Go
Create Trigger Trg_T_5_2 On T_5
After Insert, Update, Delete As
Print 'Trg_T_5_2';
Go
Create Trigger Trg_T_5_3 On T_5
After Insert, Update, Delete As
Print 'Trg_T_5_3';
Go
Insert
Into T_5(Txt)
Select 'a' Union All
Select 'bb' Union All
Select 'ccc';
Exec SP_SetTriggerOrder 'Trg_T_5_1', 'Last', 'Update';
Exec SP_SetTriggerOrder 'Trg_T_5_3', 'First', 'Delete';
Update T_5
Set Txt='New bb'
Where ID=2;
Delete
From T_5
Where Txt='ccc';
בפקודת ה-Insert הטריגרים בוצעו בסדר אקראי (במקרה או שלא- לפי סדר היווצרותם).
לאחר מכן הטריגר הראשון הוגדר כאחרון לפקודות Update, והטריגר השלישי כראשון לפקודות Delete.
בפקודת ה-Update הטריגר הראשון בוצע אחרון כפי שהוגדר עבורו,
ובפקודת ה-Delete הטריגר השלישי בוצע ראשון כפי שהוגדר עבורו (והראשון אחריו מכיוון שהוא אינו מוגדר כאחרון עבור Delete אלא רק עבור Update).
לבסוף- ניתן ליצור טריגרים גם על Views, בתנאי שמדובר ב-Instead of Trigger:
Use tempdb;
Go
If Object_Id('T_6','U') Is Not Null Drop Table T_6;
Go
Create Table T_6(ID Int Identity,
Txt Varchar(Max));
Go
Create Trigger Trg_T_6_1 On T_6
After Insert, Update, Delete As
Print 'Trg_T_6_1';
Go
If Object_Id('V_6','V') Is Not Null Drop View V_6;
Go
Create View V_6 As
Select *
From T_6;
Go
Create Trigger Trg_T_6_2 On V_6
Instead of Insert, Update, Delete As
Print 'Trg_T_6_2';
Go
Insert
Into T_6(Txt)
Select 'a' Union All
Select 'bb' Union All
Select 'ccc';
Insert
Into V_6(Txt)
Select 'd' Union All
Select 'ee' Union All
Select 'fff';
כפי שאפשר להבין- פעולת ה-Insert הראשונה הופנתה לטבלה והתבצעה,
ואילו פעולת ה-Insert השניה הופנתה ל-View והטריגר התבצע במקומה.
אין מניעה לבצע Insert ל-Views כמו זה, אולם המגבלה של Instead of Triggers גורפת לכל סוגי ה-Views בין אם ניתן לבצע עליהם פעולות DML ובין אם לא.
טריגרים ניתן לנטרל מבלי לבטל כך (בעיקר כשמדובר בהשעייה זמנית):
Disable Trigger All On T_6; --כל הטריגרים
Go
Disable Trigger Trg_T_6_1 On T_6; --טריגר ספציפי
Go
ולהחזיר לכשירות כך:
Enable Trigger All On T_6; --כל הטריגרים
Go
Enable Trigger Trg_T_6_1 On T_6; --טריגר ספציפי
Go
ב-TSQL יש פונקציית Sum לסיכום ערכים, אך אין פונקציית Product לכפילתם. כיצד נחשב איפוא מכפלה של ערכים בשליפת Group By?
הזכרתי בעבר את הפתרון שלמדתי מאב"ג ושהוא עצמו למד מאחרים, ומדי פעם אני נאנח בצער על שלא אני המצאתי את הפטנט הזה: כשלמדתי בתיכון לפני קרוב ל-40 שנה מחשבוני הכיס רק הופיעו, אנחנו היינו כחולמים כשראינו שהם מסוגלים לחבר 1 ועוד 1 ולהחזיר את התוצאה 2, והמשוכללים שבהם כללו פונקציות כמו שורש והראו שהשורש הריבועי של 9 הוא 2.9999999 (בערך)..
לשיעורי חשבון הגענו מצויידים בלוח לוגריתמים- חוברת שכלל טבלאות לוגריתמים של מספרים, וטבלאות סינוס/קוסינוס/טנגנס של זוויות; ובעזרתם פתרנו בעיות חישוביות. הלוגריתמים סייעו לבצע מכפלות מסובכות או חישובי חזקות מעוררי חלחלה בכך שבמקום לכפול שני מספרים היינו מחברים את הלוגריתמים שלהם, ומוצאים את האנטילוג של התוצאה.
זה בערך הרעיון גם בפטנט לחישוב מכפלה:
Select ID,
Exp(Sum(Log(MyFld)))
From MyTbl
Group By ID;
פונקציית Log ב-SQL Server היא לפי הבסיס הטבעי e, והפונקציה Exp מחזירה את e בחזקת הפרמטר שזה בעצם האנטילוג.
יש לפתרון הזה מגבלות, ובראש ובראשונה העובדה שאין Log לאפס ולמספרים שליליים, ולכן גם לבעייה זו נאלתר פתרון.
קודם כל- ניצור בעייה שיש לפתור:
Create Table #T(ID Int, I Int);
Go
Insert
Into #T(ID,I)
Select 1,2 Union All --ID=1 is OK
Select 1,8 Union All
Select 1,5 Union All
Select 2,0 Union All --ID=2 has 1 zero
Select 2,2 Union All
Select 2,8 Union All
Select 2,5 Union All
Select 3,-2 Union All --ID=3 has 2 negatives
Select 3,8 Union All
Select 3,-5 Union All
Select 4,2 Union All --ID=4 Has 1 negative
Select 4,-8 Union All
Select 4,5 Union All
Select 5,0 Union All --ID=5 Has 2 zeros and 3 negatives
Select 5,-2 Union All
Select 5,-8 Union All
Select 5,-5 Union All
Select 5,0;
Go
Select *
From #T
Order By ID;
וחישוב המכפלה של I לכל ID:
Select ID,
Case When Min(Abs(I))=0 Then 0
Else Exp(Sum(Log(Case When I=0 Then 1 Else Abs(I) End)))*Power(-1,Count(Case When I<0 Then 1 End))
End
From #T
Group By ID;
התיבה האדומה: אם יש 0 (אפס) בדרך המכפלה גם 0.
התיבה השחורה: חישוב ה-Log מתבצע על הערך המוחלט, ובנפרד אני מחשב כמה שליליים יש (במקרה של 0 לא משנה מה נבחר).
התיבה התכולה: התוצאה מוכפלת ב-(-1) בחזקת מספר השליליים (אם זוגי אזי 1 ואם שלילי -1).
עד כמה התוצאה מדוייקת? הרי הלוגריתמים אינם מספרים שלמים ובחישובים על הרבה ערכים עלולה להיווצר סטייה ונקבל תוצאות לא מדוייקות.. ביצעתי מספר בדיקות- הצלחתי לחשב עצרת עד 15! (יותר מטריליון) ללא כל סטיה, וחזקות של 2 עד 224 (יותר מ-16 מיליון) ללא סטיה. מעבר לכך נוצרות סטיות שהן זניחות בהתחשב בגודל המספרים, והן בעייתיות בעיקר מבחינה אסתטית (מכפלה של שלמים מחזירה מספר לא שלם).
יחד עם זאת- גם הבעייה האסתטית לרוב אינה בעייה כלל: מתי נצטרך לחשב מכפלות בצורה כזו? הרי אף אחד אינו נדרש לחשב מכפלות אסטרונומיות של מספרים שלמים; ולרוב נצטרך לחשב מכפלות של אחוזים (למשל- חישוב ריבית דריבית להלוואות) או מכפלות של סיכויים, ואז מדובר מראש בשברים שמכפלתם אף היא שבר.
מה נעשה אם נרצה להגביל מראש את מספר השורות הנשלפות כדי לא להעמיס על המערכת שלא לצורך,
או אם נרצה לשלוף בסדר אקראי כדי לא לראות בכל פעם אותן 20-30 שורות ראשונות,
או נבקש לבצע פעולה מורכבת יחסית על מדגם מייצג מהטבלה?
הרי בדרך כלל כשאנחנו צריכים להציץ בטבלה כדי להתרשם מ"מה יש שם", אנו כמקובל מפעילים פקודת Select, ממתינים בין שבריר שנייה למספר שניות שהריצה תסתיים, מעיינים, וממשיכים הלאה. אם מתברר שבטבלה יש כמה מליוני שורות והשליפה עלולה להימשך זמן רב- לוחצים כמקובל על האייקון האדום שעוצר את זה באמצע ומסתפקים במה שכבר נשלף.
מישהו נוהג אחרת? כנראה שלא..
אילו אופציות יותר מקצועיות עומדות לרשותנו למקרה הצורך?
האפשרות הראשונה היא למיין באופן אקראי בעזרת NewID:
Use AdventureWorks;
Go
Select Top 20 *
From Sales.Customer
Order By NewID();
אפשר לשלוף את כולן ואפשר לשלוף את חלקן בעזרת Top, עובד כמו שצריך, והבעייה היחידה היא שהמערכת באמת ממיינת את הטבלה, ולא בטוח שלשלוף מליון שורות לטבלה יותר גרוע מלמיין אותן..
למערכת יש פתרון נוסף, פחות מוכר, ועם יותר פונקציונליות:
Select *
From Sales.Customer TableSample(20 Rows);
המערכת מבצעת במקרה זה Scan על הטבלה, ובוחרת אקראית 20 שורות מבלי למיין; וליתר דיוק- היא כנראה בוחרת נקודות אקראיות ושולפת רצף של שורות החל מכל אחת (או משהו בסגנון); כל זה בכפוף לחלק היחסי של 20 מכלל השורות. אני מציין זאת כי לרוב לא יחזרו 20 שורות בדיוק אלא בממוצע: לעיתים יותר, לעיתים פחות, ולעיתים בכלל לא.
באופן דומה ניתן לבחור להציג אחוז מסויים משורות הטבלה:
Select *
From Sales.Customer TableSample(2 Percent);
במקרה זה חוזרות לי תמיד כפולות שלמות של 188 שורות (מתוך 19185), כאשר כל כפולה היא רציפה (שימו לב שבצילום המסך ה-CustomerID רציפים), ו-188 הוא ככל הנראה מספר השורות ב-Page (בנ"ל 103 Pages), וזה נותן רמז לגבי האופן בו המנגנון פועל.
במילים אחרות- לא רק השורות החוזרות עצמן הן אקראיות, אלא גם מספר השורות החוזרות הוא אקראי..
מכיוון שבכל שליפה יחזרו שורות אחרות, יתכן מצב בו נרצה שאותן X שורות אקראיות יחזרו בכל פעם; למשל אם מריצים תהליך על מדגם מהטבלה, מדבּגים, מתקנים, ורוצים להריץ אותו שוב על אותו מדגם לביקורת; או אולי אם מבצעים Join של מדגם מהטבלה עם עצמו, ורוצים ששני המדגמים יהיו זהים כדי שהחיתוך לא יהיה ריק.
במקרה כזה נוכל להפעיל את השליפה עם האופרטור Repeatable ומספר אקראי כלשהו, ואז עבור אותו מספר אקראי יחזור תמיד אותו סט (לצורך ההדגמה אני מריץ אותה שליפה פעמיים עם המספר האקראי 230):
Select *
From Sales.Customer TableSample(20 Rows)
Repeatable(230);
כדאי להסתייג ולציין שהשימוש באופרטור TableSample מוגבל לטבלאות משתמש בלבד, ולא נוכל לפנות בעזרתו לטבלאות מערכת, פונקציות המחזירות טבלה וכו';
ובנוסף- פילטור התוצאות בעזרת Where יפעל על השליפה בדיעבד ולכן יחזרו כנראה פחות שורות ממה שציינו.
נסיונות לעזור לאופטימייזר של ה-SQL Server משול לעיתים לניסיון המיתולוגי לעזור לזקנה לחצות את הכביש: גרמנו יותר תועלת מנזק.. נכון שלא תמיד זה כך, ולכן יש להפעיל שיקול דעת.
הפעם דוגמה מתי לא כדאי: נתונות שתי טבלאות- אחת גדולה (100,000 שורות) ואחת קטנה (33 שורות) ואנחנו מעוניינים לבצע Join בינהן.
העמודה בטבלה הקטנה איתה מתבצע ה-Join היא יחודית ולכאורה היא Primary Key, אך לטבלאות לא הוגדרו אינדקסים.
אנחנו רוצים לעזור לאופטימייזר: הוא הרי יבצע Scan לטבלה הגדולה, ולכל שורה יחפש התאמה בטבלה הקטנה; ומכיוון שהוא אינו "יודע" שיכולה להיות רק התאמה אחת בקטנה- הוא לא יעצר כשימצא אלא ימשיך לחפש וחבל..
היה ראוי ליצור אינדקסים מתאימים ולתת לאופטימייזר להשתמש במידע השלם על פי הבנתו, אבל אנחנו מנסים בטעות להחליט במקומו כיצד לעבוד..
ניצור שתי טבלאות:
If Object_Id('messages','U') Is Not Null Drop Table messages;
Go
Select *
Into messages
From sys.messages;
Go
If Object_Id('syslanguages','U') Is Not Null Drop Table syslanguages;
Go
Select *
Into syslanguages
From sys.syslanguages;
Go
--טבלה גדולה
Select *
From messages;
--טבלה קטנה
Select *
From syslanguages;
וכעת נבצע Join בארבע דרכים שונות:
--1
Select *
From messages M
Inner Join syslanguages L
On M.language_id=L.lcid;
--2
Select *
From messages M
Cross Apply (Select *
From syslanguages L
Where M.language_id=L.lcid) L;
--3
Select *
From messages M
Inner Join (Select lcid,
Max(langid) langid,
Max(dateformat) dateformat,
Max(datefirst) datefirst,
Max(Upgrade) Upgrade,
Max(name) name,
Max(alias) alias,
Max(months) months,
Max(shortmonths) shortmonths,
Max(msglangid) msglangid
From syslanguages
Group By lcid) L
On M.language_id=L.lcid;
--4
Select *
From messages M
Cross Apply (Select Top 1 *
From syslanguages L
Where M.language_id=L.lcid) L;
שליפה 1 – Join רגיל בין שתי הטבלאות.
שליפה 2 – Join בשיטת Cross Apply (לפי הסינטקס ניכר שההבדלים בינו לבין Join רגיל הם קוסמטיים במקרה זה).
שליפה 3 – ניסיון "לעזור" ל-Join הרגיל: נבצע Group By על הטבלה הקטנה כדי "לאלץ" אותה להיות עם Primary Key (זה לא ישנה אותה אבל ירמוז למערכת שכעת היא כזו).
שליפה 4 – ניסיון "לעזור" ל-Cross Apply על ידי הוספת האופרטור Top כדי "לאלץ" את המערכת להסתפק בשורה המתאימה הראשונה ולא לחפש עוד התאמות ללא צורך.
כפי שאפשר לראות מה-Execution Plan – שתי השליפות הראשונות זהות,
השליפה השלישית מעט פחות טובה מהן- השימוש ב-Group By לא הביא תועלת ואולי אף פגע מעט בביצועים (התווספו מיון ואגרגציה),
והשליפה הרביעית גרועה משלוש הקודמות באופן ניכר, וזה מעורר תמהון:
הרי לשימוש ב-Top אמורה להיות רק תועלת ובוודאי שלא נזק כזה..
הסיבה העיקרית לכך נלמדת מה-Properties של ה-Table Scan של הטבלה הקטנה: בעוד שבשלוש השליפות הראשונות התבצע Table Scan אחד שעם התוצאות שלו התבצע Hash March עם הטבלה הגדולה, בשליפה הרביעית התבצעו 97526 Table Scans (במילים- לא אחד אלא תשעים ושבעה אלף חמש מאות עשרים ושישה!) כמספר השורות בטבלה הגדולה.
מתברר שהמערכת מחפשת בטבלה הקטנה Top 1 לכל שורה מהטבלה הגדולה, מכיוון שאין לה סיבה להבין שמספר ערכי ה-Top מוגבל ולכל Language_id מהטבלה הגדולה יחזור אותו lcid מהקטנה.
בקיצור- הטענו את האופטימייזר, וכמו שאמא שלי נהגה לנזוף בי כשהייתי ילד: אם אתה רוצה לעזור- עשה מה שמבקשים ממך (=צור אינדקסים מתאימים), או שלפחות אל תפריע (=השימוש הנפסד באופרטור Top)..
ב-Execution Plans אנחנו מוצאים לא פעם את החיוויים הנ"ל צמודים ל-Hash Match או ל-Nested Loops.
מה המשמעות שלהם?
לגבי Join, Left Join, Right Join לא ארחיב כאן ורק אזכיר שהם מחזירים את כל ההתאמות בין שתי טבלאות או סטים בכפוף להגדרות והתנאים, ואציין כאן שתי דוגמאות מפוסט קודם:
--1
Select *
From messages M
Inner Join syslanguages L
On M.language_id=L.lcid;
--2
Select *
From messages M
Cross Apply (Select *
From syslanguages L
Where M.language_id=L.lcid) L;
הסינטקס שונה אך מדובר בפעולות Join זהות במקרה זה: כל ההתאמות בין הטבלה העליונה לתחתונה מוצגות.
Semi Join ("חצי" Join, Join חלקי..) הוא פעולה שמחזירה את כל השורות מהטבלה הראשונה (Left Semi Join) / מהטבלה השניה (Right Semi Join) שיש להן התאמה בטבלה השניה / הראשונה בהתאם. כלומר- לא כל ההתאמות בין שתי הטבלאות חוזרות, אלא כל השורות מהטבלה האחת שיש להן התאמה באחרת (השורה תוצג פעם אחת גם אם יש לה מספר התאמות).
רוצה לומר שהמערכת מחפשת לכל שורה בטבלה האחת התאמה באחרת, ואם היא מצאה- היא עוצרת את החיפוש ומציגה אותה.
Anti Semi Join ("חצי" Join הפוך) הוא פעולה שמחזירה את כל השורות מהטבלה הראשונה (Left Anti Semi Join) / מהטבלה השניה (Right Anti Semi Join) שאין להן התאמה בטבלה השניה / הראשונה בהתאם. המערכת מחפשת לכל שורה בטבלה האחת התאמות באחרת, אם היא מוצאת היא עוצרת את החיפוש ולא מציגה אותה, ואם היא לא מוצאת – היא כן מציגה אותה.
דוגמאות, ונתחיל מיצירת שתי טבלאות – אחת גדולה ואחת קטנה - להדגמה:
If Object_Id('messages','U') Is Not Null Drop Table messages;
Go
Select *
Into messages
From sys.messages;
Go
If Object_Id('syslanguages','U') Is Not Null Drop Table syslanguages;
Go
Select *
Into syslanguages
From sys.syslanguages;
Go
--טבלה גדולה
Select *
From messages;
--טבלה קטנה
Select *
From syslanguages;
Right Semi Join
--Right Semi Join
Select *
From messages M
Where Exists (Select 1
From syslanguages L
Where L.msglangid=M.language_id);
Select *
From messages
Where language_id In (Select msglangid
From syslanguages);
לשתי השליפות הנ"ל אותו Execution Plan: המערכת מבצעת Scan לטבלה השניה/התחתונה (messages) ולכל שורה מחפשת התאמה ב-Hash Table של הראשונה, ואם יש- מציגה אותה.
Right Anti Semi Join
--Right Anti Semi Join
Select *
From messages M
Where Not Exists (Select 1
From syslanguages L
Where L.msglangid=M.language_id);
Select *
From messages
Where language_id Not In (Select msglangid
From syslanguages);
שוב- שני Execution Plans זהים לשתי השליפות: המערכת מבצעת Scan לטבלה השניה/התחתונה (messages) ולכל שורה מחפשת התאמה ב-Hash Table של הראשונה, ואם יש- אינה מציגה אותה.
Left Semi Join
--Left Semi Join
Select *
From syslanguages L
Where Exists (Select 1
From messages M
Where L.msglangid=M.language_id);
Select *
From syslanguages
Where msglangid In (Select language_id
From messages);
Select msglangid
From syslanguages
Intersect
Select language_id
From messages;
Select language_id
From messages
Intersect
Select msglangid
From syslanguages;
לשליפות הראשונה והשניה אותם Execution Plans, והם די דומים ל-Right Semi Joins שפגשנו קודם מבחינת הסינטקס אך לא מבחינת ה-Execution Plans שכן הטבלה הגדולה messages והטבלה הקטנה syslanguages החליפו מקומות והפניות החוזרות ונשנות לגדולה מחייבות תחליף לאינדקס שאינו קיים וזה ה-Table Spool (טבלה אגרגטיבית זמנית שנוצרת ב-tempdb).
מה שמעניין יותר הוא שתי השליפות האחרונות בהן פקודת Intersect יוצרת Join בין העמודות המתאימות בשתי הטבלאות ומסננת כפילויות. למרות שהתוצאות זהות והאופרטור Intersect הוא סימטרי- המערכת בחרה במקרה הראשון להתחיל עם הטבלה הקטנה, למיין אותה ולכל שורה לחפש התאמה בגדולה (לאחר שעברה אגרגציה למניעת כפילויות); ובמקרה השני להתחיל דווקא מהגדולה, לבצע לה אגרגציה, ולחפש התאמות בקטנה מבלי למיין אותה תחילה.
אפשר להבין שכדי למנוע כפילויות המערכת בוחרת למיין (כדי לאתר אותם בקלות) או לבצע אגרגציה.
בשני המקרים זה Left Semi Join אבל בכל פעם מתחילים מטבלה אחרת, וזה קצת מפתיע לאור העובדה ש-Intersect הוא אופרטור סימטרי והסדר אינו אמור להיות משמעותי עבורו.
Left Anti Semi Join
--Left Anti Semi Join
Select *
From syslanguages L
Where Not Exists (Select 1
From messages M
Where L.msglangid=M.language_id);
Select *
From syslanguages
Where msglangid Not In (Select language_id
From messages);
Select msglangid
From syslanguages
Except
Select language_id
From messages;
Select language_id
From messages
Except
Select msglangid
From syslanguages;
גם כאן שתי השליפות הראשונות זהות מבחינת ה-Executions Plans והפלטים, ומה שמעניין הוא שתי השליפות האחרונות:
בשתיהן התבצעה פעולת Except (חיסור של הטבלה השניה מהראשונה) שמשעותה דומה בבירור ל-Left Anti Semi Join (להציג את השורות מהראשונה שאינן בשניה). Except אינה סימטרית ולכן ברור מדוע יש הבדל ב-Execution Plans.
לסיכום: יוסף בורג ז"ל – שר הפנים הנצחי – אמר פעם- ימין ושמאל רק חול וחול (ציטוט משיר תמים בהקשר פוליטי),
ובאופן דומה נוכל לומר- Left Anti Semi or Right Anti Semi – כולם אנטישמים!
מה ההבדל בין 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
כלומר- בדטבייס הראשון שה-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
(פונקציית 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
נימוק כבד משקל שיש לקחת בחשבון הוא נושא האינדקסים: עמודות מסוג (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
יצרנו 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
הודעת השגיאה היא לגבי הנסיון ליצור אינדקס על 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 איטיים יותר. לא בדקתי.