Transact-SQL

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

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

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

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

## הקדמה
### סביבות פיתוח והתממשקות
סביבות הפיתוח המקובלות ל-TSQL הן:
* SSMS– סביבת הפיתוח הייעודית ל-SQL Server של מיקרוסופט.
* ויז'ואל סטודיו – סביבת הפיתוח הכללית של מיקרוסופט.
ניתן בנוסף להשתמש בכלי פיתוח של צד שלישי.

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

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

### סקריפטים ותכנות פרוצדורלי
פקודות TSQL ניתן לאגד לסקריפטים הכוללים פקודות רבות ושיכולות לקבל פרמטרים בחלק מהמקרים

, כגון:
* פרוצדורות – מבצעות משימה מוגדרת.
* פונקציות – מחשבות ומחזירות ערך או סט נתונים.
* טריגר על טבלה (DML Trigger) – המופעל כשנתונים משתנים.
* טריגר על דטבייס (DDL Trigger) – המופעל כשחל שינוי באובייקטים בדטבייס.
* ג'וב – סקריפט המתוזמן לביצוע באופן אסינכרוני.
* קובצי טקסט חיצוניים הכוללים פקודות TSQL ושניתן להפעילם (למשל- דרך שורת פקודה).
TSQL מאפשר תכנות פרוצדורלי, למשל:
* פירוק של משימה למספר פרוצדורות משנה, והפעלתן על פי הסדר הלוגי מתוך הפרוצדורה הראשית.
* תנאי If .. Else המאפשרים בקרת ריצה.
* שימוש ב-Try & Catch לטיפול בשגיאות (שיגרת שגיאה).
* שימוש בלולאות.
בנוסף לכל הנ"ל כדאי לציין יכולות כמו:
* שימוש בפרמטרים ובמשתנים מקומיים (Local Variables) שמתאימים לכל סוגי הנתונים הנתמכים על ידי SQL Server, כולל משתני טבלה. אין תמיכה במשתנים גלובליים, וניתן לשנות את ערכי הפרמטרים כמו המשתנים.
* שימוש ב-GoTo בסקריפטים.
* הרצה אסינכרונית של סקריפטים דרך ג'ובים או Service Broker.
* הרצת שורות פקודה מתוך סקריפטים (בנוסף ליכולת להריץ סקריפטים בעזרת שורת פקודה).

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

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

## פקודות DML
המונח DML

מתייחס ללב ליבה של שפת SQL לגרסאותיה השונות והיא היכולת לשלוף ולשנות נתונים בעזרת פקודות. הפקודות היסודיות הן:
* Select – שליפת נתונים.
* Insert – הכנסת נתונים.
* Update – שינוי נתונים קיימים (עדכון).
* Delete – מחיקת נתונים.

### שליפת נתונים
במקרה של Select ניתן לצרף פסוקיות שמעצבות ומגדירות את סט הנתונים הנשלף:
* מאילו טבלאות לשלוף, בעזרת האופרטורים From, Join, Apply.
* כללי ההתאמה בין הנתונים מהמקורות השונים (למשל- לשלוף רק את השורות שיש ביניהן התאמה או לחלופין את אלו שאין להן התאמה). ראו להלן בתת-הסעיף "פעולות על קבוצות". כל זאת בעזרת אופרטורים כדוגמת On, In, Exist.
* אילו עמודות (ומאילו טבלאות) להציג.
* אגרגציה (קיבוץ) של שורות וסכימה על עמודות.
* אילו שורות להציג, פלטור של הנתונים הגולמיים ושל ערכים המקובצים; בעזרת האופרטורים Where, Having, On.
* חישובים ברמת השורה המתבססים על שורות אחרות (פונקציות חלון).
* מיון סט הנתונים המוצג בעזרת האופרטור Order By.
על הנתונים הנשלפים ניתן לבצע חישובים ומניפולציות בהתאם לסוג הנתונים ובעזרת פונקציות שונות של TSQL:
* חישובים אריתמטיים ומתמטיים על נתונים וסוגי נתונים מספריים.
* חישובי תאריכים על נתונים וסוגי נתונים המייצגים תאריכים.
* מניפולציות על טקסטים וסוגי נתונים טקסטואליים כולל תמיכה באותיות בשפות שונות (Collation).
* המרה של נתונים מסוג אחד לאחר (Cast, Convert).
* הצגת נתונים ייחודיים (Distinct).
* הצגת נתונים חלקיים כשאין צורך להציג את כולם או כשמתבקש להציג כמות מוגבלת (Top).
* ניתן לצרף לפקודות ה-DML "רמזים" (Hints) כדי לגרום למערכת לפעול בדרך מסוימת (למשל- להשתמש באינדקס מסוים ולא באחר).

