גרי רשף

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

הפונקציה משלימה את Replace, כאשר במקום להחליף תת מחרוזת ספציפית אחת באחרת - לא משנה מה מיקומה, היא מחליפה תת מחרוזת לא ספציפית אחת באחרת - אך במקום מסויים.

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

Declare @SQL Varchar(Max);

Set @SQL='';

Select @SQL=@SQL+','+name From sys.columns Where Object_name(Object_Id) Like 'sysfiles1';

Print @SQL;

המחרוזת המתקבלת היא ,fileid,filename,name,status והיא כוללת פסיק מיותר בתחילתה.

לא ניתן להיפטר ממנו על ידי Replace כי אז כל הפסיקים יוחלפו, ופתרון בעזרת Substring (או Right / Left) השולף מתוך SQL@ את תת המחרוזת מהמקום השני ואילך הוא מעט מסורבל:

Declare @SQL Varchar(Max);

Set @SQL='';

Select @SQL=@SQL+','+name From sys.columns Where Object_name(Object_Id) Like 'sysfiles1';

Set @SQL=Right(@SQL,Len(@SQL)-1);

Print @SQL;

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

במקרה זה ניתן להשתמש ב-Stuff שמחליפה את התו הראשון (מיקום 1, אורך 1) במחרוזת ריקה:

Declare @SQL Varchar(Max);

Set @SQL='';

Select @SQL=@SQL+','+name From sys.columns Where Object_name(Object_Id) Like 'sysfiles1';

Set @SQL=Stuff(@SQL,1,1,'');

Print @SQL;

מה קורה אם הפסיק המיותר נמצא בסוף המחרוזת?

Declare @SQL Varchar(Max);

Set @SQL='';

Select @SQL=@SQL+name+',' From sys.columns Where Object_name(Object_Id) Like 'sysfiles1';

Print @SQL;

המחרוזת המתקבלת היא fileid,filename,name,status,.

גם כאן ניתן להשתמש ב-Left (במקום Right בדוגמה למעלה) שעלול להיות מסובך אם במקום SQL@יהיה לנו ביטוי מורכב,

או ב-Stuff כך:

Declare @SQL Varchar(Max);

Set @SQL='';

Select @SQL=@SQL+name+',' From sys.columns Where Object_name(Object_Id) Like 'sysfiles1';

Set @SQL=Reverse(Stuff(Reverse(@SQL),1,1,''));

Print @SQL;

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

לסיום- מבלי לבטל דבר מכל מה שכתבתי, במקרה מסויים זה ניתן עם קצת תושיה למנוע מראש את היווצרות הפסיק המיותר (בתנאי ש-SQL@ הוא Null):

Declare @SQL Varchar(Max);

Select @SQL=IsNull(@SQL+',','')+name From sys.columns Where Object_name(Object_Id) Like 'sysfiles1';

Print @SQL;

דוגמה שימושית: יש טריק פשוט שמאפשר לבצע פעולה הדומה ל-Group By על עמודה טקסטואלית כך שתשרשר את הערכים המופיעים. למשל:

Select    type,

        Stuff((Select ','+name AS [text()] 

                From Sysobjects T1

                where T1.type=T.type

                order by name

                For XML Path('')),1,1,'') As name

From    Sysobjects T

Group By T.type;

השליפה מבצעת group By על type כך שכל ערך יופיע פעם אחת,

ולכל type מתבצעת שליפת XML משנית של ערכי name שלו (יחד עם פסיק מוביל) שלמעשה משרשרת אותם,

ועל שליפת ה-XML הזו מופעלת פונקציית Stuff כדי להסיר את הפסיק הראשון המיותר כמו בדוגמה הראשונה.

לו לא היינו משתמשים ב-Stuff אלא ב-Right היינו צריכים לצטט פעמיים את שליפת ה-XML המשנית וזה היה כמובן יותר מסורבל:

Select    type,

        Right((Select ','+name AS [text()] 

                From Sysobjects T1

                where T1.type=T.type

                order by name

                For XML Path('')) ,

            Len((Select ','+name AS [text()] 

                From Sysobjects T1

                where T1.type=T.type

                order by name

                For XML Path('')))-1) As name

