מספר חידושי TSQL שימושיים ומעניינים ב MSSQL 2012

22 בפברואר 2012

תגיות: ,
אין תגובות

eli מאת: אלי לייבה

ישנם מספר חידושים מעניינים למפתחי TSQL ו DBA בגרסת 2012 , השינויים כוללים, בין השאר:

1) CLAUSES חדשים שנוספו לפקודת SELECT

2) אובייקט חדש במסד הנתונים – מונה (Sequence)

3) שיפורים ב SQL דינאמי – פונקצית EXEC

4) פונקציות המרה חדשות – נדון ב Parse

במאמר זה אתן דוגמא קצרה לכל חידוש מהנ"ל. לדעתי כל אחד משינויים אלה חשוב וניתן ליישום מיידית ובקלות. הדוגמאות המובאות כאן פשוטות מאוד ומוצגות לצורך הבהרת הנושא. לצורך מעבר מלא על כל האפשרויות הנרחבות לכל אופציה נא פנו ל BOL (Books Online) לקבלת ההסבר המלא.

1.תוספות למשפט SELECT

זוהי תוספת מצוינת שמתפקדת טוב כפונקציה "חלון" – בתוך Result set

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

הפסוקיות החדשות שנוספו לפקודת ה SELECT הינן:

OFFSET <EXPR> ROWS = לציין מאיזו שורה להתחיל את הצגת הנתונים

FETCH NEXT <EXPR> ROWS ONLY = כמה שורות להביא בתשובה המוחזרת

באופן שלם – התוספת הינה כך:

SELECT * FROM …..

ORDER BY …..

OFFSET <EXPR> ROWS

FETCH NEXT <EXPR> ROWS ONLY

דוגמא: להביא 3 שמות בסיסי נתונים מהשרת ממוינים שמית , להתחיל לשלוף לאחר 2 שורות.

SELECT * FROM SYS.databases T

ORDER BY T.name

OFFSET 2 ROWS

FETCH NEXT 3 ROWS ONLY

  1. אובייקט המונה – Sequence

באמצעות אובייקט חדש זה ניתן לבצע מנייה שאיננה תלויה בטבלה אלא עצמאית לגמרי

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

בגרסא זו , מדובר באובייקט עצמאי שניתן לנהל עבורו צורת התקדמות , ערכים מינימאלי ומכסימלי , כוונון cache ועוד (בדומה לאובייקט המקביל ב Oracle RDBMS )

דוגמא:

— Sequence create TSQL

CREATE SEQUENCE dbo.MySeq AS INT START WITH 1 INCREMENT BY 1;

GO

— Table create

Create Table dbo.Products (ProdID , ProductName)

Go

— advance Sequence using NEXT VALUE FOR <sequence> CLAUSE

INSERT INTO dbo.Products (ProdID, ProductName)

VALUES (NEXT VALUE FOR dbo.MySeq, 'Television');

INSERT INTO dbo.Products (ProdID, ProductName)

VALUES (NEXT VALUE FOR dbo.MySeq, 'Bike');

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

CREATE SEQUENCE <Sequence> AS INT START WITH <min Value> INCREMENT BY <step>

(למבנה המלא פנו לתיעוד)

לצורך קידום המונה משתמשים בפסוקית החדשה : NEXT VALUE FOR <Sequence>

3. תוספות ל Dynamic SQL

עד לגרסת SQL2012 כשרצינו לבצע Dynamic SQL שמחזיר שאילתא היינו צריכים להגדיר מראש מבנה של טבלה זמנית או טבלת זכרון שיכיל את תוצאות הרצת השאילתא.

כעת , יש לנו אפשרות באמצעות הפסוקית : WITH Result SETS שמצטרפת לפקודת הרצת Dynamic SQL execution לבצע יחד עם הרצת הפקודה את הצגת את ה Result sets באיזה מבנה ושמות עמודות שאנחנו רוצים ולא להישאר כפופים לשמות ולסוגים שפקודת ה SQL הפנימית כפתה עלינו.

דוגמא לנושא:

DECLARE @tsql VARCHAR(100)

SET @tsql = 'SELECT T.database_id, T.name FROM sys.databases T'

EXEC (@TSQL) WITH Result SETS ( ( DbID bigInt, DbName varchar(100)))

בדוגמא זו אנו רואים שהתשובה המוחזרת הינה בשמות ובסוגים שאנו קובעים (DbId, DbName) ולא השמות והסוגים הפנימיים (למשל database_id הוא smallint בהגדרה אך אנו משנים זאת ל bigInt כרצוננו)

4. פונקצית ההמרה Parse

עד גרסת 2012 היינו משתמשים בפונקציות ההמרה cast ו convert . החיסרון בפונקציות אלו שהיו מבוססות TSQL ושהטיפול בשגיאות המרה ובתאריכים היה מסורבל. היה צורך לזכור באיזה פורמט תאריך משתמשים ואז להשתמש בטבלת פורמטים של מספר שמשייכים לפורמט למשל תאריך בפורמט אירופי dd/mm/yy מיוצג ע"י פורמט מספר 103.

עתה יש לנו פונקציות המרה בשם Parse וכן Try_Parse שמשתמשות פנימית ב .net Framework

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

דוגמא:

SET LANGUAGE english

SELECT PARSE ('12/27/2011' AS datetime) AS result

התאריך שמיוצג כאן במחרוזת עובר המרה לתאריך באמצעות ה framework ומוכנס אח"כ למשתנה תאריכי מתאים.

לפונקציה Parse יש מקבילה – הפונקציה try_parse שתבצע אותו דבר אך במידת כשלון, ההמרה לא תפסיק את פעולת התוכנית אלא תכניס NULL לתשובה ותמשיך.

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

בעל 20 שנות ותק בתחום מסדי נתונים ותכנות. iecdba@hotmail.com

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *