Transact-SQL

Transact-SQL או TSQL היא שפת פיתוח לבסיסי נתונים רלציוניים של מיקרוסופט ו-Sybase.
במילים אחרות - זו שפת תכנות המשמשת לעבודה מול SQL Server, הכלי לניהול בסיסי נתונים של מיקרוסופט (לכל כלי לניהול בסיסי נתונים כדוגמת אורקל או טרדטה יש גרסת SQL משלו). השפה היא ייעודית לטיפול בנתונים ובבסיסי נתונים, ולכן אינה משמשת - למשל - לעיצוב ולטיפול בממשקי משתמש; המנוע ^[1] שלה יעיל יחסית בטיפול בביצוע פקודות DML^[2] אך לא בחישובים מתמטיים מורכבים ובעיבוד מחרוזות^[3] וסובלת ממגוון דל יחסית של פונקציות שתומכות בכך^[4].

SQL, ראשי תיבות של Structured Query Language (שפת שאילתות מובנית), היא שפת מחשב דקלרטיבית^[5] תקנית^[6], כאשר TSQL מרחיבה במקרים רבים את התקן וכוללת יכולות שאינן כלולות בו. כל היכולות וההרחבות האלו הופכות את TSQL ל-Turing Complete ^[7].

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

הקדמה

סביבות פיתוח והתממשקות

סביבות הפיתוח המקובלות ל-TSQL הן:

SSMS^[8]– סביבת הפיתוח הייעודית ל-SQL Server של מיקרוסופט.
ויז'ואל סטודיו – סביבת הפיתוח הכללית של מיקרוסופט.

ניתן בנוסף להשתמש בכלי פיתוח של צד שלישי.

התממשקות לשם הרצת פקודות יכולה להיעשות באחת הדרכים הבאות:

דרך סביבות הפיתוח הנ"ל.
דרך שורת פקודה (Command Line).
דרך תוכנות שמתחברות (Connection) ל-SQL Server בטכנולוגיות כמו ODBC, OLEDB וכו', ושמשמשות כממשק משתמש המתממשק לשכבת הנתונים (Data Layer). למשל- קוד ASP הכולל פקודות TSQL לשליפת נתונים, אותם הוא מעצב ומציג כקובץ html בדפדפן.

היתרון של כלי מיקרוסופט הוא בדרך כלל בהתאמתם לפיתוח ב-TSQL מול SQL Server – שניהם טכנולוגיות של מיקרוסופט – כולל אופציות ויכולות חדשות שמתווספות (למשל – עבודה מול הענן^[9]), אשפים ייעודיים, יכולות השלמה אוטומטיות (IntelliSense) של שמות אובייקטים ופקודות המתאימים ל-TSQL, וכו'; אם כי בהחלט ייתכנו נסיבות בהן תהיה עדיפות לכלים אחרים שמאפשרים פיתוח גם מול מערכות שאינן של מיקרוסופט.

סקריפטים ותכנות פרוצדורלי

פקודות TSQL ניתן לאגד לסקריפטים הכוללים פקודות רבות ושיכולות לקבל פרמטרים בחלק מהמקרים ^[10] , כגון:

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

TSQL מאפשר תכנות פרוצדורלי, למשל:

פירוק של משימה למספר פרוצדורות משנה, והפעלתן על פי הסדר הלוגי מתוך הפרוצדורה הראשית.
תנאי If .. Else המאפשרים בקרת ריצה.
שימוש ב-Try & Catch לטיפול בשגיאות (שיגרת שגיאה).
שימוש בלולאות.

בנוסף לכל הנ"ל כדאי לציין יכולות כמו:

שימוש בפרמטרים ובמשתנים מקומיים (Local Variables) שמתאימים לכל סוגי הנתונים הנתמכים על ידי SQL Server, כולל משתני טבלה^[11]. אין תמיכה במשתנים גלובליים, וניתן לשנות את ערכי הפרמטרים כמו המשתנים.
שימוש ב-GoTo בסקריפטים.
הרצה אסינכרונית של סקריפטים דרך ג'ובים או Service Broker.
הרצת שורות פקודה מתוך סקריפטים (בנוסף ליכולת להריץ סקריפטים בעזרת שורת פקודה).

