מספור שורות בטבלה

31/01/2013

אין תגובות

פנה אלי פלוני בשאלה כדלקמן: יש לו טבלה אליה הוא מכניס שורות בעזרת פרוצדורה בכל פרק זמן נתון, והוא רוצה למספר אותן. נניח שהוא הכניס שורות ביום ובשעת כתיבת שורות, אזי בעמודת התאריך יהיה כתוב 29/01/2013 07:29:15, והוא רוצה בנוסף שהשורות של תאריך זה יהיו ממוספרות. הוא מכיר את אופציית ה-Identity ליצירת מספור אוטומטי, אלא שאופציה זו ממספרת בסדר עולה את כל השורות ולא כל תאריך לחוד.. מה עושים?
בניגוד לפוליטיקאים שעונים על השאלות המופנות אליהן ב-"לא זו השאלה אלא.." ומנסחים שאלה חלופית עליה יש להם תשובה מן המוכן, אני משתדל לענות קודם כל לגופו של עניין גם אם השאלה והתשובה מופרכות; ורק לאחר מכן מנסה לברר למה בעצם השואל שואף להגיע, ולהציע פתרון לבעייה ולא תשובה לשאלה.
ניתן איפוא לאפס את ה-Identity בכל ריצה, וכך בכל פעם השורות החדשות ימוספרו מחדש מ-1 ואילך; ולהקפיד כמובן שעמודה זו לא תהיה עמודת מפתח כמקובל, מכיוון שיש בה כפילויות (אא"כ מדובר בשילוב שלה עם עמודת התאריך).
לדוגמה- טבלה בה נשמור את שמות ומספרי האובייקטים שבטבלת המערכת sys.objects:

Use tempdb;

Go

 

If Object_ID('tempdb..T1','U') Is Not Null Drop Table T1;

 

Create Table T1(ID Int Identity Not Null,

                [Date] DateTime Default GetDate() Not Null,

                [Object_ID] Int,

                Name Sysname);

 

Alter Table T1 Add Constraint[PK_T1] Primary Key Clustered([Date],

                                                           ID);

 

Insert  Into T1([Object_ID],Name)

Select  Object_ID,

        Name

From    sys.objects;

 

Select  * From T1;

clip_image002

אם נרצה להריץ שוב- נאפס קודם את ה-Identity:

DBCC CheckIdent(T1,Reseed,0);

 

Insert  Into T1([Object_ID],Name)

Select  Object_ID,

        Name

From    sys.objects;

 

Select  * From T1;

clip_image004

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

If Object_ID('tempdb..T2','U') Is Not Null Drop Table T2;

 

Create Table T2([Date] DateTime Default GetDate() Not Null,

                [Object_ID] Int Not Null,

                Name Sysname);

 

Alter Table T2 Add Constraint[PK_T2] Primary Key Clustered([Date],

                                                           [Object_ID]);

 

If Object_ID('V2','V') Is Not Null Drop View V2;

Go

 

Create  View V2 As

Select  Row_Number() Over(Partition By [Date] Order By [Object_ID]) ID,

        *

From    T2;

Go

 

Insert  Into T2([Object_ID],Name)

Select  Object_ID,

        Name

From    sys.objects;

 

Select  * From V2;

clip_image006

הפתרון שהוחלט עליו לבסוף היה שילוב של שני הנ"ל: טבלה עם עמודת תאריך ועמודת מונה, אך אכלוס עמודת המונה לא תהיה באמצעות מנגנון ה-Identity אלא Row_Number:

If Object_ID('tempdb..T3','U') Is Not Null Drop Table T3;

 

Create Table T3(ID Int Not Null,

                [Date] DateTime Default GetDate() Not Null,

                [Object_ID] Int,

                Name Sysname);

 

Alter Table T3 Add Constraint[PK_T3] Primary Key Clustered([Date],

                                                           ID);

 

Insert Into T3(ID,[Object_ID],Name)

Select  Row_Number() Over(Order By [Object_ID]) ID,

        Object_ID,

        Name

From    sys.objects;

 

Select  * From T3;

clip_image008

כדאי לשים לב שהפרמטר Partition By אינו נדרש בפונקצייה Row_Number במקרה זה מכיוון שבכל פעולת Insert – לכל השורות אותו תאריך. כלומר- גם אם לא נכניס לטבלה כמה מאות שורות אלא כמה מאות מליוני שורות, פעולה שעלולה להימשך לפחות מספר דקות, לכל השורות הנכנסות יהיה אותו תאריך ושעה.

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

כתיבת תגובה

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