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

תחביר

להלן התחביר להמחשת השימוש במאפיין IDENTITY ב-SQL Server:

 IDENTITY[(seed, increment)] 

פרמטרי התחביר לעיל מוסברים להלן:

זֶרַע:זה מציין את הערך ההתחלתי של השורה שנטענת לטבלה. כברירת מחדל, הערך שלו הוא 1.תוֹסֶפֶת:זה מציין את הערך המצטבר, שמתווסף לערך הזהות של השורה האחרונה שנטענה. כברירת מחדל, הערך שלו הוא 1.

הבה נבין את המושג הזה באמצעות דוגמה פשוטה.

נניח שיש לנו ' סטוּדֶנט שולחן, ואנחנו רוצים תעודת סטודנט שייווצר אוטומטית. יש לנו תעודת סטודנט מתחיל של 10 ורוצים להגדיל אותו ב-1 עם כל מזהה חדש. בתרחיש זה, יש להגדיר את הערכים הבאים.

זֶרַע: 10

תוֹסֶפֶת: 1

 CREATE TABLE Student ( StudentID INT IDENTITY(10, 1) PRIMARY KEY NOT NULL, ) 

הערה: רק עמודת זיהוי אחת מותרת לכל טבלה ב-SQL Server.

SQL Server IDENTITY דוגמה

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

מאפיין IDENTITY עם טבלה חדשה

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

 CREATE TABLE person ( PersonID INT IDENTITY(10,1) PRIMARY KEY NOT NULL, Fullname VARCHAR(100) NOT NULL, Occupation VARCHAR(50), Gender VARCHAR(10) NOT NULL ); 

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

 INSERT INTO person(Fullname, Occupation, Gender) OUTPUT inserted.PersonID VALUES('Sara Jackson', 'HR', 'Female'); 

ביצוע שאילתה זו יציג את הפלט שלהלן:

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

בוא נכניס שורה נוספת לתוך שולחן אדם כלהלן:

 INSERT INTO person(Fullname, Occupation, Gender) OUTPUT inserted.* VALUES('Mark Boucher', 'Cricketer', 'Male'), ('Josh Phillip', 'Writer', 'Male'); 

שאילתה זו תחזיר את הפלט הבא:

פלט זה מראה שהשורה השנייה הוכנסה עם הערך 11 והשורה השלישית עם הערך 12 בעמודה PersonID.

מאפיין IDENTITY עם טבלה קיימת

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

 DROP TABLE person; 

לאחר מכן, ניצור טבלה באמצעות השאילתה הבאה:

 CREATE TABLE person ( Fullname VARCHAR(100) NOT NULL, Occupation VARCHAR(50), Gender VARCHAR(10) NOT NULL ); 

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

 ALTER TABLE person ADD PersonID INT IDENTITY(10,1) PRIMARY KEY NOT NULL; 

הוספת ערך בעמודת הזהות באופן מפורש

אם נוסיף שורה חדשה לטבלה שלמעלה על-ידי ציון ערך עמודת זהות במפורש, SQL Server יזרוק שגיאה. ראה את השאילתה למטה:

 INSERT INTO person(Fullname, Occupation, Gender, PersonID) VALUES('Mary Smith', 'Business Analyst', 'Female', 13); 

ביצוע שאילתה זו יגרום לשגיאה הבאה:

כדי להוסיף את ערך עמודת הזהות באופן מפורש, עלינו להגדיר תחילה את הערך IDENTITY_INSERT ON. לאחר מכן, בצע את פעולת ההוספה כדי להוסיף שורה חדשה לטבלה ולאחר מכן הגדר את הערך IDENTITY_INSERT OFF. ראה את סקריפט הקוד שלהלן:

 SET IDENTITY_INSERT person ON /*INSERT VALUE*/ INSERT INTO person(Fullname, Occupation, Gender, PersonID) VALUES('Mary Smith', 'Business Analyst', 'Female', 14); SET IDENTITY_INSERT person OFF SELECT * FROM person; 

IDENTITY_INSERT ON מאפשר למשתמשים להכניס נתונים לעמודות זהות, תוך כדי IDENTITY_INSERT כבוי מונע מהם להוסיף ערך לעמודה זו.

ביצוע סקריפט הקוד יציג את הפלט למטה שבו נוכל לראות שה-Person ID עם ערך 14 הוכנס בהצלחה.

פונקציית IDENTITY

SQL Server מספק כמה פונקציות זהות לעבודה עם עמודות IDENTITY בטבלה. פונקציות זהות אלה מפורטות להלן:

1. פונקציית @@IDENTITY
2. פונקציית SCOPE_IDENTITY()
3. פונקציה IDENT_CURRENT
4. פונקציית IDENTITY

בואו נסתכל על פונקציות IDENTITY עם כמה דוגמאות.

פונקציית @@IDENTITY

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

דוגמא

נניח שיש לנו ערך הזהות המקסימלי הנוכחי בטבלת האדם הוא 13. כעת נוסיף רשומה אחת באותה הפעלה שמגדילה את ערך הזהות באחד. לאחר מכן נשתמש בפונקציה @@IDENTITY כדי לקבל את ערך הזהות האחרון שנוצר באותה הפעלה.

הנה תסריט הקוד המלא:

 SELECT MAX(PersonID) AS maxidentity FROM person; INSERT INTO person(Fullname, Occupation, Gender) VALUES('Brian Lara', 'Cricket', 'Male'); SELECT @@IDENTITY; 

ביצוע הסקריפט יחזיר את הפלט הבא שבו נוכל לראות את ערך הזהות המקסימלי בשימוש הוא 14.

פונקציית SCOPE_IDENTITY()

ה-SCOPE_IDENTITY() הוא פונקציה המוגדרת על ידי מערכת ל להציג את ערך הזהות העדכני ביותר בטבלה בהיקף הנוכחי. היקף זה יכול להיות מודול, טריגר, פונקציה או פרוצדורה מאוחסנת. היא דומה לפונקציה @@IDENTITY(), אלא שלפונקציה זו יש היקף מוגבל בלבד. הפונקציה SCOPE_IDENTITY מחזירה NULL אם נבצע אותה לפני פעולת ה-insert שמייצרת ערך באותו scope.

דוגמא

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

 SELECT MAX(PersonID) AS maxid FROM person; INSERT INTO person(Fullname, Occupation, Gender) VALUES('Jennifer Winset', 'Actoress', 'Female'); SELECT SCOPE_IDENTITY(); SELECT @@IDENTITY; 

הפעלת הקוד תציג את אותו הערך בהפעלה הנוכחית ובהיקף דומה. ראה את תמונת הפלט למטה:

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

 CREATE TABLE employee_data ( emp_id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL, fullname VARCHAR(20) NULL ) GO CREATE TABLE department ( department_id INT IDENTITY(100, 5) PRIMARY KEY, department_name VARCHAR(20) NULL ); 

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

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

urfi javed

 CREATE TRIGGER Insert_Department ON employee_data FOR INSERT AS BEGIN INSERT INTO department VALUES ('IT') END; 

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

 INSERT INTO employee_data VALUES ('John Mathew'); 

ביצוע השאילתה יוסיף שורה אחת לטבלת העובד_נתונים וייצור ערך זהות באותה הפעלה. לאחר ביצוע שאילתת ההוספה בטבלת עובד_נתונים, היא קוראת אוטומטית לטריגר כדי להוסיף שורה אחת בטבלת המחלקה. ערך מקור הזהות הוא 1 עבור העובד_נתונים ו-100 עבור טבלת המחלקה.

לבסוף, אנו מבצעים את ההצהרות שלהלן המציגות את הפלט 100 עבור הפונקציה SELECT @@IDENTITY ו-1 עבור הפונקציה SCOPE_IDENTITY מכיוון שהם מחזירים ערך זהות באותו היקף בלבד.

 SELECT MAX(emp_id) FROM employee_data SELECT MAX(department_id) FROM department SELECT @@IDENTITY SELECT SCOPE_IDENTITY() 

הנה התוצאה:

IDENT_CURRENT() פונקציה

ה-IDENT_CURRENT הוא פונקציה המוגדרת על ידי המערכת ל להציג את הערך העדכני ביותר של IDENTITY נוצר עבור טבלה נתונה תחת כל חיבור. פונקציה זו אינה מתייחסת להיקף שאילתת SQL שיוצרת את ערך הזהות. פונקציה זו דורשת את שם הטבלה שעבורה אנו רוצים לקבל את ערך הזהות.

דוגמא

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

 1st Connection Window INSERT INTO person(Fullname, Occupation, Gender) VALUES('John Doe', 'Engineer', 'Male'); GO SELECT MAX(PersonID) AS maxid FROM person; 2nd Connection Window SELECT MAX(PersonID) AS maxid FROM person; GO SELECT IDENT_CURRENT('person') AS identity_value; 

ביצוע הקודים לעיל בשני חלונות שונים יציג את אותו ערך זהות.

פונקציית IDENTITY()

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

התחביר הבא ממחיש את השימוש בפונקציה זו ב-SQL Server:

 IDENTITY (data_type , seed , increment) AS column_name 

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

 SELECT IDENTITY(INT, 100, 2) AS NEW_ID, PersonID, Fullname, Occupation, Gender INTO person_info FROM person; 

ביצוע ההצהרה לעיל יחזיר את הודעת השגיאה הבאה:

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

 CREATE TABLE student_data ( roll_no INT PRIMARY KEY NOT NULL, fullname VARCHAR(20) NULL ) 

לאחר מכן, העתק טבלה זו באמצעות המשפט SELECT INTO כולל הפונקציה IDENTITY באופן הבא:

 SELECT IDENTITY(INT, 100, 1) AS student_id, roll_no, fullname INTO temp_data FROM student_data; 

לאחר ביצוע ההצהרה, נוכל לאמת אותה באמצעות ה sp_help פקודה המציגה מאפייני טבלה.

אתה יכול לראות את העמודה IDENTITY ב- מתפתה נכסים לפי התנאים שצוינו.

אם נשתמש בפונקציה זו עם המשפט SELECT, SQL Server יעביר את הודעת השגיאה הבאה:

Msg 177, Level 15, State 1, Line 2 ניתן להשתמש בפונקציה IDENTITY רק כאשר יש למשפט SELECT משפט INTO.

שימוש חוזר בערכי IDENTITY

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

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

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

 CREATE TABLE POSITION ( PositionID INT IDENTITY (1, 1) PRIMARY KEY, Position_name VARCHAR (255) NOT NULL ); CREATE TABLE person_position ( PersonID INT, PositionID INT, PRIMARY KEY (PersonID, PositionID), FOREIGN KEY (PersonID) REFERENCES person (PersonID), FOREIGN KEY (PositionID) REFERENCES POSITION (PositionID) ); 

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

 BEGIN TRANSACTION BEGIN TRY -- insert a new row into the person table INSERT INTO person (Fullname, Occupation, Gender) VALUES('Joan Smith', 'Manager', 'Male'); -- assign a position to a new person INSERT INTO person_position (PersonID, PositionID) VALUES(@@IDENTITY, 10); END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH IF @@TRANCOUNT > 0 COMMIT TRANSACTION; 

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

מכיוון שיש לנו ערך זהות מקסימלי בעמודה PersonID הוא 16, הצהרת ההוספה הראשונה צרכה את ערך הזהות 17, ואז העסקה הוחזרה לאחור. לכן, אם נוסיף את השורה הבאה בטבלת האדם, ערך הזהות הבא יהיה 18. בצע את המשפט הבא:

 INSERT INTO person(Fullname, Occupation, Gender) VALUES('Peter Drucker',' Writer', 'Female'); 

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

שתי עמודות IDENTITY בטבלה אחת

מבחינה טכנית, לא ניתן ליצור שתי עמודות זהות בטבלה אחת. אם נעשה זאת, SQL Server זורק שגיאה. ראה את השאילתה הבאה:

 CREATE TABLE TwoIdentityTable ( ID1 INT IDENTITY (10, 1) NOT NULL, ID2 INT IDENTITY (100, 1) NOT NULL ) 

כאשר אנו מבצעים את הקוד הזה, נראה את השגיאה הבאה:

עם זאת, אנו יכולים ליצור שתי עמודות זהות בטבלה אחת באמצעות העמודה המחושבת. השאילתה הבאה יוצרת טבלה עם עמודה מחושבת המשתמשת בעמודת הזהות המקורית ומקטינה אותה ב-1.

 CREATE TABLE TwoIdentityTable ( ID1 INT IDENTITY (10, 1) NOT NULL, SecondID AS 10000-ID1, Descriptions VARCHAR(60) ) 

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

 INSERT INTO TwoIdentityTable (Descriptions) VALUES ('Javatpoint provides best educational tutorials'), ('www.javatpoint.com') 

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

אנו יכולים לראות בתמונה כיצד עמודת SecondID פועלת כעמודת זהות שנייה, יורדת בעשר מהערך ההתחלתי של 9990.

תפיסות שגויות של העמודה IDENTITY של SQL Server

למשתמש DBA יש תפיסות שגויות רבות לגבי עמודות זהות של SQL Server. להלן רשימת התפיסות השגויות הנפוצות ביותר לגבי עמודות זהות שיראו:

העמודה IDENTITY היא ייחודית: על פי התיעוד הרשמי של SQL Server, מאפיין הזהות אינו יכול להבטיח שערך העמודה הוא ייחודי. עלינו להשתמש באינדקס PRIMARY KEY, UNIQUE או אינדקס UNIQUE כדי לאכוף את הייחודיות של העמודות.

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

העמודה IDENTITY לא יכולה ליצור אוטומטית ערכים קיימים: אין אפשרות שעמודת הזהות תיצור אוטומטית ערכים קיימים עד שמאפיין הזהות יוצג מחדש באמצעות הפקודה DBCC CHECKIDENT. זה מאפשר לנו להתאים את ערך ה-Seed (הערך ההתחלתי של השורה) של מאפיין הזהות. לאחר ביצוע פקודה זו, SQL Server לא יבדוק את הערכים החדשים שנוצרו שכבר קיימים בטבלה או לא.

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

שימוש בכלי שגוי כדי להחזיר ערכי זהות לאחר הוספה: זוהי גם תפיסה שגויה נפוצה לגבי חוסר מודעות להבדלים בין הפונקציות @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT ו-IDENTITY() כדי לקבל את ערך הזהות המוכנס ישירות מההצהרה שזה עתה ביצענו.

ההבדל בין SEQUENCE ל-IDENTITY

אנו משתמשים גם ב-SEQUENCE וגם ב-IDENTITY ליצירת מספרים אוטומטיים. עם זאת, יש לו כמה הבדלים, וההבדל העיקרי הוא שהזהות תלויה בטבלה, בעוד שהרצף לא. הבה נסכם את ההבדלים ביניהם לטופס הטבלה:

סיכום

מאמר זה יספק סקירה מלאה של נכס IDENTITY ב-SQL Server. כאן למדנו כיצד ומתי משתמשים בנכס זהות, הפונקציות השונות שלו, התפיסות השגויות, וכיצד הוא שונה מהרצף.