הבלוג של גרי רשף

14/11/2010

Sequence – מספור אוטומטי משותף

Filed under: Uncategorized — תגיות: , , , , — גרי רשף @ 19:46

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

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

ניתן היה לחשוב על כל מיני פתרונות:

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

2. טבלה משותפת כנ"ל עם העמודות המשותפות, וטבלאות נפרדות עם העמודות הנפרדות.

3. מספור אוטומטי מ-1 ומעלה בטבלה אחת, ומספור אוטומטי מ-231 ומטה בטבלה השניה (הגבול העליון של Integer).

מה קורה כשרוצים לצרף אובייקטים נוספים לסידור הזה? למשל- רוצים שהמספר 3 הנ"ל יהיה שייך רק לאובייקט אחד (האובייקטים- מספרי הזמנות, חשבוניות, מספרי עובדים, מספרי ציוד, פריטי מלאי וכו'); מה עושים אז כשמדובר בפריטים כל כך שונים שחוץ מעמודת המספור האוטומטי אין להם שום דבר משותף; או אולי במצב בו מדי פעם עלולה להתווסף עוד טבלה ל"שיתוף" וצריך למצוא לה תחום מספרים מתאים?

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

אפשרות נוספת – וריאציה על הטבלה המשותפת לספקים וללקוחות – היא טבלה שיש בה רק עמודה אחת, עמודת Identity, ובכל פעם שמוסיפים שורה לאחת מהטבלאות הנ"ל – מוסיפים קודם שורה (בת עמודה אחת..) לטבלת ה-Identities ומשתמשים בערך שנוצר.. לאיציק בן-גן הייתה פעם הצעה משופרת- לפתוח טרנזקציה, להוסיף לטבלה שורה, ללכוד את הערך, ולבצע Rollback כדי לא לנפח אותה שלא לצורך (ביצוע Rollback מבטל את הוספת השורה אך לא את הגידול ב-1 של המונה).

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

ניצור אחד לדוגמה (עבור מי שכבר התקין את הגרסה הראשונה של Denali):

Use tempdb;

Go


If Object_id('dbo.MySeq') Is Not Null Drop Sequence MySeq;

Go


Create Sequence MySeq;

Go

מכיוון שבחרנו בכל ברירות המחדל- המונה יתחיל מ- (2147483648-) שזה ערכו המינימלי של Int,

ויגדל ב-1 בכל פעם,

למשל:

Select  Next Value For MySeq [Mone],

        *

From    sys.objects;

אם נריץ שוב- הערכים בעמודת Mone כבר יהיו אחרים..

כללית- ניתן לשחק עם ההגדרות של ה-Sequence לפי צרכינו:

If Object_id('dbo.MySeq') Is Not Null Drop Sequence MySeq

Go


Create Sequence MySeq As TinyInt

    Start With 100

    Increment By 1

    MinValue 95

    MaxValue 105

    Cycle;

Go

במקרה זה ה-Sequence יוצר מספרים מסוג TinyInt (0-127),

מתחיל מ-100,

גדל בכל פעם ב-1,

הערך המינימלי הוא 95 (לשם הוא יחזור כשיגיע למקסימום ויש אופציית Cycle – ראו להלן),

והערך המקסימלי 105 (אחריו תיווצר שגיאה ללא אופציית Cycle או שהוא יחזור לערך המינימלי עם האופציה).

אם נריץ כעת שוב את השאילתה הנ"ל על sys.objects נקבל בעמודת מונה ערכים שחוזרים על עצמם בין 95 ו-105, החל מ-100.

את רשימת ה-Sequences והגדרותיהם ניתן לראות בטבלת המערכת Sys.Sequences או כרגיל ב-Sys.objects.

מודעות פרסומת

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

יצירה של אתר חינמי או בלוג ב־WordPress.com.

%d בלוגרים אהבו את זה: