TechCodeview

IDENTITY-nøgleordet er en egenskab i SQL Server. Når en tabelkolonne er defineret med en identitetsegenskab, vil dens værdi være automatisk genereret trinvis værdi . Denne værdi oprettes automatisk af serveren. Derfor kan vi ikke manuelt indtaste en værdi i en identitetskolonne som bruger. Derfor, hvis vi markerer en kolonne som identitet, vil SQL Server udfylde den på en auto-increment måde.

Syntaks

Følgende er syntaksen til at illustrere brugen af ​​IDENTITY-egenskaber i SQL Server:

 IDENTITY[(seed, increment)] 

Ovenstående syntaksparametre er forklaret nedenfor:

Frø:Det angiver startværdien for den række, der er indlæst i tabellen. Som standard er dens værdi 1.Forøgelse:Det angiver den trinvise værdi, som lægges til identitetsværdien for den sidst indlæste række. Som standard er dens værdi 1.

Lad os forstå dette koncept gennem et simpelt eksempel.

Antag, at vi har en ' Studerende ' bord, og vi vil have Studiekort skal genereres automatisk. Vi har en begyndende studie-id af 10 og ønsker at øge den med 1 for hvert nyt ID. I dette scenarie skal følgende værdier defineres.

Frø: 10

Forøgelse: 1

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

BEMÆRK: Kun én identifikationskolonne er tilladt pr. tabel i SQL Server.

Eksempel på SQL Server IDENTITY

Lad os forstå, hvordan vi kan bruge identitetsegenskaben i tabellen. Identitetsegenskaben i en kolonne kan indstilles enten, når den nye tabel oprettes, eller efter den er blevet oprettet. Her vil vi se begge tilfælde med eksempler.

IDENTITY-ejendom med ny tabel

Følgende sætning vil oprette en ny tabel med egenskaben identitet i den angivne database:

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

Dernæst vil vi indsætte en ny række i denne tabel med en PRODUKTION klausul for at se det autogenererede person-id:

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

Udførelse af denne forespørgsel vil vise nedenstående output:

Denne udgang viser, at den første række er blevet indsat med værdien ti i PersonID kolonne som angivet i tabeldefinitionsidentitetskolonnen.

Lad os indsætte en anden række i person bord som nedenfor:

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

Denne forespørgsel returnerer følgende output:

Dette output viser, at den anden række er indsat med værdien 11 og den tredje række med værdien 12 i kolonnen PersonID.

IDENTITY-ejendom med eksisterende tabel

Vi vil forklare dette koncept ved først at slette ovenstående tabel og oprette dem uden identitetsegenskaber. Udfør nedenstående sætning for at droppe tabellen:

 DROP TABLE person; 

Dernæst vil vi oprette en tabel ved hjælp af nedenstående forespørgsel:

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

Hvis vi vil tilføje en ny kolonne med egenskaben identitet i en eksisterende tabel, skal vi bruge ALTER-kommandoen. Nedenstående forespørgsel tilføjer PersonID som en identitetskolonne i persontabellen:

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

Tilføjelse af værdi i identitetskolonnen eksplicit

Hvis vi tilføjer en ny række i ovenstående tabel ved eksplicit at angive identitetskolonnens værdi, vil SQL Server give en fejl. Se nedenstående forespørgsel:

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

Udførelse af denne forespørgsel vil gennem følgende fejl:

For eksplicit at indsætte værdien for identitetskolonnen skal vi først sætte værdien IDENTITY_INSERT TIL. Udfør derefter insert-operationen for at tilføje en ny række i tabellen, og sæt derefter IDENTITY_INSERT-værdien FRA. Se nedenstående kodescript:

 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 TIL lader brugere sætte data i identitetskolonner, mens IDENTITY_INSERT FRA forhindrer dem i at tilføje værdi til denne kolonne.

Udførelse af kodescriptet vil vise nedenstående output, hvor vi kan se, at PersonID med værdi 14 er indsat.

IDENTITET Funktion