From    Sysobjects T

Group By T.type;

יש מקרים מורכבים גם יותר מזה..

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

נבדוק במה דברים אמורים- ניצור שתי טבלאות עם עמודת Int - בראשונה נגדיר אותה כ-Sparse ובשניה לא, לשתיהן נוסיף 100,000 שורות, ואולי אף נגזים ולעמודת ה-Sparse נכניס Nulls ולעמודה שאינה Sparse נכניס מספרים, ונבדוק כמה זה עולה לנו:

Use tempdb;

Go

If Object_Id('T1') Is Not Null Drop Table T1;

If Object_Id('T2') Is Not Null Drop Table T2;

Go

Create Table T1(I Int Sparse Null);

Create Table T2(I Int Null);

Go

With T As

(Select 1 I

Union All

Select I+1

From T

Where I<100000)

Insert Into T1

Select Null

From T

Option (MaxRecursion 0);

With T As

(Select 1 I

Union All

Select I+1

From T

Where I<100000)

Insert Into T2

Select I

From T

Option (MaxRecursion 0);

Go

Exec sp_SpaceUsed T1;

Exec sp_SpaceUsed T2;

Go

שטח האחסון של טבלה T1 בה עמודת ה-Sparse הריקה קטן מזה של טבלה T2 בה העמודה מלאה בכ-17%.

זה הכל? כן- זה הכל, ומכאן המצב רק יחמיר מכיוון שאם נאכלס את עמודת ה-Sparse בערכים באופן חלקי, הפער הקטן מלכתחילה ילך ויצטמצם, ובסביבות 20% הן יתפסו שטח אחסון דומה (כלומר- אם בטבלה T1 יהיו 20% מהשורות מלאות ו-80% ריקות - שטח האחסון שלה יהיה כשל T2 100% מלאה).

כדאי להבהיר שטבלה T2 תתפוס אותו שטח כשהיא ריקה או מלאה מכיוון שבכל מקרה המקום המוקצה למספר נשמר גם אם בפועל יש שם Null.

נבצע בדיקה אחרת- הפעם שתי טבלאות עם עמודת Varchar(Max):

Use tempdb;

Go

If Object_Id('T1') Is Not Null Drop Table T1;

If Object_Id('T2') Is Not Null Drop Table T2;

Go

Create Table T1(S Varchar(Max) Sparse Null);

Create Table T2(S Varchar(Max) Null);

Go

With T As

(Select 1 I

Union All

Select I+1

From T

Where I<100000)

Insert Into T1

Select Null

From T

Option (MaxRecursion 0);

With T As

(Select 1 I

Union All

Select I+1

From T

Where I<100000)

Insert Into T2

Select Null

From T

Option (MaxRecursion 0);

Go

Exec sp_SpaceUsed T1;

Exec sp_SpaceUsed T2;

Go

לשתי הטבלאות אותו שטח אחסון!

זה באמת מוזר מכיוון שתא Varchar תופס שטח מינימלי כשהוא ריק, וציפיתי שבטבלת ה-Sparse השטח הזה יחסך, והנה לא; ומכאן החישובים הם לטובת טבלה T2 "הרגילה" אם נתחיל להזין לתוכן נתונים.

לא בדקתי מה קורה בעמודת Char מכיוון שזה לא לעניין: אם ידוע מלכתחילה שיהיו הרבה Nulls - אין טעם להשתמש בעמודת Char שמקצה את מלוא המקום לכל תא ללא קשר לניצול שלו.

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

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

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

ב-SQL Sever הטרנזקציות משפיעות גם על פעולות DDL (שינויים במבני טבלאות): יצירת אובייקטים, ביטול אובייקטים ושינוי אובייקטים. כך למשל פעולת -Truncate Table שהיא בעצם יצירה מחדש של הטבלה - המתבצעת בתוך טרנזקציה, תתבטל במקרה של Rollback.

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

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

לקח לי זמן להבין שהפרוצדורה מתחילה ב-Begin Tran, במקרה של שגיאה מופעל מנגנון ה-Catch, ומתבצע Rollback;

