TechCodeview

Cuvântul cheie IDENTITY este o proprietate în SQL Server. Când o coloană de tabel este definită cu o proprietate de identitate, valoarea acesteia va fi o valoare incrementală generată automat . Această valoare este creată automat de server. Prin urmare, nu putem introduce manual o valoare într-o coloană de identitate ca utilizator. Prin urmare, dacă marcăm o coloană ca identitate, SQL Server o va popula într-o manieră de incrementare automată.

Sintaxă

Următoarele sunt sintaxa pentru a ilustra utilizarea proprietății IDENTITY în SQL Server:

 IDENTITY[(seed, increment)] 

Parametrii de sintaxă de mai sus sunt explicați mai jos:

Sămânță:Indică valoarea de pornire a rândului încărcat în tabel. În mod implicit, valoarea sa este 1.Creştere:Indică valoarea incrementală, care se adaugă la valoarea de identitate a ultimului rând încărcat. În mod implicit, valoarea sa este 1.

Să înțelegem acest concept printr-un exemplu simplu.

Să presupunem că avem un ' Student ' masă și vrem Carnet de student să fie generate automat. Noi avem o ID de student începător al 10 și doriți să o măriți cu 1 cu fiecare ID nou. În acest scenariu, trebuie definite următoarele valori.

Sămânță: 10

Creştere: 1

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

NOTĂ: Este permisă o singură coloană de identificare per tabel în SQL Server.

Exemplu IDENTITATE SQL Server

Să înțelegem cum putem folosi proprietatea identity din tabel. Proprietatea de identitate dintr-o coloană poate fi setată fie atunci când noul tabel este creat, fie după ce acesta a fost creat. Aici vom vedea ambele cazuri cu exemple.

Proprietate IDENTITY cu tabel nou

Următoarea instrucțiune va crea un nou tabel cu proprietatea identity în baza de date specificată:

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

În continuare, vom insera un nou rând în acest tabel cu un IEȘIRE clauză pentru a vedea ID-ul de persoană generat automat:

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

Executarea acestei interogări va afișa rezultatul de mai jos:

Această ieșire arată că primul rând a fost inserat cu valoarea zece în PersonID coloană așa cum este specificat în coloana de identitate a definiției tabelului.

Să introducem un alt rând în masa de persoane ca mai jos:

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

Această interogare va returna următoarea ieșire:

Această ieșire arată că al doilea rând a fost inserat cu valoarea 11 și al treilea rând cu valoarea 12 în coloana PersonID.

Proprietate IDENTITY cu tabel existent

Vom explica acest concept ștergând mai întâi tabelul de mai sus și creându-le fără proprietatea de identitate. Executați instrucțiunea de mai jos pentru a arunca tabelul:

 DROP TABLE person; 

În continuare, vom crea un tabel utilizând interogarea de mai jos:

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

Dacă vrem să adăugăm o nouă coloană cu proprietatea identity într-un tabel existent, trebuie să folosim comanda ALTER. Interogarea de mai jos va adăuga PersonID ca coloană de identitate în tabelul de persoane:

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

Adăugarea de valoare în coloana de identitate în mod explicit

Dacă adăugăm un nou rând în tabelul de mai sus specificând în mod explicit valoarea coloanei de identitate, SQL Server va genera o eroare. Vedeți interogarea de mai jos:

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

Executarea acestei interogări va apărea prin următoarea eroare:

Pentru a introduce valoarea coloanei de identitate în mod explicit, trebuie să setăm mai întâi valoarea IDENTITY_INSERT ON. Apoi, executați operația de inserare pentru a adăuga un nou rând în tabel și apoi setați valoarea IDENTITY_INSERT OFF. Vedeți scriptul de cod de mai jos:

 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 permite utilizatorilor să pună date în coloane de identitate, în timp ce IDENTITY_INSERT OFF le împiedică să adauge valoare acestei coloane.

Executarea scriptului de cod va afișa rezultatul de mai jos, unde putem vedea că PersonID cu valoarea 14 este inserat cu succes.

Funcția IDENTITATE

SQL Server oferă câteva funcții de identitate pentru lucrul cu coloanele IDENTITY dintr-un tabel. Aceste funcții de identitate sunt enumerate mai jos:

1. Funcția @@IDENTITY
2. Funcția SCOPE_IDENTITY().
3. IDENT_CURRENT Funcția
4. Funcția IDENTITATE

Să aruncăm o privire la funcțiile IDENTITATE cu câteva exemple.

Funcția @@IDENTITY

@@IDENTITY este o funcție definită de sistem care afișează ultima valoare de identitate (valoarea maximă de identitate utilizată) creată într-un tabel pentru coloana IDENTITATE din aceeași sesiune. Această coloană a funcției returnează valoarea identității generată de instrucțiune după inserarea unei noi intrări într-un tabel. Se întoarce a NUL valoare atunci când executăm o interogare care nu creează valori IDENTITY. Funcționează întotdeauna în cadrul sesiunii curente. Nu poate fi folosit de la distanță.

Exemplu

Să presupunem că avem valoarea maximă curentă de identitate în tabelul de persoane este 13. Acum vom adăuga o înregistrare în aceeași sesiune care crește valoarea identității cu una. Apoi vom folosi funcția @@IDENTITY pentru a obține ultima valoare de identitate creată în aceeași sesiune.

Iată scriptul complet al codului:

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

Executarea scriptului va returna următoarea ieșire, unde putem vedea că valoarea maximă a identității utilizate este 14.

Funcția SCOPE_IDENTITY().

SCOPE_IDENTITY() este o funcție definită de sistem pentru afișează cea mai recentă valoare de identitate într-un tabel sub domeniul actual. Acest domeniu poate fi un modul, un declanșator, o funcție sau o procedură stocată. Este similară cu funcția @@IDENTITY(), cu excepția faptului că această funcție are doar un domeniu limitat. Funcția SCOPE_IDENTITY returnează NULL dacă o executăm înainte de operația de inserare care generează o valoare în același domeniu.

Exemplu

Codul de mai jos folosește atât funcția @@IDENTITY, cât și SCOPE_IDENTITY() în aceeași sesiune. Acest exemplu va afișa mai întâi ultima valoare de identitate, apoi va introduce un rând în tabel. Apoi, execută ambele funcții de identitate.

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

Executarea codului va afișa aceeași valoare în sesiunea curentă și un domeniu similar. Vedeți imaginea de ieșire de mai jos:

Acum vom vedea cum ambele funcții sunt diferite cu un exemplu. Mai întâi, vom crea două tabele numite date_angajați și departament folosind declarația de mai jos:

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

Apoi, creăm un declanșator INSERT pe tabelul employee_data. Acest declanșator este invocat pentru a insera un rând în tabelul departamentului ori de câte ori inserăm un rând în tabelul employee_data.

Interogarea de mai jos creează un declanșator pentru inserarea unei valori implicite 'ACEASTA' în tabelul departamentului pentru fiecare interogare de inserare în tabelul date_employé:

lingurita vs lingura

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

După crearea unui declanșator, vom insera o înregistrare în tabelul employee_data și vom vedea rezultatul ambelor funcții @@IDENTITY și SCOPE_IDENTITY().

 INSERT INTO employee_data VALUES ('John Mathew'); 

Executarea interogării va adăuga un rând în tabelul employee_data și va genera o valoare de identitate în aceeași sesiune. Odată ce interogarea de inserare este executată în tabelul employee_data, apelează automat un declanșator pentru a adăuga un rând în tabelul departamentului. Valoarea inițială a identității este 1 pentru date_employed și 100 pentru tabelul departamentului.

În cele din urmă, executăm instrucțiunile de mai jos care afișează ieșirea 100 pentru funcția SELECT @@IDENTITY și 1 pentru funcția SCOPE_IDENTITY deoarece returnează valoarea identității numai în același domeniu.

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

Iată rezultatul:

IDENT_CURRENT() Funcția

IDENT_CURRENT este o funcție definită de sistem pentru afișează cea mai recentă valoare IDENTITY generat pentru un tabel dat sub orice conexiune. Această funcție nu ia în considerare domeniul de aplicare al interogării SQL care creează valoarea identității. Această funcție necesită numele tabelului pentru care dorim să obținem valoarea identității.

Exemplu

O putem înțelege deschizând mai întâi cele două ferestre de conexiune. Vom insera o înregistrare în prima fereastră care generează valoarea de identitate 15 în tabelul de persoane. Apoi, putem verifica această valoare de identitate într-o altă fereastră de conexiune unde putem vedea aceeași ieșire. Iată codul complet:

 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; 

Executarea codurilor de mai sus în două ferestre diferite va afișa aceeași valoare de identitate.

Funcția IDENTITY().

Funcția IDENTITY() este o funcție definită de sistem folosit pentru inserarea unei coloane de identitate într-un tabel nou . Această funcție este diferită de proprietatea IDENTITY pe care o folosim cu instrucțiunile CREATE TABLE și ALTER TABLE. Putem folosi această funcție doar într-o instrucțiune SELECT INTO, care este utilizată în timpul transferului de date dintr-un tabel în altul.

Următoarea sintaxă ilustrează utilizarea acestei funcții în SQL Server:

 IDENTITY (data_type , seed , increment) AS column_name 

Dacă un tabel sursă are o coloană IDENTITY, tabelul format cu o comandă SELECT INTO o moștenește implicit. De exemplu , am creat anterior o persoană de tabel cu o coloană de identitate. Să presupunem că creăm un tabel nou care moștenește tabelul de persoane folosind instrucțiunile SELECT INTO cu funcția IDENTITY(). În acest caz, vom primi o eroare deoarece tabelul sursă are deja o coloană de identitate. Vedeți interogarea de mai jos:

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

Executarea instrucțiunii de mai sus va returna următorul mesaj de eroare:

Să creăm un tabel nou fără proprietatea de identitate folosind instrucțiunea de mai jos:

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

Apoi, copiați acest tabel utilizând instrucțiunea SELECT INTO, inclusiv funcția IDENTITY, după cum urmează:

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

Odată ce instrucțiunea este executată, o putem verifica folosind sp_help comandă care afișează proprietățile tabelului.

Puteți vedea coloana IDENTITATE în Ispititor proprietăți conform condițiilor specificate.

Dacă folosim această funcție cu instrucțiunea SELECT, SQL Server va trece prin următorul mesaj de eroare:

Mesajul 177, Nivel 15, Stare 1, Linie 2 Funcția IDENTITY poate fi utilizată numai atunci când instrucțiunea SELECT are o clauză INTO.

Reutilizarea valorilor IDENTITY

Nu putem reutiliza valorile de identitate din tabelul SQL Server. Când ștergem orice rând din tabelul coloanei de identitate, va fi creat un decalaj în coloana de identitate. De asemenea, SQL Server va crea un decalaj atunci când introducem un nou rând în coloana de identitate, iar instrucțiunea este eșuată sau este anulată. Decalajul indică faptul că valorile de identitate sunt pierdute și nu pot fi generate din nou în coloana IDENTITATE.

Luați în considerare exemplul de mai jos pentru a-l înțelege practic. Avem deja un tabel de persoane care conține următoarele date:

În continuare, vom crea încă două tabele numite 'poziţie' , și ' persoană_poziție ' folosind următoarea afirmație:

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

În continuare, încercăm să inserăm o nouă înregistrare în tabelul person și să le atribuim o poziție adăugând un nou rând în tabelul person_position. Vom face acest lucru utilizând extrasul de tranzacție după cum urmează:

 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; 

Scriptul de cod de tranzacție de mai sus execută cu succes prima instrucțiune de inserare. Dar a doua declarație a eșuat, deoarece nu a existat nicio poziție cu id zece în tabelul de poziții. Prin urmare, întreaga tranzacție a fost anulată.