בנוסף, ניתן לעשות שימוש ביכולות הרבות של שאילתות ה-Select כדי לקבל מידע על המערכת ממקורות כדוגמת:
* טבלאות מערכת הכוללות מידע על המערכת עצמה (Meta Data).
* DMVהכוללים מידע על השימוש השוטף במערכת.
* משתני מערכת.

יש מספר כלים שמאפשרים לשלוף על ידי פקודות 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- פעולות על הגדרה של שליפה מטבלה במקום על הטבלה במישרין.
* שימוש בפונקציות CLR שנכתבו ב-#C בדרך כלל, ומאפשרות לשלב יכולות פרוצדורליות בשפת SQL הדקלרטיבית.

## פעולות DDL
המונח DDL

מתייחס בראש ובראשונה לפקודות ליצירה וטיפול בטבלאות, להבדיל מפקודות DML המשמשות לטיפול בנתונים שבטבלאות, אך במשמעותו הרחבה יותר הוא מתייחס לכל הטיפול באובייקטים השונים של SQL Server, מלבד הנתונים עצמם.
TSQL מאפשרת לעבוד עם האובייקטים השונים, ליצור ולשנות אותם, להפעיל אותם וכו'.
כל הפעולות המתבצעות על SQL Server, גם אם הן נעשות דרך הממשק הגרפי תוך שימוש באשפים (Wizards) ובעכבר, מתורגמות לפקודות TSQL. מספר דוגמאות להמחשה:
* טיפול והתייחסות לארכיטקטורה של SQL Server (שרת => דטבייס => סכמה => טבלה), ולאובייקטים השונים:
:Create - יצירת אובייקט.
:Alter - שינוי אובייקט קיים.
:Drop - מחיקת אובייקט.
* ביצוע ותזמון פעולות תחזוקה תקופתיות הכוללות גיבויים, טיפול בפרגמנטציה בטבלאות ובאינדקסים.
* מניעה והתאוששות מתקלות בשרת (High Availability and Data Recovery) – שחזור דטבייסים מגיבויים, הפעלת שרתי גיבוי.
* הגדרת משתמשים והענקת ושלילת הרשאות לגישה ושימוש בנתונים ובאובייקטים השונים:
:Grant - מתן הרשאות.
:Revoke - שלילת הרשאות.
:Deny - מניעת הרשאות.
* הגדרת רפליקציות.
* יצירת ג'ובים, תזמונם והרצתם.
* משלוח דואר אלקטרוני.
* יצירה, הפעלה ותחקור של מעקבים (Trace, Profiler, Extended Events) על השרת, למניעה ופתרון תקלות.

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

```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 יהיה כדלקמן
:


```sql
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, למשל בשאילתה הבאה:

```sql
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 מתבצעת; למשל אם הפקודה היא:

```sql
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. למשל:

```sql
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 למקרה בו יש חשיבות לסדר).

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

*Sybase Transact-SQL User's Guide
*[Transact-SQL Reference for SQL Server 2000 (MSDN)](http://msdn2.microsoft.com/en-us/library/aa260642(SQL.80).aspx)
*Transact-SQL Reference for SQL Server 2005 (MSDN)
*[Transact-SQL Reference for SQL Server 2008 (MSDN)](http://msdn.microsoft.com/en-us/library/bb510741(SQL.100).aspx)
*Transact-SQL Reference for SQL Server 2012 (MSDN)
*Transact-SQL Tutorial

## הערות שוליים

קטגוריה:שפות תכנות