וכתוצאה מכך הפקודה הבאה -

If Object_Id('tempdb..##MyTbl') Is Not Null Drop Table ##MyTbl;

Exec('Select    .,

            .,

            .

    Into    ##MyTbl

    From    .')

התבטלה והטבלה הישנה הוחזרה למערכת..

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

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

פונקציות חלון מאפשרות לנו להוסיף לשליפה רגילה עמודה עם סיכומים מבלי לבצע Group By - פעולה שמקבצת נתונים ואינה מחזירה את ה-Set המקורי.

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

בפוסט Calculate Percentage in SQL Server מציג שיטה לחישוב אחוזים בשאילתת Group By: ביצענו סיכום לפי ציונים, וכדי לדעת את חלקו של כל ציון בסה"כ יש לחלק את מספר הציונים בכל קבוצה בסה"כ הציונים; כלומר- לבצע פעם Count של Group By לפי קבוצה ופעם Count כללי כפונקציית חלון.

המחבר בחר לא להסתבך עם זה, או אולי לא שקל זאת או לא הצליח, ובכל מקרה- הנתונים לבעייה:

CREATE TABLE #ClassRoom(ID INT IDENTITY(1,1), Grade char(2) NULL);

GO

INSERT INTO #ClassRoom Values ('A');

INSERT INTO #ClassRoom Values ('B');

INSERT INTO #ClassRoom Values ('B+');

INSERT INTO #ClassRoom Values ('B');

INSERT INTO #ClassRoom Values ('A');

INSERT INTO #ClassRoom Values ('A+');

INSERT INTO #ClassRoom Values ('B');

INSERT INTO #ClassRoom Values ('B');

INSERT INTO #ClassRoom Values ('A+');

INSERT INTO #ClassRoom Values ('C');

GO

והפתרון בו המחבר בחר (בכמה שינויים קוסמטיים להפשטה):

Select Grade,

       100*Count(*)/tot 'Percent'

From   #ClassRoom,

       (Select Count(*) as tot 

       From #ClassRoom) x

Group By Grade,

       tot;

ההצעה החלופית שלי המשלבת שימוש בפונקציות חלון:

Select Grade,

       (100*COUNT(*))/Sum(COUNT(*)) Over() 'Percent'

From   #ClassRoom

Group By Grade;

כלומר- מופעלת פונקציית החלון Sum על ה-Count של ה-Group By.

מבחינת ה-Execution Plan צפוי רווח מסויים בזכות העובדה שביצעתי Scan אחד ולא שניים:

יצרנו טבלת עובדים. באיזו עמודה נבחר בתור ה-Primary Key שלה, הנתון שיצביע באופן חד משמעי על העובד בטבלאות המקושרות כ-Foreign Key (ביחס 1:N) או בחיפוש בטבלה?

אפשרות שמן הסתם תיפסל מראש היא להשתמש בשם ובשם הפרטי, מכיוון שהם אינם יחודיים בהכרח.

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

נכון- הבעיות קצת נדירות או מוזרות ותמיד ניתן לאלתר פתרון שעוקף אותן, אבל הן אינן מופרכות.

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

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

זו בקצרה אחת הסיבות להעדפת השימוש ב-SK מלאכותי על פני מפתח טבעי, כשהכוונה בדרך כלל למספרים שנוצרים אוטומטית בעמודת Identity בטבלה הנ"ל: מנגנון יצירת מספרי העובדים הפנימיים למערכת המידע שלנו יהיה עצמאי ובלתי תלוי בשגיונותיהם של משרד הפנים המנפיק תעודות זהות או של אגף משאבי האנוש בארגון. כמובן שנקצה בטבלה עמודות מתאימות למספר תעודות הזהות ולמספר העובד בארגון, אבל ה-Primary Key יהיה בשליטתנו המלאה.

דוגמה נוספת – יש לנו טבלת תאריכים מ-01/01/2000 ועד 31/12/2999 כמקובל במערכות OLAP. מה יהיה כאן ה-Primary Key?

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

האם גם כאן עלולות לצוץ בעיות? כנראה שכן: מה יקרה אם נרצה משיקולים שונים להוסיף לטבלה תאריכים כמו "ינואר 2010" (זה לא בדיוק תאריך אבל יש פעילויות שנמדדות ברזולוציה של חודשים ולא של ימים)? מה יקרה אם נרצה לצרף חלקי יממה? מה יקרה אם מיקרוסופט תשנה את ניהול התאריכים במערכות שלה וכעת הם לא יהיו מספרים שלמים?

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

גם כאן הפתרון יכול להיות עמודת Identity בטבלת התאריכים, ואז לא תהיה בעייה להוסיף לטבלה שורה עבור ינואר 2010 או עבור 16/12/2010 13:38:15, ובעמודות ה-Foreign Key בטבלת ה-Fact לא יופיעו תאריכים אלא מספרים הפונים לטבלת התאריכים.

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

דוגמה אחרונה- יש לנו טבלה בה כתוב כמה פריטים מהזמנת יצור יצר עובד במשמרת, כלומר- המפתח הוא תאריך & משמרת & עובד & הזמנה & פריט, ובנוסף יש עמודת כמות. אנחנו רוצים לאגור מידע לגבי תלונות או הערות המתייחסים לכל אחת מהשורות בטבלה, ולכן אנו יוצרים טבלה נוספת (כי לכל שורה בראשונה יכולות להיות כמה בשניה) ומקשרים אותן ביחס של 1:N, וזה מחייב אותנו ליצור בטבלה השניה עמודות תאריך & משמרת & עובד & הזמנה & פריט שישמשו כ-Foreign Key מהראשונה, ולזה להוסיף עמודות נוספות עבור הטבלה עצמה (עד כה זה רק נועד לקשר אותה לראשונה) וליצור גם לה מפתח שיכלול את כל עמודות ה-Foreign Key ועוד עמודה אחת לפחות. קצת מסורבל: כבר המפתח של הטבלה הראשונה מסורבל מאוד, ועל זה של השניה- מוטב לא לדבר..

גם כאן פתרון נוח יכול להיות SK בצורת עמודת Identity שישמש כמפתח, וכך הטבלה השניה תהיה פשוטה ונוחה יותר להבנה, שלא לדבר על כך שגם הטבלה הראשונה יכולה הייתה להיות פשוטה לו נקטנו בשיטה זו מלכתחילה.

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

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

בנוסף נאבד את המידע הטמון במפתחות הטבעיים, והדוגמה הכי טובה היא שדה התאריך שעד כה יכולנו לחלץ ממנו בקלות את השנה והחודש או להוסיף ולגרוע ממנו ימים, וכעת עלינו לגשת לטבלת התאריכים לשם כך.

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

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

בפורום בסיסי נתונים בתפוז תהינו האם האופרטורים In & Exists יעילים, והאם נעשה בהם שימוש "חכם".

כלומר- כשאנחנו מחפשים שורות בעלות Foreign Key בצד ה-1 שיש להן שורות תלויות בצד ה-N (הרבים) אפשר לבדוק בעזרת Join בין שתי הטבלאות, אלא שיש כאן בזבוז- המערכת תחזיר את כל ההתאמות של כל שורה (למשל עבור מוצר X את כל המכירות שלו..) כשכל מה שאנחנו רוצים זה לקבל תשובה של כן/לא..

הדרך הנכונה לעשות זאת היא בעזרת Exists או In, כשהדעת נותנת שלאחר שנמצאה התאמה אחת- המערכת תפסיק לחפש ולא תמשיך לבדוק לחינם את כל הטבלה שבצד הרבים; אך האם כך הם פני הדברים? כדאי לבדוק!

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

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

Use tempdb;

Go

If Object_Id('T_Misparim') Is Not Null Drop Table T_Misparim;

Go

With T As

(Select 1 Mispar

Union All

Select Mispar+1

From T

Where Mispar<1000000)

Select *

Into T_Misparim

From T

option (MaxRecursion 0);

Go

Create Unique Clustered Index Idx_T_Misparim On T_Misparim(Mispar);

Go

כעת נוסיף עמודה נטולת אינדקס לטבלה, ונעדכן אותה כך שיופיעו בה המספרים כמו בעמודת המספרים הקודמת, חוץ מ-999999 שיוחלף בערך -1 (הסבר בהמשך):

Alter Table T_Misparim Add Bdika Int;

Go

Update T_Misparim

Set Bdika=Case When Mispar=999999 Then -1 Else Mispar End

Go

וכעת נריץ את הקוד הבא (ליתר בטחון אני מריץ פעמיים ומציג את נתוני הריצה השניה לאחר שהנתונים נטענו ל-Cash):

Set Statistics Time On

Set Statistics IO On

Print 

'1-----------------------------------------------------------------------------------------------------------------------------------------------------------'

Select 1 N

Where 1 In (Select Sqrt(Bdika) From T_Misparim)

Print 

'500---------------------------------------------------------------------------------------------------------------------------------------------------------'

Select 500 N

Where 500 In (Select Sqrt(Bdika) From T_Misparim)

Print 

'1000--------------------------------------------------------------------------------------------------------------------------------------------------------'

Select 1000 N

Where 1000 In (Select Sqrt(Bdika) From T_Misparim)

Print 

'------------------------------------------------------------------------------------------------------------------------------------------------------------'

Set Statistics IO Off

Set Statistics Time Off

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

בשליפה השניה אמור לקחת לו הרבה יותר זמן למצוא את המספר 250,000 שהשורש שלו הוא 500.

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

התוצאות שקיבלתי:

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

1-----------------------------------------------------------------------------------------------------------------------------------------------------------

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

(1 row(s) affected)

Table 'T_Misparim'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

500---------------------------------------------------------------------------------------------------------------------------------------------------------

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

(1 row(s) affected)

Table 'T_Misparim'. Scan count 1, logical reads 812, physical reads 0, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 78 ms, elapsed time = 76 ms.

1000--------------------------------------------------------------------------------------------------------------------------------------------------------

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

Msg 3623, Level 16, State 1, Line 13

A domain error occurred.

אפשר לראות שהחיפוש הראשון היה מהיר משמעותית מהשני וצרך פחות משאבים, ומכאן שהוא הסתיים הרבה יותר מהר;

ורק החיפוש השלישי נכשל לאחר שהגיע למספר השלילי (שני הראשונים עצרו לפני כן).

אם נבצע אותה בדיקה על שליפות העושות שימוש באופרטור Exists נקבל תוצאות דומות:

Set Statistics Time On

Set Statistics IO On

Print 

'1-----------------------------------------------------------------------------------------------------------------------------------------------------------'

Select 1 N

Where Exists (Select * From T_Misparim Where Sqrt(Bdika)=1)

Print 

'500---------------------------------------------------------------------------------------------------------------------------------------------------------'

Select 500 N

Where Exists (Select * From T_Misparim Where Sqrt(Bdika)=500)

Print 

'1000--------------------------------------------------------------------------------------------------------------------------------------------------------'

Select 1000 N

Where Exists (Select * From T_Misparim Where Sqrt(Bdika)=1000)

Print 

'------------------------------------------------------------------------------------------------------------------------------------------------------------'

Set Statistics IO Off

Set Statistics Time Off

והתוצאות די דומות:

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

1-----------------------------------------------------------------------------------------------------------------------------------------------------------

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

(1 row(s) affected)

Table 'T_Misparim'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

500---------------------------------------------------------------------------------------------------------------------------------------------------------

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

(1 row(s) affected)

Table 'T_Misparim'. Scan count 1, logical reads 812, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 63 ms, elapsed time = 70 ms.

1000--------------------------------------------------------------------------------------------------------------------------------------------------------

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

Msg 3623, Level 16, State 1, Line 14

A domain error occurred.

יש כמה הבדלים לא משמעותיים בין ריצת ה-In וריצת ה-Exists, וה-Execution Plans שלהם זהים:

לסיום כדאי לוודא שפקודות ה-Set Statistics נוטרלו (השגיאה היזומה מנעה מהריצה להגיע אליהן):

Set Statistics IO Off

Set Statistics Time Off

לכל קבוצה בגודל n יש 2ⁿ תת קבוצות, למשל- לקבוצה {A,B,C} יש תת קבוצות {A},{B},{C},{A,B},{A,C},{B,C}; נוסיף להן את הקבוצה המקורית ואת הקבוצה הריקה ונקבל 8=2³ תת קבוצות.
אם נבחר קבוצה בת 4 איברים- אפשר באותה שיטה למצוא את 16=2⁴ תת הקבוצות שלה, או לעשות חשבון פשוט- לכל תת קבוצה של הקבוצה בת 3 האיברים ניתן להוסיף את האיבר הרביעי וניתן לא להוסיף; כלומר- מכל תת קבוצה של n=3 נקבל 2 תת קבוצות עבור n=4 ובסה"כ 16.

כך מוכיחים באופן כללי את הנוסחה באינדוקציה, וזה גם ישמש אותנו ליצור את תת הקבוצות בהמשך.
בנוסף כדאי לשים לב שאם נכתוב את כל המספרים מ-0 ועד ל-8 (לא כולל) באופן בינארי (בהתייחס לדוגמה הראשונה)- נקבל את כל תת הקבוצות, וזו דרך נוחה למספר או ליצור אותם. נניח - 5 נכתב בבינארית 101 וזה מייצג את תת הקבוצה עם האיבר הראשון והשלישי וללא השני.

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

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

2. יש לנו טבלה ואיננו יודעים מה המפתח שלה. משימה שבוודאי קרתה למי שהתבקש לטפל במערכות לא מתועדות שמי שהקים אותם כבר מזמן לא עובד בארגון, וגם מי שהחליף אותו ועשה איתו חפיפה לא נמצא; ובנוסף לכל- לא הוגדר מפתח באופן פורמלי למרות שמעשית אנחנו חושדים שיש כזה.. בדרך כלל נחפש עמודה בשם ID או TeudatZehut, אולי נצרף אליה עמודת תאריך שנמצאת בהתחלה וננסה לבדוק אם זה יוצר מפתח יחודי; אבל אם רוצים להיות שיטתיים- צריך לבדוק את כל תתי הקבוצות של עמודות הטבלה- החל מעמודות בודדות, המשך דרך צמדים של עמודות, עבור לשלישיות, וכלה בכל העמודות יחד; ולכל תת קבוצה כזו לבצע בדיקה האם היא יחודית (Unique), כלומר- האם Countו-Count Distinct מחזירים אותה תוצאה.

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

דיבורים דיבורים, אבל בלי קצת קוד זה לא זה- הרי מדובר בבלוג טכני בענייני SQL ולא במוסף ספרותי ברומו של עולם!

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

Declare        @N Int;

Set        @N=3; 

With T As 

(Select    1 N, 

        Cast(Case When T2.N=1 Then '1' Else '' End As Varchar(Max))T 

From    (Select    1 N 

        Union All 

        Select    2 N) T2 

Union All 

Select    T.N+1, 

        T.T+Case When T2.N=1 Then Case When T.T='' Then '' Else ',' End+Cast(T.N+1 As Varchar) Else '' End T 

From    T 

Cross Join (Select    1 N 

            Union All 

            Select    2 N) T2 

Where    T.N<@N) 

Select    T 

From    T 

Where    T.N=@N 

Order By T;

Go

הדרך השניה היא ליצור את כל המספרים מ-1 ועד 2ⁿ, ועבור כל אחד למצוא את החזקות של 2 שיוצרות אותו בעזרת האופרטור & (להלן- Bitwise And):

Declare    @N Int;

Set        @N=3;

With T As

(Select    0 N

Union All

Select    N+1

From    T

Where    N<Power(2,@N)-1)

Select    T1.N,

        T2.N+1

From    T T1

Left Join T T2

        On T2.N<=@N

        And T1.N & Power(2,T2.N)>0

Order By 1,2

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

ניתן לקבל את שם היום ושם החודש (פורמט מלא) של תאריך כלשהו כך:

Select DatePart(Weekday,GetDate()),

       DateName(Weekday,GetDate()),

       DatePart(Month,GetDate()),

       DateName(Month,GetDate()),

       Cast(GetDate() As Varchar);

כללתי בשליפה גם את מספר היום בשבוע, את מספר החודש ואת התאריך כטקסט הכולל את שם החודש בפורמט של שלוש אותיות.

אני קיבלתי את הערכים הבאים:

5, Thursday, 12, December, Dec 2 2010 3:15PM

השמות נלקחים מטבלת המערכת:

Select *

From   sys.syslanguages;

כפי שניתן לראות- לכל שפה מוגדרים השמות השונים בעמודות Months, ShortMonths, Days;

והמערכת מציגה את השם המתאים לשפה של ה-Session (ניתן לברר על ידי LANGUAGE@@).

מהגרסה הבאה נוכל לעצב את ה-Result Sets המוחזרים על ידי הפרוצדורות בעזרת פקודה מתאימה!

(סימן הקריאה נכתב בציניות כי ההתרשמות שלי היא פיהוק אחד גדול)

ניקח פרוצדורה פשוטה לדוגמה:

Exec sp_databases

With Result Sets(([Database_name] Varchar(Max) Not Null,

                [Database_Size] Int Not Null,

                [Remarks] Varchar(Max) Null));

זה הסינטקס והפלט עם הפרדיקאט או בלעדיו זהה.

ננסה לבחון את יכולותיו ומגבלותיו:

1. מה יקרה אם נבחר לעמודה הראשונה סוג טקסט קטן מדי?

Exec sp_databases

With Result Sets(([Database_name] Varchar(10) Not Null,

                [Database_Size] Int Not Null,

                [Remarks] Varchar(Max) Null));

התחלה יפה- הפרדיקאט מקטין את הטקסט לגדול הנדרש.

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

2. מה יקרה אם נבחר לעמודה השניה - עמודת ה-Integer - סוג נתון קטן מדי?

Exec sp_databases

With Result Sets(([Database_name] Varchar(Max) Not Null,

                [Database_Size] SmallInt Not Null,

                [Remarks] Varchar(Max) Null));

כצפוי קיבלנו שגיאה, אבל אם ננסה לשנות באופן הגיוני (למשל ל-Float) לא צפויה בעייה.

3. מה יקרה אם נגדיר את העמודה השלישית כ-Not Null למרות שיש בה ערכי Null?

Exec sp_databases

With Result Sets(([Database_name] Varchar(Max) Not Null,

                [Database_Size] Int Not Null,

                [Remarks] Varchar(Max) Not Null));

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

4. מה יקרה אם נגדיר את העמודה הראשונה כ-PK (שמות הדטבייסים הם יחודיים)?

Exec sp_databases

With Result Sets(([Database_name] Varchar(Max) Primary Key,

                [Database_Size] Int Not Null,

                [Remarks] Varchar(Max) Null));

שגיאה: הפרדיקאט אינו אמור לבדוק יחודיות.

5. מה יקרה עם נשנה את ה-Collation של העמודה הראשונה?

Exec sp_databases

With Result Sets(([Database_name] Varchar(Max) Collate Hebrew_CS_AS,

                [Database_Size] Int Not Null,

                [Remarks] Varchar(Max) Null));

יפה מאוד- המשימה הצליחה, אם כי שינוי Collation מתבצע באופן אוטומטי גם אם מפנים את הפלט לטבלה.

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

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

1. הצגה של חלק מהעמודות:

Exec sp_databases

With Result Sets((--[Database_name] Varchar(Max) Collate database_default,

                [Database_Size] Int Not Null,

                [Remarks] Varchar(Max) Null));

כאמור- שגיאה..

1. מה יקרה אם נשנה את שם העמודה?

Exec sp_databases

With Result Sets(([DB_name] Varchar(Max) Collate database_default,

                [Database_Size] Int Not Null,

                [Remarks] Varchar(Max) Null));

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

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

Exec sp_SpaceUsed

With Result Sets((Database_name Varchar(Max),

                database_size Varchar(Max),

                unallocated_space Varchar(Max)),

                (reserved Varchar(Max),

                data Varchar(Max),

                index_size Varchar(Max),

                unused Varchar(Max)));

לסיכום- אם ברשימת החידושים ב-TSQL הנ"ל מופיע במקום בולט - כנראה שאין הרבה חידושים..