יצירת ערכים אוטומטיים
ייתכן שיהיה עליך ליצור באופן אוטומטי ערכים רציפים עבור עמודה אחת בטבלה ספציפית. Transact-SQL מספק שתי דרכים לעשות זאת: השתמש במאפיין IDENTITY עם עמודה ספציפית בטבלה, או הגדר אובייקט SEQUENCE והשתמש בערךים שנוצרו על-ידי אובייקט זה.
המאפיין IDENTITY
כדי להשתמש במאפיין IDENTITY, הגדר עמודה באמצעות סוג נתונים מספרי עם קנה מידה של 0 (כלומר מספרים שלמים בלבד) וכלול את מילת המפתח IDENTITY. הסוגים המעניקים הרשאה כוללים את כל סוגי המספרים העשרוניים וסוגי המספרים העשרוניים שבהם אתה מעניק במפורש קנה מידה של 0.
ניתן גם להוסיף סוגים אופציונליים של נוסחה (ערך התחלתי) והפרש קבוע (ערך שלב). אם לא תנתק את הכללים וההפרשים ההפרשים יגדירו את שניהם ל- 1.
הערה
המאפיין IDENTITY מצוין במקום לציין NULL או NOT NULL בהגדרת העמודה. עמודה כלשהי עם המאפיין IDENTITY אינה ניתנת ל- Null באופן אוטומטי. באפשרותך לציין NOT NULL רק עבור תיעוד עצמי, אך אם תציין את העמודה כ- NULL (כלומר Null), משפט יצירת הטבלה ייצור שגיאה.
ניתן להגדיר את המאפיין IDENTITY בעמודה אחת בלבד בטבלה אחת; הוא משמש לעתים קרובות כמפתח הראשי או כמפתח חלופי.
הקוד הבא מציג את יצירת הטבלה Sales.Promotion המשמשת בדוגמאות של המקטע הקודם, אך הפעם עם עמודת זהות בשם PromotionID כמפתח הראשי:
CREATE TABLE Sales.Promotion
(
PromotionID int IDENTITY PRIMARY KEY,
PromotionName varchar(20),
StartDate datetime NOT NULL DEFAULT GETDATE(),
ProductModelID int NOT NULL REFERENCES Production.ProductModel(ProductModelID),
Discount decimal(4,2) NOT NULL,
Notes nvarchar(max) NULL
);
הערה
הפרטים המלאים של משפט CREATE TABLE אינם נכללים בטווח של מודול זה.
הוספת נתונים לעמודת זהות
כאשר המאפיין IDENTITY מוגדר עבור עמודה, בדרך כלל, משפטי INSERT לטבלה אינם מציינים ערך עבור העמודה IDENTITY. מנגנון מסד הנתונים יוצר ערך באמצעות הערך הזמין הבא עבור העמודה.
לדוגמה, באפשרותך להוסיף שורה לטבלה Sales.Promotion מבלי לציין ערך עבור העמודה PromotionID :
INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.10, '10% discount')
שים לב של למרות שהפסוקית VALUES אינה כוללת ערך עבור העמודה PromotionID , אין צורך לציין רשימת עמודות במשפט INSERT - עמודות הזהות פטורות מדרישות אלה.
אם שורה זו היא השורה הראשונה שנוספה לטבלה, התוצאה היא שורה חדשה באופן הבא:
מזהה קידום מכירות
שם קידום מכירות
תאריך התחלה
ProductModelID
הנחה
הערות
1
מכירת חיסול
2021-01-01T00:00:00
23
0.1
10% הנחה
כאשר הטבלה נוצרה, לא הוגדר ערך של תרשים או ערך בהפרש קבוע עבור העמודה IDENTITY, כך שהשורה הראשונה נוספת עם ערך של 1. לשורה הבאה להוספה יוקצה ערך PromotionID של 2 וכן הלאה.
מאחזר ערך זהות
כדי להחזיר את ערך IDENTITY האחרון שהוקצה בתוך אותה הפעלה ואותו טווח, השתמש SCOPE_IDENTITY הקודמת; כמו זה:
SELECT SCOPE_IDENTITY();
הפונקציה SCOPE_IDENTITY מחזירה את ערך הזהות העדכני ביותר שנוצר בטווח הנוכחי עבור כל טבלה. אם אתה זקוק לערך הזהות העדכני ביותר בטבלה ספציפית, באפשרותך להשתמש בפונקציה IDENT_CURRENT, באופן הבא:
SELECT IDENT_CURRENT('Sales.Promotion');
עוקף ערכי זהות
אם ברצונך לעקוף את הערך שנוצר באופן אוטומטי ולהקצות ערך ספציפי לעמודה IDENTITY, תחילה עליך להפוך הוספות זהויות לזמינות באמצעות המשפט SET IDENTITY INSERT table_name ON. כאשר אפשרות זו זמינה, באפשרותך להוסיף ערך מפורש עבור עמודת הזהות, בדיוק כמו כל עמודה אחרת. לאחר שתסיים, תוכל להשתמש במשפט SET IDENTITY INSERT table_name OFF כדי לחדש את השימוש בערך זהות אוטומטי, תוך שימוש בערך האחרון שהזנת במפורש כגרם.
SET IDENTITY_INSERT SalesLT.Promotion ON;
INSERT INTO SalesLT.Promotion (PromotionID, PromotionName, ProductModelID, Discount)
VALUES
(20, 'Another short sale',37, 0.3);
SET IDENTITY_INSERT SalesLT.Promotion OFF;
כפי שלמדת, המאפיין IDENTITY משמש ליצירת רצף ערכים עבור עמודה בתוך טבלה. עם זאת, המאפיין IDENTITY אינו מתאים לתיאום ערכים בין טבלאות מרובות בתוך מסד נתונים. לדוגמה, נניח שהארגון שלך מבדיל בין מכירה ישירה למכירות למ מכירה, והוא מעוניין לאחסן נתונים עבור מכירות אלה בטבלאות נפרדות. שני סוגי המכירה עשויים לדרוש מספר חשבונית ייחודי, וייתכן שתרצה להימנע משכפול אותו ערך עבור שני סוגים שונים של מכירה. פתרון אחד עבור דרישה זו הוא שמירה על מאגר של ערכים רציפים ייחודיים על-פני שתי הטבלאות.
ניתוח מחדש של עמודת זהות
מדי פעם, יהיה עליך לאפס או לדלג על ערכי זהות עבור העמודה. לשם כך, "תחבר מחדש" את העמודה באמצעות הפונקציה DBCC CHECKIDENT. באפשרותך להשתמש באפשרות זו כדי לדלג על ערכים רבים, או כדי לאפס את ערך הזהות הבא ל- 1 לאחר מחיקת כל השורות בטבלה. לקבלת פרטים מלאים באמצעות DBCC CHECKIDENT, עיין בתיעודTransact-SQL הבא.
רצף
ב- Transact-SQL, באפשרותך להשתמש באובייקט רצף כדי להגדיר ערכים רציפים חדשים ללא קשר לטבלה ספציפית. אובייקט רצף נוצר באמצעות המשפט CREATE SEQUENCE, אשר מספק באופן אופציונלי את סוג הנתונים (חייב להיות סוג מספר שלם או מספר עשרוני או מספרי עם קנה מידה של 0), הערך ההתחלתי, ערך ברווחים קבועים, ערך מרבי ואפשרויות אחרות הקשורות לביצועים.
CREATE SEQUENCE Sales.InvoiceNumber AS INT
START WITH 1000 INCREMENT BY 1;
כדי לאחזר את הערך הזמין הבא מרצף, השתמש ב- NEXT VALUE FOR construct, באופן הבא:
INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);
IDENTITY או SEQUENCE
בעת החלטה אם להשתמש בעמודות IDENTITY או באובייקט SEQUENCE לאכלוס אוטומטי של ערכים, זכור את הנקודות הבאות:
השתמש בפונקציה SEQUENCE אם היישום שלך דורש שיתוף סידרת מספרים אחת בין טבלאות מרובות או עמודות מרובות בתוך טבלה.
הפונקציה SEQUENCE מאפשרת לך למיין את הערכים לפי עמודה אחרת. THE NEXT VALUE FOR construct can use the OVER clause to specify the sort column. פסוקית OVER מבטיחה שהערכים המוחזרים נוצרים בסדר המשפט ORDER BY של פסוקית OVER. פונקציונליות זו גם מאפשרת לך ליצור מספרי שורות עבור שורות כאשר הן מוחזרות ב- SELECT. בדוגמה הבאה, הטבלה Production.Product ממוינים לפי העמודה שם , והעמודה המוחזרת הראשונה היא מספר רציף.
SELECT NEXT VALUE FOR dbo.Sequence OVER (ORDER BY Name) AS NextID, ProductID, Name FROM Production.Product;למרות שההצהרה הקודמת רק בחרה ערכי SEQUENCE להצגה, הערכים עדיין נמצאים בשימוש, והערכים המוצגים SEQUENCE לא יהיו זמינים עוד. אם אתה מפעיל את ה- SELECT שלעיל כמה פעמים, תקבל ערכי SEQUENCE שונים בכל פעם.
השתמש ב- SEQUENCE אם היישום שלך דורש להקצות מספרים מרובים בו-זמנית. לדוגמה, יישום צריך לשריין חמישה מספרים רציפים. בקשת ערכי זהות עלולה להוביל לפערים בסדרה אם תהליכים אחרים הונפקו בו-זמנית על מספרים. באפשרותך להשתמש בהליך sp_sequence_get_range כדי לאחזר מספר מספרים ברצף בו-זמנית.
הפונקציה SEQUENCE מאפשרת לך לשנות את מפרט הרצף, כגון ערך ההפרש הקבוע.
ערכי IDENTITY מוגנים מפני עדכונים. אם תנסה לעדכן עמודה עם המאפיין IDENTITY, תקבל שגיאה.