SQL Server giver nogle identitetsfunktioner til at arbejde med IDENTITY-kolonnerne i en tabel. Disse identitetsfunktioner er angivet nedenfor:

1. @@IDENTITET Funktion
2. SCOPE_IDENTITY() Funktion
3. IDENT_CURRENT Funktion
4. IDENTITET Funktion

Lad os se på IDENTITET-funktionerne med nogle eksempler.

@@IDENTITET Funktion

@@IDENTITY er en systemdefineret funktion, der viser den sidste identitetsværdi (maksimal brugt identitetsværdi) oprettet i en tabel for IDENTITY-kolonnen i samme session. Denne funktionskolonne returnerer den identitetsværdi, der genereres af sætningen efter indsættelse af en ny post i en tabel. Det returnerer en NUL værdi, når vi udfører en forespørgsel, der ikke skaber IDENTITY-værdier. Det fungerer altid inden for rammerne af den aktuelle session. Det kan ikke bruges eksternt.

Eksempel

Antag, at vi har den aktuelle maksimale identitetsværdi i persontabellen er 13. Nu tilføjer vi én post i samme session, der øger identitetsværdien med én. Så vil vi bruge @@IDENTITY-funktionen til at få den sidste identitetsværdi oprettet i samme session.

Her er det fulde kodescript:

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

Udførelse af scriptet vil returnere følgende output, hvor vi kan se den maksimale brugte identitetsværdi er 14.

SCOPE_IDENTITY() Funktion

SCOPE_IDENTITY() er en systemdefineret funktion til vise den seneste identitetsværdi i en tabel under det nuværende omfang. Dette omfang kan være et modul, trigger, funktion eller en lagret procedure. Den ligner @@IDENTITY()-funktionen, bortset fra at denne funktion kun har et begrænset omfang. Funktionen SCOPE_IDENTITY returnerer NULL, hvis vi udfører den før indsættelsesoperationen, der genererer en værdi i samme omfang.

Eksempel

Nedenstående kode bruger både @@IDENTITY og SCOPE_IDENTITY() funktionen i samme session. Dette eksempel vil først vise den sidste identitetsværdi og derefter indsætte en række i tabellen. Dernæst udfører den begge identitetsfunktioner.

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

Udførelse af koden vil vise den samme værdi i den aktuelle session og lignende omfang. Se nedenstående outputbillede:

Nu vil vi se, hvordan begge funktioner er forskellige med et eksempel. Først vil vi oprette to navngivne tabeller medarbejderdata og afdeling ved hjælp af nedenstående udsagn:

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

Dernæst opretter vi en INSERT-trigger på tabellen medarbejder_data. Denne trigger aktiveres for at indsætte en række i afdelingstabellen, hver gang vi indsætter en række i tabellen medarbejderdata.

Nedenstående forespørgsel opretter en trigger til indsættelse af en standardværdi 'DET' i afdelingstabellen på hver indsæt-forespørgsel i medarbejderdata-tabellen:

skuespiller zeenat aman

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

Efter at have oprettet en trigger, skal vi indsætte én post i tabellen medarbejder_data og se output fra både @@IDENTITY og SCOPE_IDENTITY() funktioner.

 INSERT INTO employee_data VALUES ('John Mathew'); 

Udførelse af forespørgslen vil tilføje en række til tabellen medarbejder_data og generere en identitetsværdi i samme session. Når insert-forespørgslen er udført i tabellen medarbejder_data, kalder den automatisk en trigger for at tilføje en række i afdelingstabellen. Identitetsseedværdien er 1 for medarbejderdata og 100 for afdelingstabellen.

Til sidst udfører vi nedenstående sætninger, der viser output 100 for funktionen SELECT @@IDENTITY og 1 for funktionen SCOPE_IDENTITY, fordi de kun returnerer identitetsværdi i samme omfang.

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

Her er resultatet:

IDENT_CURRENT() Funktion

IDENT_CURRENT er en systemdefineret funktion til vise den seneste IDENTITY-værdi genereret for en given tabel under enhver forbindelse. Denne funktion tager ikke hensyn til omfanget af den SQL-forespørgsel, der skaber identitetsværdien. Denne funktion kræver det tabelnavn, som vi ønsker at få identitetsværdien for.

