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

11/08/2010

מספור אוטומטי – עמודת Identity

Filed under: Uncategorized — תגיות: , , , , , , — גרי רשף @ 18:32

Identity היא תכונה ההופכת עמודה מספרית של שלמים לעמודת מספור אוטומטי, כאשר ניתן להגדיר לה מספר התחלתי (Seed) וקבוע גידול (Increment); וברירת המחדל אם לא הוגדר אחרת – (1,1), כלומר- רצף המספרים הטבעיים החל מ-1.
נגדיר שתי טבלאות עם עמודת Identity, ולאחת מהם נוסיף View הפונה אליה:

Use tempdb;

Go

Create Table T1(ID Int Identity);

Go

Create View V1 As

Select    *

From    T1;

Go

Create Table T2(ID Int Identity(1000,10) Unique Clustered,

                Shem Varchar(50),

                Taarih DateTime Default GetDate());

Go

מכיוון שערכי עמודת ה-Identity גדלים כל הזמן (או קטנים אם משום מה בחרנו ב-Incremental שלילי) – העמודה נוחה לשימוש כ-Clustered Index (לא מומלץ לבחור לשם כך בעמודה לתוכה מכניסים ערכים באמצע מכיוון שזה יגרום לדפרגמנטציה).

בטבלה הראשונה ה-Identity מתבסס על ערכי ברירת המחדל,

ובטבלה השניה – מתחיל מ-1000 וגדל ב-10 בכל פעם.

נכניס חמש שורות חדשות לטבלה T1 ומכיוון שלא ניתן להכניס ערכים מפורשים לעמודת Identity (חוץ מחריג שיוסבר בהמשך) – נשתמש ב-Default Value,

נמחק את השורות עם הערכים 1,3,5,

נכניס עוד חמישה ערכים,

נמחק את הערכים 8,9,10,

ולאחר כל פעולה נעיין בתכולת הטבלה:

Insert Into T1 Default Values;

Go 5

Select * From T1 Order By ID;

Go

Delete From T1 Where ID In (1,3,5);

Go

Select * From T1 Order By ID;

Go

Insert Into T1 Default Values;

Go 5

Select * From T1 Order By ID;

Go

Delete From T1 Where ID In (8,9,10);

Go

Select * From T1 Order By ID;

Go

כפי שאפשר לראות – עמודת ה-Identity "זוכרת" את הערכים שהיו לה ואינה ממחזרת אותם; ולכן בפעם השניה נכנסו הערכים 6-10 למרות שהערך 5 לא היה קיים.

יש לכך הגיון מבחינת היחודיות של הערכים, ומי שמעוניין בערכים רציפים ומיחזור של ערכים ישנים- שיחולל אותם בעצמו ויוותר על שירותי ה-Identity.

ניתן לקבל מידע על עמודת ה-Identity של טבלה T1 כך:

Select    Ident_Seed('T1') Ident_Seed,

        Ident_Incr('T1') Ident_Incr,

        Ident_Current('T1') Ident_Current;

Go

במקרה זה רואים שהערך השוטף Ident Current הוא 10 למרות שאינו קיים כלל בטבלה, וניתן ללמוד מכך שהערך הבא – אם נכניס שורה חדשה – הוא 11.

נכניס מעט נתונים גם לטבלה השניה:

Insert Into T2(Shem)

Select    'Anat'

Union All

Select    'Beni';

Go

Select * From T2 Order By ID;

Go

ניתן לקבל מידע על עמודות ה-Identity של כל הטבלאות כך:

Select    object_id,

        Object_Name(object_id) Tbl,

        name,

        seed_value,

        increment_value,

        last_value,

        Ident_Seed(object_name(object_id)) Ident_Seed,

        Ident_Incr(object_name(object_id)) Ident_Incr,

        Ident_Current(object_name(object_id)) Ident_Current

From    sys.identity_columns;

Go

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

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

ניתן לאתחל מחדש את ה-Ident_Current של הטבלה, למשל-

DBCC CheckIdent(T1,Reseed,7);

Go

Insert Into T1 Default Values;

Go

Select * From T1 Order By ID;

Go

במקרה זה ה-Ident_Current עודכן ל-7, ולכן כשהוספנו ערך חדש הוא היה 8.

מכיוון שהעמודה בטבלה זו לא הוגדרה כ-Unique – ניתן לחזור על ערכים קיימים.

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

Set Identity_Insert T1 On;

Go

Insert Into T1(ID)

Select    1 Union All

Select    3 Union All

Select    5;

Go

Set Identity_Insert T1 Off;

Go

Select * From T1 Order By ID;

Go

יש לציין שבכל נקודת זמן רק טבלה אחת יכולה "להינות" מ- Identity_Insert On..

25/08/2010: כדאי לעיין בפוסט הקצר הבא לגבי השימוש ב-Identity@@ ב-Session, וב-()Scope_Identity ב-Scope.

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

2 תגובות »

  1. […] כתבתי פוסט לגבי עמודת Identity וציינתי שם שניתן לאפס את המונה בטבלה MyTbl ולהתחיל מחדש […]

    פינגבאק של כיצד מאפסים עמודת מספור אוטומטי (Identity) בטבלה? - גרי רשף — 30/11/2011 @ 19:50

  2. […] כתבתי פוסט לגבי עמודת Identity וציינתי שם שניתן לאפס את המונה בטבלה MyTbl ולהתחיל מחדש […]

    פינגבאק של כיצד מאפסים עמודת מספור אוטומטי (Identity) בטבלה? « הבלוג של גרי רשף — 28/11/2011 @ 17:39


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

בלוג בוורדפרס.קום.

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