טרנזקציות

מרכיב בסיסי במערכת בסיסי נתונים ובשפת SQL הוא שימוש בטרנזקציות כדי להבטיח שסדרה של פעולות מתבצעת במלואה באופן תקין (מתבצע Commit) או אינה מתבצעת כלל ^[12]. ב-TSQL ניתן להגדיר סדרה של פעולות כטרנזציה, כאשר כל פעולה בפני עצמה היא טרנזקציה במשתמע (Implicitly), וכך אם עוצרים פעולת עדכון באמצע – אף נתון אינו מתעדכן והטרנזקציה מתבטלת (Roll Back).

רקורסיה

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

פקודות DML

המונח DML ^[13] מתייחס ללב ליבה של שפת SQL לגרסאותיה השונות והיא היכולת לשלוף ולשנות נתונים בעזרת פקודות. הפקודות היסודיות הן:

Select – שליפת נתונים.
Insert – הכנסת נתונים.
Update – שינוי נתונים קיימים (עדכון).
Delete – מחיקת נתונים.

שליפת נתונים

במקרה של Select ניתן לצרף פסוקיות שמעצבות ומגדירות את סט הנתונים הנשלף:

מאילו טבלאות לשלוף, בעזרת האופרטורים From, Join, Apply.
כללי ההתאמה בין הנתונים מהמקורות השונים (למשל- לשלוף רק את השורות שיש ביניהן התאמה או לחלופין את אלו שאין להן התאמה). ראו להלן בתת-הסעיף "פעולות על קבוצות". כל זאת בעזרת אופרטורים כדוגמת On, In, Exist.
אילו עמודות (ומאילו טבלאות) להציג.
אגרגציה (קיבוץ) של שורות וסכימה על עמודות.
אילו שורות להציג, פלטור של הנתונים הגולמיים ושל ערכים המקובצים; בעזרת האופרטורים Where, Having, On.
חישובים ברמת השורה המתבססים על שורות אחרות (פונקציות חלון)^[14].
מיון סט הנתונים המוצג בעזרת האופרטור Order By.

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

חישובים אריתמטיים ומתמטיים על נתונים וסוגי נתונים מספריים.
חישובי תאריכים על נתונים וסוגי נתונים המייצגים תאריכים.
מניפולציות על טקסטים וסוגי נתונים טקסטואליים כולל תמיכה באותיות בשפות שונות (Collation).
המרה של נתונים מסוג אחד לאחר (Cast, Convert).
הצגת נתונים ייחודיים (Distinct).
הצגת נתונים חלקיים כשאין צורך להציג את כולם או כשמתבקש להציג כמות מוגבלת (Top).
ניתן לצרף לפקודות ה-DML "רמזים" (Hints^[15]) כדי לגרום למערכת לפעול בדרך מסוימת (למשל- להשתמש באינדקס מסוים ולא באחר).

בנוסף, ניתן לעשות שימוש ביכולות הרבות של שאילתות ה-Select כדי לקבל מידע על המערכת ממקורות כדוגמת:

טבלאות מערכת הכוללות מידע על המערכת עצמה (Meta Data).
DMV^[16]הכוללים מידע על השימוש השוטף במערכת.
משתני מערכת.

יש מספר כלים שמאפשרים לשלוף על ידי פקודות Select מידע ממקורות חיצוניים כמו שרתים או קבצים (OpenQuery, Linked Server).

פעולות על קבוצות

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

Union – איחוד.
Except – הפרש.
Intersect – חיתוך.

שינוי נתונים

בנוסף לשלוש הפקודות שצוינו (Insert, Update, Delete), יש ל-TSQL פקודות ייעודיות למקרים מיוחדים:

