TechCodeview

Ключовата дума IDENTITY е свойство в SQL Server. Когато колона на таблица е дефинирана със свойство за идентичност, нейната стойност ще бъде автоматично генерирана нарастваща стойност . Тази стойност се създава автоматично от сървъра. Следователно не можем ръчно да въведем стойност в колона за самоличност като потребител. Следователно, ако маркираме колона като идентичност, SQL Server ще я попълни по начин на автоматично нарастване.

Синтаксис

По-долу е синтаксисът за илюстриране на използването на свойството IDENTITY в SQL Server:

 IDENTITY[(seed, increment)] 

Горните синтактични параметри са обяснени по-долу:

семена:Той показва началната стойност на реда, зареден в таблицата. По подразбиране стойността му е 1.Увеличение:Той показва нарастващата стойност, която се добавя към стойността на идентичността на последния зареден ред. По подразбиране стойността му е 1.

Нека разберем тази концепция чрез прост пример.

Да предположим, че имаме Студент ' маса, и ние искаме ID на студент да се генерира автоматично. Ние имаме начална студентска книжка на 10 и искате да го увеличите с 1 с всеки нов ID. В този сценарий трябва да се дефинират следните стойности.

семена: 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'); 

Изпълнението на тази заявка ще покаже изхода по-долу:

Този изход показва, че първият ред е бил вмъкнат със стойност десет в PersonID колона, както е посочено в колоната за идентичност на дефиницията на таблицата.

Нека вмъкнем още един ред в лице маса както по-долу:

 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 ); 

Ако искаме да добавим нова колона със свойството identity в съществуваща таблица, трябва да използваме командата 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 на ИЗКЛ. Вижте кодовия скрипт по-долу:

 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 ВКЛ позволява на потребителите да поставят данни в колони за самоличност, докато IDENTITY_INSERT ИЗКЛ им пречи да добавят стойност към тази колона.

Изпълнението на кодовия скрипт ще покаже резултата по-долу, където можем да видим, че PersonID със стойност 14 е вмъкнат успешно.

Функция ИДЕНТИЧНОСТ

SQL Server предоставя някои функции за идентичност за работа с колоните IDENTITY в таблица. Тези функции за идентичност са изброени по-долу:

1. Функция @@IDENTITY
2. Функция SCOPE_IDENTITY().
3. Функция IDENT_CURRENT
4. Функция ИДЕНТИЧНОСТ

Нека да разгледаме функциите IDENTITY с някои примери.

Функция @@IDENTITY

@@IDENTITY е дефинирана от системата функция, която показва последната стойност на идентичност (максимална използвана стойност на идентичност), създадена в таблица за колоната IDENTITY в същата сесия. Тази функционална колона връща стойността на самоличността, генерирана от оператора след вмъкване на нов запис в таблица. Връща a НУЛА стойност, когато изпълняваме заявка, която не създава стойности на 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, ако я изпълним преди операцията за вмъкване, която генерира стойност в същия обхват.

Пример

Кодът по-долу използва функциите @@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 в таблицата employee_data. Този тригер се извиква, за да вмъкне ред в таблицата на отдела, когато вмъкнем ред в таблицата с данни за служителите.

Заявката по-долу създава тригер за вмъкване на стойност по подразбиране 'ТО' в таблицата на отдела при всяка заявка за вмъкване в таблицата с данни за служителите:

np.sum

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

След като създадем тригер, ще вмъкнем един запис в таблицата employee_data и ще видим резултата както от функциите @@IDENTITY, така и от SCOPE_IDENTITY().

 INSERT INTO employee_data VALUES ('John Mathew'); 

Изпълнението на заявката ще добави един ред в таблицата employee_data и ще генерира стойност за самоличност в същата сесия. След като заявката за вмъкване се изпълни в таблицата employee_data, тя автоматично извиква тригер за добавяне на един ред в таблицата на отдела. Изходната стойност на самоличността е 1 за Emploee_data и 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 команда, която показва свойствата на таблицата.

Можете да видите колоната ИДЕНТИЧНОСТ в ИЗКУШИТЕЛЕН имоти според посочените условия.