Eksempel

Vi kan forstå det ved først at åbne de to forbindelsesvinduer. Vi vil indsætte én post i det første vindue, der genererer identitetsværdien 15 i persontabellen. Dernæst kan vi verificere denne identitetsværdi i et andet forbindelsesvindue, hvor vi kan se det samme output. Her er den fulde kode:

 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; 

Udførelse af ovenstående koder i to forskellige vinduer vil vise den samme identitetsværdi.

IDENTITY() Funktion

Funktionen IDENTITY() er en systemdefineret funktion bruges til at indsætte en identitetskolonne i en ny tabel . Denne funktion er forskellig fra egenskaben IDENTITY, som vi bruger sammen med CREATE TABLE- og ALTER TABLE-sætningerne. Vi kan kun bruge denne funktion i en SELECT INTO-sætning, som bruges under overførsel af data fra en tabel til en anden.

Følgende syntaks illustrerer brugen af ​​denne funktion i SQL Server:

 IDENTITY (data_type , seed , increment) AS column_name 

Hvis en kildetabel har en IDENTITY-kolonne, arver tabellen, der er dannet med en SELECT INTO-kommando, den som standard. For eksempel , har vi tidligere oprettet en tabelperson med en identitetskolonne. Antag, at vi opretter en ny tabel, der arver persontabellen ved hjælp af SELECT INTO-sætningerne med IDENTITY()-funktionen. I så fald får vi en fejl, fordi kildetabellen allerede har en identitetskolonne. Se nedenstående forespørgsel:

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

Udførelse af ovenstående sætning vil returnere følgende fejlmeddelelse:

Lad os oprette en ny tabel uden identitetsegenskab ved hjælp af nedenstående erklæring:

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

Kopier derefter denne tabel ved hjælp af SELECT INTO-sætningen inklusive IDENTITY-funktionen som følger:

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

Når sætningen er eksekveret, kan vi bekræfte den ved hjælp af sp_hjælp kommando, der viser tabelegenskaber.

Du kan se IDENTITET-kolonnen i FRISTELSELIG egenskaber i henhold til de angivne betingelser.

Hvis vi bruger denne funktion med SELECT-sætningen, vil SQL Server gennem følgende fejlmeddelelse:

Meddelelse 177, niveau 15, tilstand 1, linje 2 IDENTITY-funktionen kan kun bruges, når SELECT-sætningen har en INTO-sætning.

Genbrug af IDENTITY-værdier

Vi kan ikke genbruge identitetsværdierne i SQL Server-tabellen. Når vi sletter en række fra identitetskolonnetabellen, vil der blive oprettet et hul i identitetskolonnen. SQL Server vil også skabe et hul, når vi indsætter en ny række i identitetskolonnen, og sætningen mislykkes eller rulles tilbage. Mellemrummet angiver, at identitetsværdierne er tabt og ikke kan genereres igen i IDENTITY-kolonnen.

Overvej nedenstående eksempel for at forstå det praktisk. Vi har allerede en persontabel, der indeholder følgende data:

Dernæst vil vi oprette yderligere to tabeller med navn 'position' , og ' person_position ' ved hjælp af følgende udsagn:

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

Dernæst forsøger vi at indsætte en ny post i person-tabellen og tildele dem en position ved at tilføje en ny række i person_position-tabellen. Vi vil gøre dette ved at bruge transaktionserklæringen som nedenfor:

 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; 

Ovenstående transaktionskodescript udfører den første insert-sætning med succes. Men den anden erklæring mislykkedes, da der ikke var nogen position med id ti i positionstabellen. Derfor blev hele transaktionen rullet tilbage.

Da vi har den maksimale identitetsværdi i PersonID-kolonnen er 16, forbrugte den første insert-sætning identitetsværdien 17, og derefter blev transaktionen rullet tilbage. Derfor, hvis vi indsætter den næste række i Person-tabellen, vil den næste identitetsværdi være 18. Udfør nedenstående sætning:

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