Truncate – פקודת מחיקה מהירה לטבלה שלמה.
Bulk Load, BCP ועוד – שליפה יעילה מקבצי טקסט, גליונות אלקטרוניים, קובצי XML וכו' לתוך טבלאות, ולהפך.
Linked Server - ביצוע פעולות DML בשרתים אחרים (לאו דווקא SQL Server).
Merge – שילוב של Insert & Update בו זמנית.

שונות

פקודות DML תומכות בכלים וסוגי נתונים מיוחדים כמו:

Geo Spatial – יצוג נקודות גאומטריות במישור או נקודות גאוגרפיות על פני כדור הארץ.
Hierarchyid – יצוג היררכיות, בעיקר לייעול הטיפול ומניעת רקורסיות וחישובים מורכבים ולא יעילים.
XML – פעולות DML על קבצים ומשתנים מסוג XML.
ניתוח טקסטים (Full Text Search).
קינון סטים של נתונים ו-CTE^[17]- פעולות על הגדרה של שליפה מטבלה במקום על הטבלה במישרין.
שימוש בפונקציות CLR שנכתבו ב-#C בדרך כלל, ומאפשרות לשלב יכולות פרוצדורליות בשפת SQL הדקלרטיבית.

פעולות DDL

המונח DDL ^[18] מתייחס בראש ובראשונה לפקודות ליצירה וטיפול בטבלאות, להבדיל מפקודות DML המשמשות לטיפול בנתונים שבטבלאות, אך במשמעותו הרחבה יותר הוא מתייחס לכל הטיפול באובייקטים השונים של SQL Server, מלבד הנתונים עצמם. TSQL מאפשרת לעבוד עם האובייקטים השונים, ליצור ולשנות אותם, להפעיל אותם וכו'. כל הפעולות המתבצעות על SQL Server, גם אם הן נעשות דרך הממשק הגרפי תוך שימוש באשפים (Wizards) ובעכבר, מתורגמות לפקודות TSQL. מספר דוגמאות להמחשה:

טיפול והתייחסות לארכיטקטורה של SQL Server (שרת => דטבייס => סכמה => טבלה), ולאובייקטים השונים:

Create - יצירת אובייקט.

Alter - שינוי אובייקט קיים.

Drop - מחיקת אובייקט.

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

Grant - מתן הרשאות.

Revoke - שלילת הרשאות.

Deny - מניעת הרשאות^[19].

הגדרת רפליקציות.
יצירת ג'ובים, תזמונם והרצתם.
משלוח דואר אלקטרוני.
יצירה, הפעלה ותחקור של מעקבים (Trace, Profiler, Extended Events) על השרת, למניעה ופתרון תקלות.

העיבוד הפיזי לעומת העיבוד הלוגי על פי תקן ANSI

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

Select Name, Family From TblStudents Where BirthCountry='Israel' Order By Family, Name;

היא פקודת TSQL תקנית המהווה תרגום מילולי מקורב למשפט "שלוף שם ומשפחה מטבלת סטודנטים של כל אלו שנולדו בישראל ומיין לפי משפחה ושם". בשפת תכנות אימפרטיבית כדוגמת C או ג'אווה פקודה כזו תוחלף באופן כללי בסדרת הוראות טכניות ליצירת קישור (Connection) לבסיס הנתונים, שליפה בלולאה של הנתונים, מיונם (בעזרת קוד או הפעלת מתודה מתאימה) והצגתם על המסך.

The processing of a SELECT statement according to ANSI SQL would be the following: הסדר הלוגי של הפסוקיות בפקודת Select טיפוסית מתאימה לצורת הדיבור הטיפוסית ולא לאופן בו הפעולה מתבצעת באופן פיזי, ולכן העיבוד הפיזי של השאילתה על ידי המנוע הוא בסדר שונה מאופן כתיבתה. למשל- בשפה מדוברת אנו עשויים לומר "הבא לי את המזלג ממגירת הסכו"ם שבארון", בעוד שפיזית הפעולה מתבצעת בסדא הפוך: "גש לארון, מצא את מגירת הסכו"ם, מצא את הסכום" וכו'.

