Transact-SQL

Transact-SQL או TSQL היא שפת פיתוח לבסיסי נתונים רלציוניים של מיקרוסופט וSybase.

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

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

הקדמה

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

סביבות הפיתוח המקובלות הן SSMS ^[4] – סביבת הפיתוח היעודית ל-SQL Server של מיקרוסופט, ו-Visual Studio – סביבת הפיתוח הכללית של מיקרוסופט.

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

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

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

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

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

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

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

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

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

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

שימוש ב-GoTo בסקריפטים.
הרצה אסינכרונית של סקריפטים דרך ג'ובים או Service Broker.
הרצת שורות פקודה מתוך סקריפטים (בנוסף ליכולת להריץ סקריפטים בעזרת שורת פקודה).

טרנזקציות

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

רקורסיה

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

פקודות DML

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

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

שליפת נתונים

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

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

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

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

^[8] ) כדי לגרום למערכת לפעול בדרך מסוימת (למשל- להשתמש באינדקס מסוים ולא באחר). בנוסף, ניתן לעשות שימוש ביכולות הרבות של שאילתות ה-Select כדי לתחקר את טבלאות המערכת הכוללות מידע רב על המערכת עצמה (Meta Data) ועל השימוש השוטף הנעשה בה.

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

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

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

פעולות DDL

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

טיפול והתייחסות לארכיטקטורה של SQL Server (שרת => דטבייס => סכמה => טבלה).
ביצוע ותזמון פעולות תחזוקה תקופתיות הכוללות גיבויים, טיפול בפרגמנטציה בטבלאות ובאינדקסים.
מניעה והתאוששות מתקלות בשרת (High Availability and Data Recovery) – שחזור דטבייסים מגיבויים, הפעלת שרתי גיבוי.
הגדרת משתמשים והענקת ושלילת הרשאות לגישה ושימוש בנתונים ובאובייקטים השונים.
הגדרת רפליקציות.
יצירת ג'ובים, תזמונם והרצתם.
משלוח דואר אלקטרוני.
עבודה מול "הענן"^[11].
יצירה, הפעלה ותחקור של מעקבים (Trace, Profiler, Extended Events) על השרת, למניעה ופתרון תקלות.

הערות שוליים

^ משתמשת בפקודות דקלרטיביות (הצהרתיות) מה לעשות, בניגוד לשפות תכנות פרוצדורליות הכוללות פקודות איך לעשות.
^ הכוונה לתקן ANSI (של מכון התקנים האמריקני American National Standards Institute). ולתקן ISO
^ שפת תכנות נחשבת ל-Turing Complete אם היא יכולה לבצע מה שמכונת טיורינג מבצעת.
^ SQL Server Management Studio
^ למשל פרוצדורה המחשבת סיכום מכירות שנתי לפי חודשים של סוכן מכירות, ומקבלת שני פרמטרים- מספר הסוכן ושנת הדו"ח.
^ למשל- חיוב של חשבון אחד וזיכוי של הנגדי לו במערכת חשבונאית
^ Ddata Manipulation Language
^ בשפות דקלרטיביות המתכנת אינו מגדיר איך לבצע את המשימה וזה נעשה אוטומטית על ידי המערכת, אך יש דרכים להשפיע עליה במקרה הצורך וה-Hints הם דוגמה לכך.
^ Common Table Expression
^ Ddata Definition Language
^ Azure שימוש בשרתים של מיקרוסופ שהשימוש בהם נמכר כשירות (ולא כתוכנה המותקנת על השרת של הלקוח).

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

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

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

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

[4] SQL Server Management Studio

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

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

[7] Ddata Manipulation Language

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

[9] Common Table Expression

[10] Ddata Definition Language

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

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

[10]

[11]