Efter at have tjekket persontabellen igen, ser vi, at den nyligt tilføjede post indeholder identitetsværdi 18.

To IDENTITY-kolonner i en enkelt tabel

Teknisk set er det ikke muligt at oprette to identitetskolonner i en enkelt tabel. Hvis vi gør dette, giver SQL Server en fejl. Se følgende forespørgsel:

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

Når vi udfører denne kode, vil vi se følgende fejl:

Vi kan dog oprette to identitetskolonner i en enkelt tabel ved at bruge den beregnede kolonne. Følgende forespørgsel opretter en tabel med en beregnet kolonne, der bruger den oprindelige identitetskolonne og reducerer den med 1.

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

Dernæst vil vi tilføje nogle data til denne tabel ved hjælp af nedenstående kommando:

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

Til sidst kontrollerer vi tabeldataene ved hjælp af SELECT-sætningen. Det returnerer følgende output:

Vi kan se på billedet, hvordan SecondID-kolonnen fungerer som en anden identitetskolonne, der falder med ti fra startværdien på 9990.

SQL Servers IDENTITY-kolonne misforståelser

DBA-brugeren har mange misforståelser vedrørende SQL Server-identitetskolonner. Følgende er listen over de mest almindelige misforståelser vedrørende identitetskolonner, der ville blive set:

IDENTITY-kolonnen er UNIK: Ifølge SQL Servers officielle dokumentation kan identitetsegenskaben ikke garantere, at kolonneværdien er unik. Vi skal bruge en PRIMÆR NØGLE, UNIK begrænsning eller UNIK indeks for at gennemtvinge entydighed i kolonnen.

IDENTITY-kolonnen genererer fortløbende tal: Officiel dokumentation angiver klart, at de tildelte værdier i identitetskolonnen kan gå tabt ved en databasefejl eller servergenstart. Det kan forårsage huller i identitetsværdien under indsættelse. Gabet kan også skabes, når vi sletter værdien fra tabellen, eller insert-sætningen rulles tilbage. De værdier, der genererer huller, kan ikke bruges yderligere.

IDENTITY-kolonnen kan ikke automatisk generere eksisterende værdier: Det er ikke muligt for identitetskolonnen at automatisk generere eksisterende værdier, før identitetsegenskaben er genset ved at bruge kommandoen DBCC CHECKIDENT. Det giver os mulighed for at justere startværdien (startværdien af ​​rækken) af identitetsegenskaben. Efter at have udført denne kommando, vil SQL Server ikke kontrollere de nyoprettede værdier, der allerede er til stede i tabellen eller ej.

IDENTITY-kolonnen som PRIMÆR NØGLE er nok til at identificere rækken: Hvis en primærnøgle indeholder identitetskolonnen i tabellen uden andre unikke begrænsninger, kan kolonnen gemme duplikerede værdier og forhindre kolonneunik. Som vi ved, kan den primære nøgle ikke gemme duplikerede værdier, men identitetskolonnen kan gemme dubletter; Det anbefales ikke at bruge den primære nøgle og identitetsegenskaben i samme kolonne.

Brug af det forkerte værktøj til at få identitetsværdier tilbage efter en indsættelse: Det er også en almindelig misforståelse om ubevidsthed om forskellene mellem funktionerne @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT og IDENTITY() at få identitetsværdien direkte indsat fra den sætning, vi lige har udført.

Forskellen mellem SEQUENCE og IDENTITY

Vi bruger både SEQUENCE og IDENTITY til at generere autonumre. Det har dog nogle forskelle, og hovedforskellen er, at identitet er tabelafhængig, hvorimod sekvens ikke er det. Lad os opsummere deres forskelle i tabelformen:

Konklusion

Denne artikel vil give et komplet overblik over IDENTITY-egenskaber i SQL Server. Her har vi lært, hvordan og hvornår identitetsegenskaber bruges, dens forskellige funktioner, misforståelser, og hvordan den er forskellig fra sekvensen.