גרסת CTP השלישית של Denali כללה מספר רב של פקודות ואופציות חדשות ב-TSQL, שמסיבה מעט קורת נחת גם למפתחים ולא רק ל-DBA-ים התשתיתיים.
על פונקציות החלון החדשות אדלג ואולי אסקור אותן יותר באריכות בעתיד, ואתמקד כאן בפקודות הפשוטות יותר.
את הפקודות אציג לא לפי נושאים, אלא לפי ערכן (לעניות דעתי השיפוטית).
נתחיל מהפקודות שגורמות גם לפולנים ביננו לגנוח (ולא מצער), שמאפשרות לנו לעשות בקלות דברים שבעבר סיבכו אותנו וגרמו לי לכתוב פוסטים שכעת אין בהם עוד תועלת..
למשל- הפקודה Format שמאפשרת להציג מספרים או תאריכים בפורמט בו אנו בוחרים, ונתחיל בתאריכים:
Select Getdate() [Getdate],
Format(Getdate(),'dd/MM/yyyy HH:mm:ss.fff') [dd/MM/yyyy HH:mm:ss.fff],
Format(Getdate(),'yyyyMMdd') ['yyyyMMdd],
Format(Getdate(),'dd MMM yyyy','en') [dd MMM yyyy en],
Format(Getdate(),'dd MMMM yyyy','he') [dd MMMM yyyy he];
התאריך השוטף מוצג במספר פורמטים, בין היתר בפורמט yyyyMMdd שהוא הפורמט התקני לשימוש בתאריכים בשאילתות SQL (בשימוש ב-SQL דינאמי),
ובפורמט המאפשר להציג את שם החודש בהתאם ל-Culture בו בחרנו – ולראשונה יש אפשרות לשימוש בעברית (ובעוד כמה מאות שפות- פירוט כאן).
יש לשים לב שמחרוזת הגדרת הפורמט היא Case Sensitive (למשל M מתייחס ל-Month ו-m ל-minute).
נמשיך עם מספרים:
Select Format(0.5,'000.0') [000.0],
Format(0.575,'#.##') [#.##],
Format(10002.5,'N') [N],
Format(1234,'#,###') [#,###];
נעבור לתצוגה חשבונאית:
Select Format(5400,'$#,##0.00;($#,##0.00);Zero') [Positive],
Format(-1500,'$#,##0.00;($#,##0.00);Zero') [Negative],
Format(0,'$#,##0.00;($#,##0.00);Zero') [Zero];
ונסיים באחוזים:
Select Format(0.5,'P') [P],
Format(0.5,'000.0%') [000.0%];
כדאי להדגיש- הכלי הזה מתאים למי שמייצא נתונים לקובץ טקסט, למי ששולח מיילים אוטומטיים ורוצה לעצב אותם נכון ואפילו למי שבונה פקודות SQL דינאמיות;
אך הן לא נועדו למי שכותב דוחות. מי שכותב שליפה שתוצג בדוח של SSRS או כל כלי אחר- שיפרמט את התאריכים והמספרים בכלי עצמו ולא בשליפה, מכיוון שפקודת ה-Format הופכת את הנתונים ל-NVarchar וכעת לא ניתן לסכמם או לבצע חישובי הפרשים וכו'.
פקודה מועילה נוספת היא IIF שמקבילה ל-Decode של אוראקל, והיא מאפשרת לבצע פעולות If מיידיות באופן אלגנטי ללא צורך להסתרבל עם Select Case .. When .. Else ..End, למשל- בדיקה האם אנחנו במחצית הראשונה או השניה של השנה:
Select IIf(Month(Getdate())>=7,'Second half of the year','First half of the year') [Today];
אני נזקקתי בעבר לסינטקס פשוט שיאפשר לי לבחור את הגדול מבין שני ערכים ונאלצתי להשתמש ב-Select Case, אך מעתה אוכל לעשות זאת בעזרת IIf (כלומר- לא ממש מרגע זה שכן במקום העבודה שלי – מוסד בנקאי ידוע – משתמשים עדיין ב-SQL 2005 ול-2011 יעברו אולי בסוף העשור..).
מקבץ של שלוש פקודות מתווסף ל-Convert (או Cast) הוותיקה ומצרף אליה את Try_Convert, Parse, Try_Parse.
Parse דומה ל-Convert אך חכמה יותר, בעיקר בכל הקשור לתרגום תאריכים טקסטואליים לתאריך תקני של SQL Server. למשל:
Select Parse('15 Feb 2011' As DateTime) [15 Feb 2011],
Parse('15 פברואר 2011' As Date Using 'he') [15 פברואר 2011 he];
כן כן- כולל יכולת להבין עברית.
פקודות Try_Convert ו-Try_Parse מאפשרות לבצע Convert או Parse מבלי ליפול אם ההמרה נכשלת – במקרה כזה נקבל Null. למשל-
Select Try_Convert(DateTime,'20110215') [Correct date],
Try_Convert(DateTime,'20111515') [Wrong date],
Try_Parse('Tuesday, Feb 15 2011' As DateTime) [Correct day of week],
Try_Parse('Sunday, Feb 15 2011' As DateTime) [Wrong day of week];
לא כל כך ברור לי מדוע מיקרוסופט בחרה להחיות את convert הוותיקה ולא להרחיב את Cast הידידותית שהסינטקס שלה דומה לזה של Parse,
ובנוסף- הפונקציות הנ"ל (וגם חלק מהאחרות שמתבססות על ספריות ה-dot.net) עדיין אינן יציבות מספיק וקורסות לעיתים ביציאות שהן לא צפו מראש.
עוד פקודה שיכולה להיות שימושית היא Choose, המקבלת אינדקס ורשימה ומחזירה מהרשימה את הפריט שמציין האינדקס. למשל –('Choose(2,'A','B','C תחזיר את B שהוא השני ברשימה (מותר עד 254 פריטים). יכול להיות שימושי בעמודות המכילות מידע כמו 0 או 1 המציינות לא וכן או זכר ונקבה, או אולי עמודה בה מצויין מספר של חודש, ואנחנו מעוניינים לפרש זאת. למשל- באיזה חודש נוצרו האובייקטים השונים בדטבייס (משימה טכנית להמחשה):
Select Choose(Month(create_date),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') Month,
*
From sys.objects;
פקודת EOMonth עבור תאריך המתקבל כקלט מחזירה את היום האחרון בחודש:
Select EOMonth(GETDATE()) [EOMonth];
הפקודה הזו דווקא שימושית ונוחה וחוסכת כל מיני פתרונות מסורבלים, רק שלא ברור מדוע לא בחרו בפקודה המציגה את היום הראשון בחודש או אולי את שתי האפשרויות גם יחד (סה"כ זה כמעט אותו קוד עבור מי שפיתח את הפתרון). בכל מקרה- ניתן לעבור מהיום האחרון בחודש לראשון על ידי DateAdd שבעזרתה מוסיפים יום ומורידים חודש:
Select DateAdd(Month,-1,DateAdd(Day,1,EOMonth(GETDATE()))) [BOMonth];
ונסיים את החלק הזה עם פקודת ה-Concat שמקבלת מספר מחרוזות ומחברת אותן. לכאורה יכולנו לעשות זאת בעצמנו בעזרת האופרטור +, אלא שיש לה שני יתרונות:
1. פקודת Concat יודעת להמיר מספרים ותאריכים למחרוזות מבלי לשלוח אותנו לבצע Cast בעצמנו.
2. אם יש Null – הוא אינו הופך את כל המחרוזת ל-Null אלא מצטרף כמחרוזת ריקה.
למשל – ניתן לשרשר את מספרי ה-object_id של כל האובייקטים בדטבייס למחרוזת אחת כך:
Declare @S Varchar(Max);
Select @S=Concat(@S,',',object_id)
From sys.objects;
Print @S;
הפטנט הזה לשירשור ערכים אינו חדש וקיים גם בגרסאות קודמות, אלא שעד כה היה צורך לאתחל את S@ למחרוזת ריקה כדי שניתן יהיה לשרשר אליו ערכים מבלי שהכל יהפוך ל-Null,
והיה צורך לבצע Cast ל-object_id למחרוזת.
לכל הפקודות החדשות הנ"ל שלכל אחת יהיה בוודאי שימוש בזמן ובמקום המתאימים, יש עוד כמה שקשה לי למצוא להן שימושים מתאימים בשלב זה, אבל מה איכפת לי שיהיו?
נתחיל מפונקציית ה-Log: עד כה היו שתי פונקציות שאיפשרו לחשב לוגים- פונקציית Log לפי הבסיס הטבעי (e) ו-Log10 לפי בסיס 10. מעתה ניתן לציין בפונקצייה Log את הבסיס וכך להשתמש גם בבסיסים אחרים. למי זה חשוב? לא יודע, אבל אם נותנים אנחנו לקוחים..
לוג לפי בסיס 2 יכול אולי לעניין מישהו. למשל עץ בינארי בעל 1,000,000 נקודות קצה יהיה בעל (Log(1000000 (לפי בסיס 2) רמות, שזה בערך 20. נניח- אם מישהו יבחר מספר בין 1 ל-1,000,000 ועלי לנחש מהו בשאלות כן/לא (האם הוא גדול מ-500,000?..) – בתוך 20 ניחושים אני אמצא אותו.
פעם כדי למצוא את (Log(1000000 (לפי בסיס 2) הייתי צריך להעביר בסיס, וכעת ניתן לחשב ישר:
Select Log(1000000)/Log(2) [Old option],
Log(1000000,2) [New option];
ולסיום ארבע פונקציות אחיות שמאפשרות ליצור תאריכים מחלקים. למשל- נתונים שלושת המספרים 17 8 2011 ואנחנו מעוניינם ליצור מהם את התאריך 17/08/2011 וכו'.
Select DateTimeFromParts(2011,8,17,15,10,11,50) [DateTimeFromParts],
DateTime2FromParts(2011,8,17,15,10,11,50,3) [DateTime2FromParts],
DateTimeOffsetFromParts(2011,8,17,15,10,11,50,2,0,3) [DateTimeOffsetFromParts],
SmallDateTimeFromParts(2011,8,17,15,10) [SmallDateTimeFromParts];
DateTimeFromParts – מקבלת שנה, חודש, יום, שעה, דקה, שניה, אלפיות שניה.
DateTime2FromParts – מקבלת שנה, חודש, יום, שעה, דקה, שניה, חלקי שניה וגודל חלק (במקרה זה 50 חלקים ו-3 מציין שמדובר באלפיות שניה).
DateTimeOffsetFromParts – מקבלת שנה, חודש, יום, שעה, דקה, שניה, חלקי שניה, Offset בשעות, Offset בדקות וגודל חלק (במקרה זה חלקי השניה וגודל החלק כמו קודם, וה-Offset הוא שעתיים קדימה משעון גריניץ' כמקובל בשעון החורף כאן בארץ).
SmallDateTimeFromParts – מקבלת שנה, חודש, יום, שעה, דקה.
TimeFromParts (לא מופיעה בדוגמה) – מקבלת שעה, דקה, שניה, אלפיות שניה.
DateFromParts (לא מופיעה בדוגמה) – מקבלת שנה, חודש, יום.
הבלוג של גרי רשף 
כתיבת תגובה