העיבוד של פקודת Select לפי תקן ANSI SQL יהיה כדלקמן ^[20]:

select g.*
from users u inner join groups g on g.Userid = u.Userid
where u.LastName = 'Smith'
and u.FirstName = 'John'

פסוקית ה-From מתבצעת, ה-Cross Join או המכפלה הקרטזית נוצרת משתי הטבלאות ונוצרת טבלה וירטואלית Vtable1
פסוקית ה-On מתבצעת על Vtable1, ורק הרשומות שעומדות בתנאי g.Userid=u.Userid נכנסות לטבלה הווירטואלית Vtable2.
אם מתבצעת פקודה הכוללת Outer Join - הרשומות המתאימות שנמחקו מ-Vtable2 מתווספות ל-Vtable3, למשל בשאילתה הבאה:

select u.*
from users u left join groups g on g.Userid = u.Userid
where u.LastName = 'Smith'
and u.FirstName = 'John'

כל השורות מטבלת users שלא נמצאו להם התאמות בטבלת groups מתווספות ל-Vtable3.

פסוקית ה-Where מתבצעת, ובמקרה זה - רק רשומות של Smith מתווספות לטבלה הווירטואלית הבאה - Vtable4.
פסוקית ה-Group By מתבצעת; למשל אם הפקודה היא:

select g.GroupName, count(g.*) as NumberOfMembers
from users u inner join groups g on g.Userid = u.Userid
group by GroupName

אזי Vtable5 תכלול רשומות מ-Vtable4 שקובצו, במקרה זה לקבוצות לפי עמודה GroupName.

פסוקית ה-Having מתבצעת על הקבוצות של ה-Group By (בניגוד ל-Where שמתבצע לפני), והרשומות שעומדות בתנאי נכנסות לטבלה וירטואלית Vtable6. למשל:

select g.GroupName, count(g.*) as NumberOfMembers
from users u inner join groups g on g.Userid = u.Userid
group by GroupName
having count(g.*) > 5

פסוקית ה-Select מתבצעת לקראת הסוף (למרות שהיא ראשונה בפקודה!) בתור טבלה וירטואלית Vtable7.
פסוקית ה-Distinct מתבצעת, שורות כפולות מתוך Vtable7 מוסרות, והתוצאה מוחזרת בתור טבלה וירטואלית Vtable8.
פסוקית ה-Order By מתבצעת, הרשומות ממויינות ומוחזרות בתור Vcursor9. זהו Cursor ולא טבלה וירטואלית שכן ANSI מגדיר סט רשומות ממויין בתור Cursor (סט או קבוצה על פי הגדרתה בתורת הקבוצות מוגדר כאוסף של איברים, לסדר שלהם אין חשיבות, ולכן השימוש במונח Cursor למקרה בו יש חשיבות לסדר).

קישורים חיצוניים

הערות שוליים

