הבלוג של גרי רשף

12/08/2011

חידושים ב-TSQL בגרסת Denali–SQL Server 2011

Filed under: Uncategorized — גרי רשף @ 23:18

גרסת 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];

image

התאריך השוטף מוצג במספר פורמטים, בין היתר בפורמט 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,'#,###') [#,###];

image

נעבור לתצוגה חשבונאית:

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];

 

image

ונסיים באחוזים:

Select  Format(0.5,'P') [P],
        Format(0.5,'000.0%') [000.0%];

image

כדאי להדגיש- הכלי הזה מתאים למי שמייצא נתונים לקובץ טקסט, למי ששולח מיילים אוטומטיים ורוצה לעצב אותם נכון ואפילו למי שבונה פקודות 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];

image

אני נזקקתי בעבר לסינטקס פשוט שיאפשר לי לבחור את הגדול מבין שני ערכים ונאלצתי להשתמש ב-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];

image

כן כן- כולל יכולת להבין עברית.

פקודות 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];

image

לא כל כך ברור לי מדוע מיקרוסופט בחרה להחיות את 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;

image

פקודת EOMonth עבור תאריך המתקבל כקלט מחזירה את היום האחרון בחודש:

Select EOMonth(GETDATE()) [EOMonth];

image

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

Select DateAdd(Month,-1,DateAdd(Day,1,EOMonth(GETDATE()))) [BOMonth];

image

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

1. פקודת Concat יודעת להמיר מספרים ותאריכים למחרוזות מבלי לשלוח אותנו לבצע Cast בעצמנו.

2. אם יש Null – הוא אינו הופך את כל המחרוזת ל-Null אלא מצטרף כמחרוזת ריקה.

למשל – ניתן לשרשר את מספרי ה-object_id של כל האובייקטים בדטבייס למחרוזת אחת כך:

 

Declare   @S Varchar(Max);
Select    @S=Concat(@S,',',object_id)
From      sys.objects;
Print     @S;

 

image

הפטנט הזה לשירשור ערכים אינו חדש וקיים גם בגרסאות קודמות, אלא שעד כה היה צורך לאתחל את 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];

image

ולסיום ארבע פונקציות אחיות שמאפשרות ליצור תאריכים מחלקים. למשל- נתונים שלושת המספרים 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];

image

DateTimeFromParts – מקבלת שנה, חודש, יום, שעה, דקה, שניה, אלפיות שניה.

DateTime2FromParts – מקבלת שנה, חודש, יום, שעה, דקה, שניה, חלקי שניה וגודל חלק (במקרה זה 50 חלקים ו-3 מציין שמדובר באלפיות שניה).

DateTimeOffsetFromParts – מקבלת שנה, חודש, יום, שעה, דקה, שניה, חלקי שניה, Offset בשעות, Offset בדקות וגודל חלק (במקרה זה חלקי השניה וגודל החלק כמו קודם, וה-Offset הוא שעתיים קדימה משעון גריניץ' כמקובל בשעון החורף כאן בארץ).

SmallDateTimeFromParts – מקבלת שנה, חודש, יום, שעה, דקה.

TimeFromParts (לא מופיעה בדוגמה) – מקבלת שעה, דקה, שניה, אלפיות שניה.

DateFromParts (לא מופיעה בדוגמה) – מקבלת שנה, חודש, יום.

מודעות פרסומת

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

יצירה של אתר חינמי או בלוג ב־WordPress.com.

%d בלוגרים אהבו את זה: