גרי רשף

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

LEN (string_expression): Returns the number of characters of the specified string expression, excluding trailing blanks.

Note: To return the number of bytes used to represent an expression, use the DATALENGTH function.

ומה אומר ה-BOL על DataLength?

DATALENGTH (expression) : Returns the number of bytes used to represent any expression.

כמו בהרבה מקרים אחרים- יש לקרוא את האותיות הקטנות: Len סופר תווים (ללא רווחים מובילים ומסיימים)
ו-DataLength סופר Bytes.
ההבדל הוא שבסוגי נתונים כדוגמת Char או Varchar כל תו הוא Byte,
אולם ב-NChar או NVarchar כל תו הוא שני Bytes:

Select  Len('ABCD ') [Len_Varchar],

        DataLength('ABCD ') [DataLen_Varchar],

        Len(N'ABCD ') [Len_NVarchar],

        DataLength(N'ABCD ') [DataLen_NVarchar];

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

Declare @S NVarchar(Max);

Select  @S='ABCD ';

Select  Len('#'+@S+'#')-2 [Length];

לחילופין אפשר להמיר את כל הרווחים לתווים אחרים על ידי Replace או לבצע Cast ל-Varchar ואז להשתמש ב-DataLength ועוד.
למי שיש רעיון יותר פשוט – אשמח לשמוע..

כל מי שעובר מ-SQL Server ל-Oracle או להיפך- הולך קצת לאיבוד בהתחלה בגלל ההבדלים בהיררכיית האובייקטים בין שתי הסביבות: ב-Oracle אין דטבייסים, ואת מקומם ממלאות הסכימות שאינן רק דרך לארגן את האובייקטים אלא מציינות בעלות עליהם, ובמילים אחרות- ב-Oracle אין הפרדה בין דטבייס & סכימה & משתמש, הם שלושתם אותו הדבר (מקווה שאינני מפספס שום דבר- לא ראיתי Oracle בשנה האחרונה), וכשמשתמש יוצר טבלה- היא נוצרת כברירת מחדל בסכימה שלו והוא הבעלים (Owner) שלה.

ב-SQL Server הדברים שונים. עד גרסה 2000 לאובייקטים היה Owner, אבל מאז גרסה 2005 אין זה כך, האובייקטים שייכים לסכימה בדטבייס ולא למשתמש, ורק הסכימה עצמה שייכת למשתמש (בדרך כלל זה ה-dbo שהוא ה-Owner של הדטבייס והסכימה).
למשל- אם מבטלים משתמש על ידי Drop User – אין כל בעייה עם האובייקטים שהוא יצר והם ימשיכו להתקיים בלעדיו, אך כן תהיה בעייה עם הסכימות המשוייכות לו.
דוגמה:

Create Login MyLogin With Password='MyLogin',Check_Policy=Off;

Go

Create User MyUser For Login MyLogin With Default_Schema=MySchema;

Go

Create Schema MySchema Authorization MyUser;

Go

Select  P.name [User_Name],

        P.type_desc,

        L.name Login_Name,

        P.default_schema_name,

        S.name [Owned_Schema_Name]

From    sys.database_Principals P

Inner Join sys.syslogins L

        On P.sid=L.sid

Left Join sys.schemas S

        On P.principal_id=S.principal_id

Where   P.name='MyUser';

יצרנו Login (המשתמש ברמת השרת),
User המשוייך לו (המשתמש ברמת הדטבייס),
Schema המשוייכת ל-User.
השליפה מציגה את האובייקטים שיצרנו,
כאשר יש להבדיל בין Default_Schema_Name המציינת מהי סכימת ברירת המחדל של ה-User (אם לא צויין ב-Create User אזי זו סכימה dbo),
ו-Owned_Schema_Name המציינת את הבעלות על הסכימה (אם לא צויין ב-Create Schema אזי בעל הסכימה הוא dbo).
במקרה זה MySchema נקבעה כברירת המחדל של MyUser עוד לפני שהיא נוצרה, ומכאן שניתן גם לבטל סכימה שמוגדרת כברירת מחדל למשתמשים ללא כל התראה, ולהסתכן בהודעות שגיאה כשינסו לפנות אליה במרומז.
בנוסף- dbo מציין גם User וגם סכימה, ולא במפתיע היא ברירת המחדל שלו והוא ה-Owner שלה..

כדי ליצור אובייקטים – למשל טבלאות - יש לתת ל-User הרשאות מתאימות: גם הרשאה ליצור טבלה וגם הרשאה להשתמש בסכימה.
הרשאה ליצור טבלה נותנים כך:

Grant Create Table To MyUser;

Go

הרשאות לסכימה אין צורך לתת מכיוון ש-MyUser הוא ה-Owner של MySchema ויש לו הרשאות מלאות, אך אם לא- ניתן למשל כך:

Grant Control On Schema::MySchema To MyUser;

Go

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

Execute As User='MyUser';

Go

Create Table MyTable(I Int);

Go

Revert;

Select  name Table_Name,

        Schema_Name(schema_id) [Schema_Name]

From    sys.tables

Where   name='MyTable';

יצרנו טבלה בתור MyUser והיא נוצרה ב-MySchema.

לסיום נבטל בסדר הפוך את האובייקטים שיצרנו:

Drop Table MySchema.MyTable;

Go

Drop Schema MySchema;

Go

Drop User MyUser;

Go

Drop Login MyLogin;

Go

את הטבלה יש לבטל כדי שיהיה ניתן לבטל את הסכימה בתוכה היא נמצאת.
את הסכימה יש לבטל כדי שיהיה ניתן לבטל את ה-User שהוא ה-Owner שלה, אם כי אין קשר ישיר של בעלות בין ה-User והטבלה, ולו היא הייתה נוצרת בסכימה שאינה שלו- לא היה צורך לבטלה.
את ה-User יש לבטל כדי שניתן יהיה לבטל את ה-Login.

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

Use tempdb;

Go

If Object_Id('Try','U') Is Not Null Drop Table Try;

Go

Create Table Try(I Float);

Go

Insert

Into   Try

Select 1.00 Union All

Select 1.01 Union All

Select 1.02 Union All

Select 1.03 Union All

Select 1.04 Union All

Select 1.05 Union All

Select 1.06 Union All

Select 1.07 Union All

Select 1.08 Union All

Select 1.09;

Go

Select  *

From    Try;

Go

ונבצע שליפה עם כמה פעולות שחוזרות על עצמן תוך בדיקת ה-Execution Plan:

Select  *,

        I+1 I1

From    Try

Where   I+1>0

Order By I+1;

מה אנחנו רואים:

1. Table Scan הכולל פילטור ובמהלכו חושב מן הסתם הערך I+1.
2. Sort- התבצע מיון שכולל כנראה חישוב מחדש של I+1.
3. Compute Scalar- חושב שוב הערך I+1 להצגה בפלט.

האמנם? אולי המערכת מספיק חכמה כדי לדעת ש- I+1 הוא חישוב סרק במקרה זה?

נבצע את הכל מחדש עם טבלה בעלת Primary Key (כלומר- אינדקס על העמודה I):

If Object_Id('Try1','U') Is Not Null Drop Table Try1;

Go

Create Table Try1(I Float Primary Key);

Go

Insert

Into   Try1

Select 1.00 Union All

Select 1.01 Union All

Select 1.02 Union All

Select 1.03 Union All

Select 1.04 Union All

Select 1.05 Union All

Select 1.06 Union All

Select 1.07 Union All

Select 1.08 Union All

Select 1.09;

Go

Select  *,

        I+1 I1

From    Try1

Where   I+1>0

Order By I+1;

Go

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

Select *,

       Sqrt(I) I1

From   Try1

Where  Sqrt(I)>0

Order By Sqrt(I);

Go

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

כיצד נדע עם Compute Scalar הוא חישוב בודד או שלושה חישובים שונים? נשווה בין שני Execution Plans של שליפות מהטבלה, כשהראשונה כוללת חישוב פשוט, והשניה מאה (100) חישובים מורכבים:

Select *,

       I+1

From   Try;

Select  *,

        Power(I,1),Power(I,2),Power(I,3),Power(I,4),Power(I,5),Power(I,6),Power(I,7),Power(I,8),Power(I,9),Power(I,10),

        Power(I,11),Power(I,12),Power(I,13),Power(I,14),Power(I,15),Power(I,16),Power(I,17),Power(I,18),Power(I,19),Power(I,20),

        Power(I,21),Power(I,22),Power(I,23),Power(I,24),Power(I,25),Power(I,26),Power(I,27),Power(I,28),Power(I,29),Power(I,30),

        Power(I,31),Power(I,32),Power(I,33),Power(I,34),Power(I,35),Power(I,36),Power(I,37),Power(I,38),Power(I,39),Power(I,40),

        Power(I,41),Power(I,42),Power(I,43),Power(I,44),Power(I,45),Power(I,46),Power(I,47),Power(I,48),Power(I,49),Power(I,50),

        Power(I,51),Power(I,52),Power(I,53),Power(I,54),Power(I,55),Power(I,56),Power(I,57),Power(I,58),Power(I,59),Power(I,60),

        Power(I,61),Power(I,62),Power(I,63),Power(I,64),Power(I,65),Power(I,66),Power(I,67),Power(I,68),Power(I,69),Power(I,70),

        Power(I,71),Power(I,72),Power(I,73),Power(I,74),Power(I,75),Power(I,76),Power(I,77),Power(I,78),Power(I,79),Power(I,80),

        Power(I,81),Power(I,82),Power(I,83),Power(I,84),Power(I,85),Power(I,86),Power(I,87),Power(I,88),Power(I,89),Power(I,90),

        Power(I,91),Power(I,92),Power(I,93),Power(I,94),Power(I,95),Power(I,96),Power(I,97),Power(I,98),Power(I,99),Power(I,000)

From    Try;

בשני המקרים ה-Estimated CPU cost הוא 0.000001, ואני מתרשם שזה "מחיר קבוע" שאינו תלוי בסיבוכיות החישוב ובכמות החישובים, ולכן לא ניתן ללמוד מזה דבר.

אם כך נסתכל על ה- Statistics Timeונשווה בין חישוב בודד של החזקה לחישוב מרובה אך זהה שלה:

Set Statistics Time On

Select  *,

        Power(I,10)

From    Try;

Select  *,

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),

        Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10),Power(I,10)

From Try;

Set Statistics Time Off

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

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

Select  *,

        (Select Max(I) From Try T2 Where T2.I<=T1.I) I2

From    Try T1

Where   (Select Max(I) From Try T2 Where T2.I<=T1.I)>=0

Order By (Select Max(I) From Try T2 Where T2.I<=T1.I);

ניתן לראות שחישוב תת השאילתה מתבצע שלוש פעמים, למרות שמדובר באותה תת שאילתה (הקפדתי להעתיק אותה על ידי Ctrl C + Ctrl V כדי לוודא שאין הבדלים בטקסט), ובנוסף- בדיקה בעזרת Statistics IO אישרה את ממצאי ה-Execution Plan.

מה יקרה אם נמקם את ה-Select ללא הפילטור והמיון בתוך CTE ונפנה לתוצאה של החישוב?

With T As

(Select *,

        (Select Max(I) From Try T2 Where T2.I<=T1.I) I2

From    Try T1)

Select  *

From    T

Where   I2>=0

Order By I2;

ניתן לראות שחישוב המשנה המתבצע שלוש פעמים בשליפה הראשונה קורה פעם אחת בלבד בשניה.

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

פוסט של משה טייכר בשם שליפת מילים מתוך טקסט בTSQL שהתפרסם לאחרונה עודד אותי לפרסם תגובה עקיפה בפוסט הסרת תווים מיותרים בו הצעתי דרך טובה יותר (לטעמי) לבצע מטלה מקדימה שלו, ואילו הפוסט הזה הוא בעקבות פטנט יפה שלמדתי ממנו – ביצוע Split בעזרת XML, ואשר סייע לי לפתור באופן אלגנטי בעייה שנתקלתי בה בעבר.

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

If Object_Id('tempdb..#T_Mehirot','U') Is Not Null Drop Table #T_Mehirot;

Go

Create Table #T_Mehirot(ID Int Identity,

                        Moher Varchar(10),

                        Prit Varchar(10),

                        Camut Int);

Go

Insert

Into   #T_Mehirot

Select 'Hila','Milk',10 Union All

Select 'Hila','Bread',5 Union All

Select 'Hila','Butter',2 Union All

Select 'Hila','Butter',1 Union All

Select 'Gal','Milk',6 Union All

Select 'Gal','Milk',1 Union All

Select 'Gal','Bread',3 Union All

Select 'Gal','Butter',2;

Go

Select  *

From    #T_Mehirot;

Go

If Object_Id('tempdb..#T_Amalot','U') Is Not Null Drop Table #T_Amalot;

Go

Create Table #T_Amalot(Moher Varchar(10),

                       Pritim Varchar(Max));

Go

Insert

Into   #T_Amalot

Select 'Hila','Milk,Butter,Bread' Union All

Select 'Gal','Bread';

Go

Select  *

From    #T_Amalot;

Go

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

Select  *

From    #T_Mehirot M

Inner Join #T_Amalot A

        On M.Moher=A.Moher

        And ','+A.Pritim+',' Like '%,'+M.Prit+',%';

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

הבעייה הקשה יותר היא מה לעשות כשיש שתי עמודות עם רשימות מופרדות בפסיקים, ואז לא מדובר בשאלה יש/אין התאמה, אלא מה מתאים; ובמילים פשוטות יותר- להכין רשימת חיתוך של שתי הרשימות עם האיברים המשותפים:

If Object_Id('tempdb..#Tbl','U') Is Not Null Drop Table #Tbl;

Go

Create Table #Tbl(ID Int Identity,

                  L1 Varchar(Max),

                  L2 Varchar(Max));

Go

Insert

Into   #Tbl

Select '1,3,5,7,9','1,2,3,4,5' Union All

Select 'Avi,Batya,Gal,Dan','Gal,Dan,Batya,Avi' Union All

Select '','abc,def,ghi' Union All

Select ' xxx,yyy','xxx, yyy';

Go

Select  *

From    #Tbl;

Go

במקרה זה מדובר בדוגמה טכנית ללא סיפור כיסוי מקצועי: בשורה הראשונה יש התאמה חלקית בין הרשימות, בשניה התאמה מלאה אך בסדר שונה, בשלישית רשימה ריקה ורשימה עם תוכן, וברביעית רשימות דומות אבל עם רווחים מובילים שאמורים למנוע התאמות.
במקרה זה אנחנו לא רק נאלצים לכאורה לבצע Split, אלא חייבים; אבל כדי לא להסתבך ביצירת אובייקטים כמו פונקציה המבצעת Split וכו' אשתמש בפטנט שלמדתי מהפוסט הנ"ל לביצוע Split בעזרת XML, אבצע Join בין הרשימות, ואהפוך את רשימת החיתוך חזרה למחרוזת – שוב בעזרת XML.
במילים פשוטות: שימוש כפול ב-XML לביצוע Split של רשימה לסט, וביצוע שרשור של סט לרשימה:

With T As

(Select *,

        Cast('<Root><S>'+Replace(L1,',','</S><S>')+'</S></Root>' As XML) XML1,

        Cast('<Root><S>'+Replace(L2,',','</S><S>')+'</S></Root>' As XML) XML2

From    #Tbl)

Select  *,

        Stuff((Select ','+L1 As [text()]

              From    (Select S1.value('.','Varchar(MAX)') L1

                      From    XML1.nodes('//Root/S') Records1(S1)

                      Inner Join XML2.nodes('//Root/S') Records2(S2)

                              On S1.value('.','Varchar(MAX)')=S2.value('.','Varchar(MAX)')) T2

              Order By T2.L1

              For XML Path('')),1,1,'') L

From    T

Order By ID;

ההמרה ל-XML נעשית בתוך ה-CTE,
ה-Join מתבצע בין שתי הרשימות שהומרו לסטים,
הסט המקבל הופך חזרה למחרוזת בעזרת For XML Path,
והתוצאה מוצגת בעמודה הימנית L.

יש לנו טקסט אותו אנחנו רוצים לנקות מתווים מיותרים כמו #\|&^ וכו' או להשאיר רק תווים אלפה-נומריים.

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

Declare @S Varchar(Max);

--"מחרוזת טכנית לדוגמה שכוללת הרבה "לכלוך

Select  @S='abc d1,$ 23A-B`CD''א]ב%ג[דה';

Select  @S=Replace(@S,' ',''), --רווח

        @S=Replace(@S,',',''),

        @S=Replace(@S,'$',''),

        @S=Replace(@S,' ',''), --{TAB}

        @S=Replace(@S,'-',''),

        @S=Replace(@S,'`',''),

        @S=Replace(@S,'''',''), --גרש יש להכפיל

        @S=Replace(@S,']',''),

        @S=Replace(@S,'%',''),

        @S=Replace(@S,'[','');

Select @S;

אפשר לשים לב לתופעה מעניינת: פקודת ה-Replace פועלות בטור – זו אחר זו – למרות שכולן באותו Select ואמורות להתבצע במקביל ולבטל זו את זו. הפתרון הזה אינו מוצלח מכיוון שכל הוספה או גריעה של תו מהרשימה מחייבת לתקן את הקוד, ובהנחה שקוד זה הוא חלק מפרוצדורה או פונקציה – זה לא רצוי וגם הופך אותו לשימושי רק בחלק מהמקרים. היה ראוי לקבל את בתווים למחיקה במחרוזת, למשל כך-

Declare @S Varchar(Max),

        @No Varchar(Max);

Select  @S='abc d1,$ 23A-B`CD''à]á%â[ãä', --"מחרוזת טכנית לדוגמה שכוללת הרבה "לכלוך

        @No='-` ~!@#$%^&*()_=+{},<.>/[?{}\|;:'']';

While @No<>''

    Select  @S=Replace(@S,Left(@No,1),''),

            @No=Stuff(@No,1,1,'');

Select @S;

פתרון שאינו כולל שימוש בלולאה- כנראה שאין, ולכן אם נרצה "לנקות" עמודה בטבלה – נצטרך לבנות פונקציה ולהשתמש בה.

מה עם האופציה החיובית?

Declare @S Varchar(Max),

        @Yes Varchar(Max);

Select  @S='abc d1,$ 23A-B`CD''à]á%â[ãä', --"מחרוזת טכנית לדוגמה שכוללת הרבה "לכלוך

        @Yes='0-9a-zA-Zא-ת';

While PatIndex('%[^'+@Yes+']%',@S)>0

        Set @S=Stuff(@S,PatIndex('%[^'+@Yes+']%',@S),1,'');

Select  @S;

הלולאה מזהה בעזרת PatIndex תווים שאינם בתחומים המוגדרים על ידי @Yes, ומחליפה אותם במחרוזת ריקה (בעזרת הפונקציה Stuff). הפונקציה PatIndex היא פונקציה חזקה מאוד, אבל יש לה כמה בעיות עם תווים שהם משמעותיים מבחינתה:

מקף (-) יש למקם בתחילת המחרוזת, אחרת הוא מציין תחום (מ.. עד ..).

גג (^) אין למקם בתחילת המחרוזת כי הוא מציין שלילה (כל מה שאינו..).

סוגרים מרובעים ([]) לא הצלחתי למצוא דרך להשתמש בסוגר הימני ([) כחלק ממחרוזת כמו בדוגמה הנ"ל. אם מישהו ימצא – אשמח מאוד.

כפי שציינתי- יש לשלב את הקודים הנ"ל בפונקציה כדי להפעיל אותה על טבלה. למשל-

Create Function dbo.F_Nikayon(@S Varchar(Max), 

                            @Yes Varchar(Max)) 

Returns Varchar(Max) As 

Begin 

While PatIndex('%[^'+@Yes+']%',@S)>0 

      Set @S=Stuff(@S,PatIndex('%[^'+@Yes+']%',@S),1,''); 

Return (@S); 

End 

Go 

Select  name, 

        dbo.F_Nikayon(name,'0-9a-zA-Zא-ת') 

From    sys.objects; 

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

פונקציות החלון הוצגו לראשונה בגרסת 2005 והן אפשרו להציג סיכומי Group By בשליפה רגילה, דבר שבגרסאות קודמות היה יכול להתבצע רק על ידי Join בין השליפה הרגילה לשליפת ה-Group By, ובנוסף אפשרו למספר את השורות הנשלפות בדרכים שונות. עם האוכל בא התיאבון, והצורך בשיפורים ותוספות לפונקציות החלון – בעיקר לאור מה שיש למשתמשי אוראקל בני המזל – עודד את מיקרוסופט להשלים את החסר בגרסת Denali, והתוספות – חלקן משמחות מאוד, וחלקן – נו טוב שיהיה..

הבשורה המשמחת ביותר מבחינתי היא הפונקציות Lag & Lead המאפשרות פניה לרשומות קודמות ועוקבות, כשלהן מתווספות First_Value & Last_ Value שמאפשרות להציג את הרשומה הראשונה והאחרונה בתחום.
פונקציות אלו יכולות להיות שימושיות כשמציגים שערי מט"ח תוך ציון שיעור השינוי היומי (המחושב על סמך הערך הנוכחי והערך הקודם) והשער בתחילת התקופה, למשל:

Use AdventureWorks;

Go

Select  ToCurrencyCode,

        AverageRate,

        CurrencyRateDate,

        Lag(AverageRate,1,Null) Over(Partition By ToCurrencyCode Order By CurrencyRateDate) PreviousRate,

        First_Value(AverageRate) Over(Partition By ToCurrencyCode,Year(CurrencyRateDate),Month(CurrencyRateDate) Order By CurrencyRateDate) FirstValue,

        Last_Value(AverageRate) Over(Partition By ToCurrencyCode,Year(CurrencyRateDate),Month(CurrencyRateDate) Order By CurrencyRateDate) LastValue

From    Sales.CurrencyRate

Order By ToCurrencyCode,

        CurrencyRateDate;

Go

כפי שאפשר לראות בטבלה המציגה את היסטוריית שערי החליפין מול הדולר- פונקציית Lag מקבלת שלושה פרמטרים:
1. שם העמודה שאת הערך הקודם שלה יש להציג.
2. כמה שלבים קודם יש לבדוק (בדרך כלל 1 אבל אפשר גם אחרת).
3. ערך חלופי אם אין ערך קודם (לרוב נבחר ב-Null או ב-0 אם ברצוננו לערוך חישובים).
בנוסף יש לציין מה התחום (במקרה זה- שם המטבע ToCurrencyCode), ולפי מה המיון שעל פיו נקבע מי הקודם (במקרה זה התאריך CurrencyRateDate).

פונקציית First_Value מקבלת את שם העמודה שאת הערך הראשון שלה יש להציג, וכן את אופן חלוקת הסט (Partition By) והמיון שלו (Order By) כדי להגדיר מי הראשון. בדוגמה זו שער החליפין הראשון של הדולר מול האירו הוא 1.1074 באפריל 2007, ו-1.0983 במאי 2007 (כי זה השער ב-01/05/2007).
פונקציות Lead (הערך הבא) ו-Last_Value פועלות באופן דומה לשתי הנ"ל, אם כי מסיבה לא ברורה (Feature? Bug?) פונקציית Last_Value מציגה את הערך השוטף (שהוא האחרון עד כה..) ולא את האחרון בתחום.

לפונקציות Lag ו-Lead יש שימושים מעניינים רבים ועוד אעסוק בכך בעתיד.

ארבע פונקציות חלון סטטיסטיות חדשות פחות מוצלחות לטעמי הן Percent_Rank, Cume_Dist, Percentile_Cont, Percentile_Disc.
ניצור טבלת מכירות להדגמה:

Use Tempdb;

Go

Create Table T_Mehirot(Moher Varchar(10),

                    Taarih DateTime,

                    Camut Int);

Go

Insert

Into T_Mehirot

Values  ('Hadas','20110801',15),

        ('Hadas','20110805',10),

        ('Yoram','20110802',10),

        ('Yoram','20110808',20),

        ('Yoram','20110812',10),

        ('Yoram','20110815',5),

        ('Yoram','20110819',15);

Go

Select  Percent_Rank() Over(Partition By Moher Order By Taarih) [Percent_Rank],

        Cume_Dist() Over(Partition By Moher Order By Taarih) [Cume_Dist],

        Percentile_Cont(0.5) Within Group(Order By Camut) Over (Partition By Moher) [Percentile_Cont],

        Percentile_Disc(0.5) Within Group(Order By Camut) Over (Partition By Moher) [Percentile_Disc],

        *

From    T_Mehirot

Order By Moher,

        Taarih;

Go

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

סיום הנחת היסודות,

סיום בניית השלד,

סיום יציקת הגג,

סיום גימור הבניין.

אין לי שמץ של מושג איך בונים בית אך דומני שזה הסדר הנכון..
בנקודת ההתחלה סיימנו 0% מהפרויקט, עם סיום הנחת היסודות סיימנו 25%, וכך הלאה.
בדרך כלל אנחנו לא מציגים את המידע שלנו בצורה כזו. אם נתייחס לְדוגמה לְחלוקה מקובלת של השנה לרבעונים- היא תיראה כך:

אנחנו נציין ארבעה רבעונים (באדום) ולא את חמשת התאריכים שתוחמים אותם (בכחול). באופן דומה- התאריך 18/08/2011 מתייחס בדרך כלל ליממה שלמה ולא רק לנקודת הזמן בחצות הליל בו היום התחיל. מכאן שהחלוקה לנקודות זמן המתחילה מאפס אינה שימושית כל כך.
בנוסף- הפונקציה Percent_Rank אינה מאפשרת לציין משקל, וכך בעל כורחנו אנחנו מניחים שלכל השלבים בבניין הבית משקל יחסי זהה של 25%.

Cume_Dist- פונקציה זו יותר שימושית מהקודמת ומתאימה למניית תקופות, ולכן אינה מתחילה מאפס אלא מ-50% (במקרה של הדס לה שתי מכירות) או מ-25% (במקרה של יורם לו חמש מכירות). יחד עם זאת- גם כאן חסרה היכולת לציין משקלים.

Percentile_Cont- פונקציה זו מאפשרת להציג אחוזונים. השימוש הנפוץ באחוזונים הוא לחישוב חציון (Median מתקבל על ידי הפרמטר 0.5) שהוא הערך האמצעי מבין רשימת ערכים, ומהווה תחליף מקובל לממוצע כשרוצים לנטרל ערכים קיצוניים. למשל- אם במשק יש חמישה אנשים שמשכורותיהם 100,200,300,400,1000; המשכורת הממוצעת היא 400, וכך יוצא שאדם אחד מרוויח מעל הממוצע ושלושה מתחתיו (בישראל המשכורת הממוצעת היא כ-8000 ₪ שהרוב מרוויחים פחות ממנה בעוד שהמשכורת החציונית היא כ-5000 ₪). לחילופין אפשר להתייחס למשכורת החציונית – במקרה זה 300 – ואז שני אנשים ירוויחו יותר ממנה ושניים פחות ממנה.
שימושים נוסף לאחוזונים הם חלוקה של המשק לעשירונים לפי גובה המשכורת, מעקב אחר התפתחות של ילדים בהשוואה להתפלגות בקרב האוכלוסיה (לילד שסובל מעודף משקל יאמר שהוא נמצא באחוזון ה-90 מבחינת המשקל).
בדוגמה הנ"ל- החציון של הדס הוא 12.5 שזה הממוצע בין שני הערכים האמצעיים שלה, והחציון של יורם הוא 10 שזה הערך השלישי לפי הסדר מבין החמישה שלו.
חסרון בולט של הפונקציה הזו כמו בקודמות הוא העדר תמיכה לשימוש במשקלים, למשל אם שתי השורות של הדס לא היו מייצגות מכירה אחת של 10 ואחת של 15, אלא מאה מכירות של 10 ושמונים של 15, ואז יש למצוא מה המכירה התשעים לפי הסדר.

Percentile_Disc- מחשבת אחוזונים כמו הקודמת, אך אינה מבצעת אינטרפולציה (כפי התבצעה במקרה של הדס בין 10 ל-15) ומחזירה ערך קונקרטי (כלומר- 10 שהוא הערך הסמוך לאמצע מלמטה ולא 12.5 שאינו קיים בטבלה). מי שרוצה לקבל את הערך הסמוך לאמצע מלמעלה- יכול למיין בסדר הפוך.
גם פונקציה זו אינה תומכת במשקלים, אך יש לה יתרון חשוב על הקודמת- היא מסוגלת למצוא חציונים של סוגי נתונים שאינם מספרים (תאריכים, מחרוזות וכו') ואשר לא נכון או לא ניתן לבצע להם אינטרפולציה (למשל- מה החציון לפי סדר א"ב מבין אבי, גלית, יוסי, תמר? הרי לא ניתן לחשב ממוצע בין גלית ויוסי ולקבל גלית וחצי..).

ולבסוף- תוספת משמעותית לפונקציות החלון הוותיקות (לא לפונקציות הסטטיסטיות החדשות!) היא היכולת להגביל אותן למסגרת חלקית (Frame) מהרשומות ב-Partition במקום לכולן כפי שהיה עד כה, וזה נעשה באמצעות הוספת הפרמטר Rows לאופרטור Over. נוכל למשל לחשב סכום מצטבר למוצר כך:

Select  *,

        Sum(Quantity) Over(Partition By ProductID

                    Order By TransactionDate, ReferenceOrderID

                    Rows Between Unbounded Preceding And Current Row) RunningAggregate

From    Production.TransactionHistory

Where   TransactionType='P'

Order By ProductID,

        TransactionDate,

        ReferenceOrderID;

הפניה של Rows יכולה להיות לכל הרשומות הקודמות (כבדוגמה הזו) או העוקבות, או למספר סופי של רשומות קודמות או עוקבות, ואפשרויות שונות מודגמות כאן:

סינטקס מקוצר לציבור החוסכים- במקום Rows Between Unbounded Preceding And Current Row ניתן להסתפק ב- Rows Unbounded Preceding, ובאופן דומה לגבי Preceding 1 וכו'; אם כי למרבה הפלא זה לא עובד ב-Following. Bug or Feature?

לצד האופרטור Rows קיים האופרטור החלופי Range שבניגוד ל-Rows מתייחס לכל הערכים הזהים לאלו שב-Frame. דוגמה:

Select  *,

        Count(Camut) Over(Partition By Moher

                    Order By Camut

                    Rows Between Unbounded Preceding And Current Row) [Rows],

        Count(Camut) Over(Partition By Moher

                    Order By Camut

                    Range Between Unbounded Preceding And Current Row) [Range]

From    T_Mehirot;

שימו לב לשורה 4 – האופרטור Rows מונה 2 כי זו השורה השניה של יורם, ואילו האופרטור Range מונה 3 גם כאן וגם בשורה הבאה מכיוון שבשתיהן הכמות זהה – 10 והוא סופר את כל העשיריות יחד.

כמו בפונקציות ה-Lead וה-Lag – הפרמטר Rows מוסיף המון אפשרויות מעניינות לפונקציות החלון הקיימות, אך על כך בפעם אחרת.

הערה לסיום- הפוסט הזה מתבסס על גרסת CTP3, ולא מן הנמנע שבגרסה שתשוחרר בעתיד יהיו שינויים.

גרסת 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 (לא מופיעה בדוגמה) - מקבלת שנה, חודש, יום.

הפוסט הזה נכתב בעקבות שאלה שנשאלה בפורום בסיסי נתונים בתפוז, והתמודדתי איתה בעזרת טריק יפה שלמדתי מאב"ג לפני שנים רבות באחד ממפגשי ISUG (כשעדיין התכבדנו בנוכחותו).
נתונה טבלת נוכחות של סטודנטים, ואנחנו מעוניינים לבדוק כמה ימים ברציפות הם נכחו או נעדרו בכל פעם;
או אולי- נתונה טבלת שערי מניות ואנחנו מעוניינים לחלק אותה לתקופות של עליות ותקופות של ירידות (תוך שאנחנו נעזרים באופציות ה-Lead וה-Lag שהתווספו לגרסת Denali שמאפשרות להשוות כל רשומה לקודמת);

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

Use tempdb;

Go

If Object_Id('T_Nohehut','U') Is Not Null Drop Table T_Nohehut;

Go

Create Table T_Nohehut(Shem Varchar(20),

                       Taarih DateTime,

                       Nohehut TinyInt);

Go

Insert Into T_Nohehut(Shem,Taarih,Nohehut)

Select 'Anat','20110701',1 Union All

Select 'Anat','20110702',1 Union All

Select 'Anat','20110703',0 Union All

Select 'Anat','20110705',0 Union All

Select 'Anat','20110707',1 Union All

Select 'Anat','20110711',0 Union All

Select 'Anat','20110713',0 Union All

Select 'Anat','20110717',0 Union All

Select 'Anat','20110719',1 Union All

Select 'Anat','20110723',0 Union All

Select 'Beni','20110701',1 Union All

Select 'Beni','20110702',1 Union All

Select 'Beni','20110703',1 Union All

Select 'Carmel','20110711',0;

Go

Select *

From   T_Nohehut

Order By Shem,

       Taarih;

Go

כעת נמספר את השורות בשני אופנים:
1. כל שם לפי סדר עולה של תאריכים.
2. כל צירוף של שם ונוכחות לפי סדר עולה של תאריכים.

והתוצאה יחד עם ההפרש בין שני המספורים:

Select  Row_Number() Over(Partition By Shem Order By Taarih) Mispar1,

        Row_Number() Over(Partition By Shem, Nohehut Order By Taarih) Mispar2,

        Row_Number() Over(Partition By Shem Order By Taarih)-Row_Number() Over(Partition By Shem, Nohehut Order By Taarih) Mispar,

        *

From    T_Nohehut

Order By Shem,

        Taarih;

Go

אפשר לשים לב שלכל תחום רציף של נוכחות יש אותו הפרש Mispar, וכשחושבים על זה רגע זה די הגיוני כי המספורים מתקדמים באותו קצב בתוך כל תחום, אך במעבר מתחום לתחום- המספור השני Mispar2 מתחיל מחדש.
מה שנעשה איפוא זה לבצע Group By לפי שם, נוכחות ומספר לשליפה הנ"ל,
ולהוסיף לה תאריכי התחלה וסיום בעזרת Min ו-Max על עמודת תאריך,
ומספר ימים בתחום בעזרת Count:

With T As

(Select Row_Number() Over(Partition By Shem Order By Taarih)-Row_Number() Over(Partition By Shem, Nohehut Order By Taarih) Mispar,

        *

From    T_Nohehut)

Select  Shem,

        Nohehut,

        Min(Taarih) MiTaarih,

        Max(Taarih) AdTaarih,

        Count(Mispar) Yamim

From    T

Group By Shem,

        Nohehut,

        Mispar

Order By Shem,

        Min(Taarih);

Go