^ "המנוע" הוא התוכנה ש"מאחורי הקלעים" שמעבדת את פקודות ה-TSQL ומבצעת אותן.
^ שליפה ועיבוד נתונים - ראה להלן.
^ לשם כך ניתן להיעזר בקוד CLR שהוא הרבה יותר יעיל בכך ונהנה מיכולות מוהנות ייעודיות.
^ ל-TSQL אין פונקציות לחישובים פיננסיים או סטטיסטיים (מעבר לממוצע וסטיית תקן פשוטים) כפי שיש למשל באקסל, או תמיכה בביטויים רגולריים.
^ משתמשת בפקודות דקלרטיביות (הצהרתיות) מה לעשות, בניגוד לשפות תכנות אימפרטיביות (פרוצדורליות) הכוללות פקודות איך לעשות.
^ הכוונה לתקן ANSI (של מכון התקנים האמריקני American National Standards Institute). ולתקן ISO
^ שפת תכנות נחשבת ל-Turing Complete אם היא יכולה לבצע מה שמכונת טיורינג מבצעת.
^ SQL Server Management Studio
^ Azure שימוש בשרתים של מיקרוסופט שהשימוש בהם נמכר כשירות (ולא כתוכנה המותקנת על השרת של הלקוח).
^ למשל פרוצדורה המחשבת סיכום מכירות שנתי לפי חודשים של סוכן מכירות, ומקבלת שני פרמטרים- מספר הסוכן ושנת הדו"ח.
^ משתנה הכולל סט נתונים: הגדרה של עמודות, ומפעילים עליו פקודות DML.
^ למשל- חיוב של חשבון אחד וזיכוי של הנגדי לו במערכת חשבונאית
^ Data Manipulation Language
^ חישוב הערך המקסימלי או המצטבר בקבוצת שורות, מספור שורות, פניה לשורה קודמת ועוד.
^ בשפות דקלרטיביות המתכנת אינו מגדיר איך לבצע את המשימה וזה נעשה אוטומטית על ידי המערכת, אך יש דרכים להשפיע עליה במקרה הצורך וה-Hints הם דוגמה לכך.
^ Data Management Views
^ Common Table Expression
^ Data Definition Language
^ פקודת Revoke שוללת הרשאה קיימת אך אינה מונעת במקרה שיש הרשאות נוספות, ואילו Deny מונעת באופן גורף גם אם יש הרשאות ישירות או עקיפות.
^ Inside Microsoft SQL Server 2005: T-SQL Querying by Itzik Ben-Gan, Lubor Kollar, and Dejan Karka

[1] "המנוע" הוא התוכנה ש"מאחורי הקלעים" שמעבדת את פקודות ה-TSQL ומבצעת אותן.

[2] שליפה ועיבוד נתונים - ראה להלן.

[3] לשם כך ניתן להיעזר בקוד CLR שהוא הרבה יותר יעיל בכך ונהנה מיכולות מוהנות ייעודיות.

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

[5] משתמשת בפקודות דקלרטיביות (הצהרתיות) מה לעשות, בניגוד לשפות תכנות אימפרטיביות (פרוצדורליות) הכוללות פקודות איך לעשות.

[6] הכוונה לתקן ANSI (של מכון התקנים האמריקני American National Standards Institute). ולתקן ISO

[7] שפת תכנות נחשבת ל-Turing Complete אם היא יכולה לבצע מה שמכונת טיורינג מבצעת.

[8] SQL Server Management Studio

[9] Azure שימוש בשרתים של מיקרוסופט שהשימוש בהם נמכר כשירות (ולא כתוכנה המותקנת על השרת של הלקוח).

[10] למשל פרוצדורה המחשבת סיכום מכירות שנתי לפי חודשים של סוכן מכירות, ומקבלת שני פרמטרים- מספר הסוכן ושנת הדו"ח.

[11] משתנה הכולל סט נתונים: הגדרה של עמודות, ומפעילים עליו פקודות DML.

[12] למשל- חיוב של חשבון אחד וזיכוי של הנגדי לו במערכת חשבונאית

[13] Data Manipulation Language

[14] חישוב הערך המקסימלי או המצטבר בקבוצת שורות, מספור שורות, פניה לשורה קודמת ועוד.

[15] בשפות דקלרטיביות המתכנת אינו מגדיר איך לבצע את המשימה וזה נעשה אוטומטית על ידי המערכת, אך יש דרכים להשפיע עליה במקרה הצורך וה-Hints הם דוגמה לכך.

[16] Data Management Views

[17] Common Table Expression

[18] Data Definition Language

[19] פקודת Revoke שוללת הרשאה קיימת אך אינה מונעת במקרה שיש הרשאות נוספות, ואילו Deny מונעת באופן גורף גם אם יש הרשאות ישירות או עקיפות.

[20] Inside Microsoft SQL Server 2005: T-SQL Querying by Itzik Ben-Gan, Lubor Kollar, and Dejan Karka

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

[10]

[11]

[12]

[13]

[14]

[15]

[16]

[17]

[18]

[19]

[20]