Sequence – רצף

By | 14 בנובמבר 2011

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

במידה והיה לנו SQL Server 2012, יכולנו לבצע את סינכרון שני המונים בטבלאות נפרדות בצורה יעילה יותר באמצעות רכיב חדש – Sequence (סדרה, רצף) . Sequence יוצר מספר רץ אשר בניגוד ל- Identity אשר מוגדר במסגרת של טבלה מסויימת, Sequence משמש ברמת בסיס נתונים ולא ברמת טבלה. כלומר, ניתן להגדיר בשתי טבלאות שונות שדה עם מספר רץ אשר לא יחזור על עצמו בשתי הטבלאות. כמובן שמקרה זה יהיו בכל אחת מהטבלאות “חלונות” בין המספרים הרצים.

כמה יתרונות של Sequence לעומת  Identity :

  • Sequence כאמור אינו מוגבל לטבלה בודדת. ניתן לשמר חד ערכיות על פני מספר טבלאות בבסיס הנתונים. בעבר היינו צריכים להשתמש בטבלה ייחודית או ב- newid כדי להשיג מטרה זו.
  • Sequence מנוהל בזיכרון ולכן מהיר יותר מ- Identity אשר מחייב פעולת I/O עקב תלותו בטבלה. ניתן להגדיר ל- Sequence כיצד להשתמש בזיכרון (כמה cache לנצל).
  • ניתן להשתמש ב- Sequence כ- Default בשדה של טבלה. לדוגמה: ישנו שדה של מק”ט אשר לעיתים נשאר ריק. ניתן להגדיר שרק במקרים בהם לא הגיע מק”ט בפקודת ה- INSERT, יכניס ה- Sequence מספר רץ. זאת בניגוד ל- Identity אשר מוגדר ברמת השדה כחלק ממבנה הטבלה. אפשר גם להשתמש ב- Sequence עם case בפעולת  ה- Insert וכך להכניסו לטבלה רק במקרים מסויימים.
  • ניתן להגדיר שה- Sequence יגיע למשל רק עד 1000 ואז יתאפס ויתחיל מהתחלה. בצורה זו ניתן לשלוט על ה- TYPE של ערך ה- Sequence. לדוגמא אם נרצה להשתמש במספר של ה-Sequence עם ערכי tiny int, נגדיר לו איפוס ב- 250.

הגדרת Sequence באמצעות SSMS:

קליק ימני על Sequences אשר נמצא בתיקייה של database מסויים תחת programmability:

SQL2012_Sequances

 

 

 

 

 

 

 

 

 

 

 

 

דוגמאות לשימושים:

תחילה נבנה את הרצף:

CREATE SEQUENCE Mone

    AS INT

    START WITH 1

    INCREMENT BY 1

       MINVALUE 1

       MAXVALUE 1000000

       CYCLE

       CACHE 50;

 

נשתמש ברצף שהגדרנו על מנת למלא שדה בטבלה. שימוש זה מקביל להגדרת השדה Mone בטבלה כ- Identity.

CREATE TABLE Seq_Table (Gk int, col2 varchar(50))

GO

INSERT Seq_Table (Gk, col2)

VALUES (NEXT VALUE FOR Mone, 'רצף מספרי רץ')

GO 100

SELECT * FROM Seq_Table

 

 

Next value for הינה פקודה חדשה אשר “מקדמת” את ערך  ה- Sequance.

Go 100 מריץ את פקודת ה- Insert מאה פעמים. התוצאה:

sequance_table

 

שימוש ב- Sequence כברירת מחדל של שדות בשתי טבלאות שונות.

-- בניית טבלה ראשונהCREATE TABLE Table1(       Id_with_default1 int DEFAULT (NEXT VALUE FOR Mone),       col2 varchar(50))GO

-- בניית טבלה שניהCREATE TABLE Table2(       Id_with_default2 int DEFAULT (NEXT VALUE FOR Mone),       col2 varchar(50))GO

-- עשר רשומות לכל טבלה

INSERT Table1(col2)VALUES ('ניסוי רצף טבלה 1') -- לא מוכנס ערך לעמודה הרשונה בטבלה הראשונהGO 5

INSERT Table2(col2)VALUES ('ניסוי רצף טבלה 2') -- לא מוכנס ערך לעמודה הרשונה בטבלה השניהGO 10

INSERT Table1(col2)VALUES ('ניסוי רצף טבלה 1') -- לא מוכנס ערך לעמודה הרשונה בטבלה הראשונהGO 5

select * from table1select * from table2

 

sequance_two_tables

המונה מתחיל ממאה כיוון שבדוגמה הראשונה קידמנו אותו למאה וכאמור הוא שומר על הספירה בין טבלאות שונות. בטבלה הראשונה מוכנסות 5 רשומות עם המספרים 101 עד 105. לאחר מכן מוכנסות עשר רשומות לטבלה השניה (106-115) ואז מוכנסות עוד חמש רשומות לטבלה הראשונה (116-120).

 

 

הערה: תודה לגרי רשף על הטיפ המאפשר להכניס את הסקריפטים למאמר בצורה הרבה יותר יפה ממה שיכולתי עד היום. לא תמיד היקר הוא הטוב. במקרה זה מוצר החינם של מיקרוסופט: Live Writer עולה על Word 2010 מבחינת איכות הפרסום לבלוג.

Share

כתיבת תגובה

האימייל לא יוצג באתר.