Ако използваме тази функция с командата SELECT, SQL Server ще покаже следното съобщение за грешка:

Msg 177, ниво 15, състояние 1, ред 2 Функцията IDENTITY може да се използва само когато изразът SELECT има клауза INTO.

Повторно използване на стойности на IDENTITY

Не можем да използваме повторно стойностите за самоличност в таблицата на SQL Server. Когато изтрием който и да е ред от таблицата с колони за самоличност, в колоната за идентичност ще се създаде празнина. Освен това SQL Server ще създаде празнина, когато вмъкнем нов ред в колоната за самоличност и операторът е неуспешен или върнат назад. Пропускът показва, че стойностите на самоличността са загубени и не могат да бъдат генерирани отново в колоната IDENTITY.

Разгледайте примера по-долу, за да го разберете на практика. Вече имаме таблица с хора, съдържаща следните данни:

След това ще създадем още две таблици с име 'позиция' , и ' person_position ' използвайки следния израз:

 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 и да им присвоим позиция, като добавим нов ред в таблицата 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; 

Горният скрипт с код на транзакция изпълнява успешно първия израз за вмъкване. Но вторият оператор се провали, тъй като нямаше позиция с идентификатор десет в таблицата с позиции. Следователно цялата транзакция беше отменена.

Тъй като имаме максимална стойност на идентичност в колоната PersonID е 16, първият оператор за вмъкване е изразходвал стойността на идентичност 17 и след това транзакцията е върната назад. Следователно, ако вмъкнем следващия ред в таблицата Person, следващата стойност на идентичност ще бъде 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 свойството identity не може да гарантира, че стойността на колоната е уникална. Трябва да използваме PRIMARY KEY, UNIQUE ограничение или UNIQUE индекс, за да наложим уникалността на колоната.

Колоната IDENTITY генерира последователни числа: Официалната документация ясно посочва, че присвоените стойности в колоната за идентичност могат да бъдат загубени при повреда на базата данни или рестартиране на сървъра. Това може да причини пропуски в стойността на самоличността по време на вмъкване. Пропускът може да се създаде и когато изтрием стойността от таблицата или операторът за вмъкване се върне назад. Стойностите, които генерират пропуски, не могат да се използват по-нататък.

Колоната IDENTITY не може автоматично да генерира съществуващи стойности: Не е възможно колоната за самоличност да генерира автоматично съществуващи стойности, докато свойството за идентичност не бъде повторно заредено с помощта на командата DBCC CHECKIDENT. Позволява ни да коригираме началната стойност (началната стойност на реда) на свойството за идентичност. След изпълнение на тази команда SQL Server няма да проверява дали новосъздадените стойности вече присъстват в таблицата или не.

Колоната ИДЕНТИЧНОСТ като ПЪРВИЧЕН КЛЮЧ е достатъчна за идентифициране на реда: Ако първичният ключ съдържа колоната за самоличност в таблицата без никакви други уникални ограничения, колоната може да съхранява дублирани стойности и да предотврати уникалността на колоната. Както знаем, първичният ключ не може да съхранява дублирани стойности, но колоната за самоличност може да съхранява дубликати; препоръчително е да не използвате първичния ключ и свойството за самоличност в една и съща колона.

Използване на грешен инструмент за връщане на стойности на идентичност след вмъкване: Също така е често срещано погрешно схващане за непознаване на разликите между функциите @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT и IDENTITY() за получаване на стойността на самоличността, директно вмъкната от израза, който току-що сме изпълнили.

Разлика между ПОСЛЕДОВАТЕЛНОСТ и ИДЕНТИЧНОСТ

Ние използваме SEQUENCE и IDENTITY за генериране на автоматични номера. Той обаче има някои разлики и основната разлика е, че идентичността зависи от таблицата, докато последователността не е. Нека обобщим разликите им в таблична форма:

Заключение

Тази статия ще даде пълен преглед на свойството IDENTITY в SQL Server. Тук научихме как и кога се използва свойството за идентичност, неговите различни функции, погрешни схващания и как се различава от последователността.