Deoarece valoarea maximă a identităţii în coloana PersonID este 16, prima instrucţiune de inserare a consumat valoarea de identitate 17, iar apoi tranzacţia a fost anulată. Prin urmare, dacă inserăm următorul rând în tabelul Persoană, următoarea valoare de identitate va fi 18. Executați instrucțiunea de mai jos:

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

După ce verificăm din nou tabelul de persoane, vedem că înregistrarea nou adăugată conține valoarea de identitate 18.

Două coloane IDENTITY într-un singur tabel

Din punct de vedere tehnic, nu este posibil să se creeze două coloane de identitate într-un singur tabel. Dacă facem acest lucru, SQL Server aruncă o eroare. Vedeți următoarea interogare:

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

Când executăm acest cod, vom vedea următoarea eroare:

Cu toate acestea, putem crea două coloane de identitate într-un singur tabel utilizând coloana calculată. Următoarea interogare creează un tabel cu o coloană calculată care utilizează coloana de identitate originală și o descrește cu 1.

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

În continuare, vom adăuga câteva date în acest tabel utilizând comanda de mai jos:

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

În cele din urmă, verificăm datele din tabel utilizând instrucțiunea SELECT. Returnează următoarea ieșire:

Putem vedea în imagine cum coloana SecondID acționează ca o a doua coloană de identitate, scăzând cu zece de la valoarea inițială de 9990.

Concepții greșite ale coloanei IDENTITATE din SQL Server

Utilizatorul DBA are multe concepții greșite cu privire la coloanele de identitate SQL Server. Următoarele sunt lista celor mai frecvente concepții greșite cu privire la coloanele de identitate care ar fi văzute:

Coloana IDENTITY este UNICA: Conform documentației oficiale a SQL Server, proprietatea de identitate nu poate garanta că valoarea coloanei este unică. Trebuie să folosim o CHEIE PRIMARĂ, o constrângere UNIQUE sau un index UNIC pentru a impune unicitatea coloanei.

Coloana IDENTITY generează numere consecutive: Documentația oficială afirmă în mod clar că valorile atribuite în coloana de identitate se pot pierde la o eroare a bazei de date sau la repornirea serverului. Poate cauza goluri în valoarea identităţii în timpul inserării. Decalajul poate fi creat și atunci când ștergem valoarea din tabel sau instrucțiunea de inserare este derulată înapoi. Valorile care generează goluri nu pot fi utilizate în continuare.

Coloana IDENTITY nu poate genera automat valori existente: Nu este posibil ca coloana de identitate să genereze automat valorile existente până când proprietatea de identitate este reseedată prin utilizarea comenzii DBCC CHECKIDENT. Ne permite să ajustam valoarea semințelor (valoarea de pornire a rândului) a proprietății de identitate. După efectuarea acestei comenzi, SQL Server nu va verifica valorile nou create deja prezente în tabel sau nu.

Coloana IDENTITY ca CHEIE PRIMARĂ este suficientă pentru a identifica rândul: Dacă o cheie primară conține coloana de identitate din tabel fără alte constrângeri unice, coloana poate stoca valori duplicate și poate preveni unicitatea coloanei. După cum știm, cheia primară nu poate stoca valori duplicate, dar coloana de identitate poate stoca duplicate; se recomandă să nu folosiți cheia primară și proprietatea de identitate pe aceeași coloană.

Folosind instrumentul greșit pentru a recupera valorile de identitate după o inserare: Este, de asemenea, o concepție greșită comună despre neconștientizarea diferențelor dintre funcțiile @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT și IDENTITY() pentru a obține valoarea identității inserată direct din instrucțiunea pe care tocmai am executat-o.

Diferența dintre SECVENȚĂ și IDENTITATE

Folosim atât SEQUENCE, cât și IDENTITY pentru a genera numere automate. Cu toate acestea, are unele diferențe, iar principala diferență este că identitatea este dependentă de tabel, în timp ce secvența nu este. Să rezumam diferențele lor în formă tabelară:

Concluzie

Acest articol va oferi o prezentare completă a proprietății IDENTITY din SQL Server. Aici am învățat cum și când este utilizată proprietatea de identitate, diferitele sale funcții, concepții greșite și cum este diferită de secvență.