TechCodeview

Het IDENTITY-sleutelwoord is een eigenschap in SQL Server. Wanneer een tabelkolom wordt gedefinieerd met een identiteitseigenschap, zal de waarde ervan een automatisch gegenereerde incrementele waarde zijn . Deze waarde wordt automatisch door de server aangemaakt. Daarom kunnen we als gebruiker niet handmatig een waarde in een identiteitskolom invoeren. Als we een kolom dus als identiteit markeren, zal SQL Server deze automatisch ophogen.

Syntaxis

Hieronder vindt u de syntaxis om het gebruik van de eigenschap IDENTITY in SQL Server te illustreren:

 IDENTITY[(seed, increment)] 

De bovenstaande syntaxisparameters worden hieronder uitgelegd:

Zaad:Het geeft de startwaarde aan van de rij die in de tabel is geladen. Standaard is de waarde 1.Verhoging:Het geeft de incrementele waarde aan, die wordt opgeteld bij de identiteitswaarde van de laatst geladen rij. Standaard is de waarde 1.

Laten we dit concept begrijpen aan de hand van een eenvoudig voorbeeld.

Stel dat we een ' Student ' tafel, en we willen StudentID automatisch gegenereerd worden. We hebben een beginnende studentenkaart van 10 en wil deze bij elke nieuwe ID met 1 verhogen. In dit scenario moeten de volgende waarden worden gedefinieerd.

Zaad: 10

Verhoging: 1

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

OPMERKING: Er is slechts één identificatiekolom per tabel toegestaan ​​in SQL Server.

SQL Server IDENTITEIT Voorbeeld

Laten we begrijpen hoe we de identiteitseigenschap in de tabel kunnen gebruiken. De identiteitseigenschap in een kolom kan worden ingesteld wanneer de nieuwe tabel wordt gemaakt of nadat deze is gemaakt. Hier zullen we beide gevallen met voorbeelden zien.

IDENTITY eigendom met nieuwe tafel

Met de volgende instructie wordt een nieuwe tabel met de identiteitseigenschap in de opgegeven database gemaakt:

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

Vervolgens voegen we een nieuwe rij in deze tabel in met een UITGANG clausule om de automatisch gegenereerde persoons-ID te zien:

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

Als u deze query uitvoert, wordt de onderstaande uitvoer weergegeven:

Deze uitvoer laat zien dat de eerste rij is ingevoegd met de waarde tien in de PersoonID kolom zoals opgegeven in de identiteitskolom van de tabeldefinitie.

Laten we nog een rij invoegen in de persoon tafel zoals hieronder:

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

Deze query retourneert de volgende uitvoer:

Deze uitvoer laat zien dat de tweede rij is ingevoegd met de waarde 11 en de derde rij met de waarde 12 in de kolom PersonID.

IDENTITY eigendom met bestaande tafel

We zullen dit concept uitleggen door eerst de bovenstaande tabel te verwijderen en deze zonder identiteitseigenschap aan te maken. Voer de onderstaande instructie uit om de tabel te verwijderen:

 DROP TABLE person; 

Vervolgens maken we een tabel met behulp van de onderstaande query:

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

Als we een nieuwe kolom met de identiteitseigenschap aan een bestaande tabel willen toevoegen, moeten we de opdracht ALTER gebruiken. Met de onderstaande zoekopdracht wordt de PersonID toegevoegd als een identiteitskolom in de persoonstabel:

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

Expliciet waarde toevoegen aan de identiteitskolom

Als we een nieuwe rij aan de bovenstaande tabel toevoegen door de waarde van de identiteitskolom expliciet op te geven, genereert SQL Server een fout. Zie de onderstaande vraag:

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

Het uitvoeren van deze query zal de volgende fout veroorzaken:

Om de identiteitskolomwaarde expliciet in te voegen, moeten we eerst de IDENTITY_INSERT-waarde AAN zetten. Voer vervolgens de invoegbewerking uit om een ​​nieuwe rij aan de tabel toe te voegen en stel vervolgens de IDENTITY_INSERT-waarde in op OFF. Zie het onderstaande codescript:

 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 AAN laat gebruikers gegevens in identiteitskolommen plaatsen, terwijl IDENTITY_INSERT UIT verhindert dat ze waarde toevoegen aan deze kolom.

Als u het codescript uitvoert, wordt de onderstaande uitvoer weergegeven, waarin we kunnen zien dat de PersonID met waarde 14 met succes is ingevoegd.

IDENTITEIT Functie

SQL Server biedt enkele identiteitsfuncties voor het werken met de IDENTITY-kolommen in een tabel. Deze identiteitsfuncties worden hieronder opgesomd:

1. @@IDENTITY-functie
2. SCOPE_IDENTITY() Functie
3. IDENT_CURRENT Functie
4. IDENTITEIT Functie

Laten we de IDENTITY-functies eens bekijken met enkele voorbeelden.

@@IDENTITY-functie

De @@IDENTITY is een door het systeem gedefinieerde functie die geeft de laatste identiteitswaarde weer (maximaal gebruikte identiteitswaarde) gemaakt in een tabel voor de IDENTITY-kolom in dezelfde sessie. Deze functiekolom retourneert de identiteitswaarde die door de instructie is gegenereerd na het invoegen van een nieuw item in een tabel. Het retourneert een NUL waarde wanneer we een query uitvoeren die geen IDENTITY-waarden creëert. Het werkt altijd binnen het bereik van de huidige sessie. Het kan niet op afstand worden gebruikt.

Voorbeeld

Stel dat de huidige maximale identiteitswaarde in de persoonstabel 13 is. Nu zullen we in dezelfde sessie één record toevoegen dat de identiteitswaarde met één verhoogt. Vervolgens gebruiken we de functie @@IDENTITY om de laatste identiteitswaarde op te halen die in dezelfde sessie is gemaakt.

Hier is het volledige codescript:

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

Als u het script uitvoert, wordt de volgende uitvoer geretourneerd, waarin we kunnen zien dat de maximaal gebruikte identiteitswaarde 14 is.

SCOPE_IDENTITY() Functie

De SCOPE_IDENTITY() is een door het systeem gedefinieerde functie de meest recente identiteitswaarde weergeven in een tabel onder het huidige bereik. Dit bereik kan een module, trigger, functie of een opgeslagen procedure zijn. Het is vergelijkbaar met de functie @@IDENTITY(), behalve dat deze functie slechts een beperkt bereik heeft. De functie SCOPE_IDENTITY retourneert NULL als we deze uitvoeren vóór de invoegbewerking die een waarde in hetzelfde bereik genereert.

Voorbeeld

De onderstaande code gebruikt zowel de functie @@IDENTITY als SCOPE_IDENTITY() in dezelfde sessie. In dit voorbeeld wordt eerst de laatste identiteitswaarde weergegeven en vervolgens wordt één rij in de tabel ingevoegd. Vervolgens voert het beide identiteitsfuncties uit.

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

Als u de code uitvoert, wordt dezelfde waarde weergegeven in de huidige sessie en met een vergelijkbaar bereik. Zie de onderstaande uitvoerafbeelding:

Nu zullen we aan de hand van een voorbeeld zien hoe beide functies verschillen. Eerst gaan we twee tabellen maken met de naam werknemer_gegevens En afdeling met behulp van de onderstaande verklaring:

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

Vervolgens maken we een INSERT-trigger op de tabel werknemer_data. Deze trigger wordt aangeroepen om een ​​rij in de afdelingstabel in te voegen telkens wanneer we een rij in de tabel werknemer_data invoegen.

Met de onderstaande query wordt een trigger gemaakt voor het invoegen van een standaardwaarde 'HET' in de afdelingstabel bij elke invoegquery in de tabel werknemer_data:

typoscript elk

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

Nadat we een trigger hebben gemaakt, gaan we één record in de tabel werknemer_data invoegen en de uitvoer bekijken van zowel de functies @@IDENTITY als SCOPE_IDENTITY().

 INSERT INTO employee_data VALUES ('John Mathew'); 

Als u de query uitvoert, wordt één rij toegevoegd aan de tabel werknemer_data en wordt er in dezelfde sessie een identiteitswaarde gegenereerd. Zodra de invoegquery is uitgevoerd in de tabel werknemer_gegevens, wordt automatisch een trigger aangeroepen om één rij toe te voegen aan de afdelingstabel. De identiteitseedwaarde is 1 voor de werknemergegevens en 100 voor de afdelingstabel.

Ten slotte voeren we de onderstaande instructies uit die de uitvoer 100 weergeven voor de functie SELECT @@IDENTITY en 1 voor de functie SCOPE_IDENTITY, omdat ze de identiteitswaarde alleen in hetzelfde bereik retourneren.

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

Hier is het resultaat:

IDENT_CURRENT() Functie

De IDENT_CURRENT is een door het systeem gedefinieerde functie de meest recente IDENTITEITswaarde weergeven gegenereerd voor een bepaalde tabel onder elke verbinding. Deze functie houdt geen rekening met het bereik van de SQL-query waarmee de identiteitswaarde wordt gemaakt. Voor deze functie is de tabelnaam vereist waarvoor we de identiteitswaarde willen verkrijgen.

Voorbeeld

We kunnen het begrijpen door eerst de twee verbindingsvensters te openen. We zullen één record in het eerste venster invoegen dat de identiteitswaarde 15 in de persoonstabel genereert. Vervolgens kunnen we deze identiteitswaarde verifiëren in een ander verbindingsvenster waar we dezelfde uitvoer kunnen zien. Hier is de volledige code:

 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; 

Als u de bovenstaande codes in twee verschillende vensters uitvoert, wordt dezelfde identiteitswaarde weergegeven.

IDENTITEIT() Functie

De functie IDENTITY() is een door het systeem gedefinieerde functie gebruikt voor het invoegen van een identiteitskolom in een nieuwe tabel . Deze functie verschilt van de eigenschap IDENTITY die we gebruiken met de instructies CREATE TABLE en ALTER TABLE. We kunnen deze functie alleen gebruiken in een SELECT INTO-instructie, die wordt gebruikt bij het overbrengen van gegevens van de ene tabel naar de andere.

De volgende syntaxis illustreert het gebruik van deze functie in SQL Server:

 IDENTITY (data_type , seed , increment) AS column_name 

Als een brontabel een IDENTITY-kolom heeft, neemt de tabel die is gevormd met de opdracht SELECT INTO deze standaard over. Bijvoorbeeld , hebben we eerder een tafelpersoon met een identiteitskolom gemaakt. Stel dat we een nieuwe tabel maken die de persoonstabel overneemt met behulp van de SELECT INTO-instructies met de functie IDENTITY(). In dat geval krijgen we een foutmelding omdat de brontabel al een identiteitskolom heeft. Zie de onderstaande vraag:

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

Als u de bovenstaande instructie uitvoert, wordt het volgende foutbericht weergegeven:

Laten we een nieuwe tabel maken zonder identiteitseigenschap met behulp van de onderstaande verklaring:

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

Kopieer deze tabel vervolgens als volgt met behulp van de instructie SELECT INTO, inclusief de IDENTITY-functie:

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

Zodra de instructie is uitgevoerd, kunnen we deze verifiëren met behulp van de sp_help opdracht die tabeleigenschappen weergeeft.

U kunt de kolom IDENTITEIT zien in de VERLEIDELIJK eigenschappen volgens de gespecificeerde voorwaarden.

Als we deze functie gebruiken met de SELECT-instructie, geeft SQL Server het volgende foutbericht weer:

Bericht 177, niveau 15, status 1, regel 2 De IDENTITY-functie kan alleen worden gebruikt als de SELECT-instructie een INTO-clausule bevat.

IDENTITY-waarden opnieuw gebruiken

We kunnen de identiteitswaarden in de SQL Server-tabel niet hergebruiken. Wanneer we een rij uit de identiteitskolomtabel verwijderen, ontstaat er een gat in de identiteitskolom. Bovendien zal SQL Server een gat creëren wanneer we een nieuwe rij in de identiteitskolom invoegen en de instructie mislukt of teruggedraaid. Het gat geeft aan dat de identiteitswaarden verloren zijn gegaan en niet opnieuw kunnen worden gegenereerd in de IDENTITEITskolom.

Beschouw het onderstaande voorbeeld om het praktisch te begrijpen. We hebben al een persoonstabel met de volgende gegevens:

Vervolgens maken we nog twee tabellen met de naam 'positie' , En ' persoon_positie ' met behulp van de volgende verklaring:

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

Vervolgens proberen we een nieuw record in de persoonstabel in te voegen en deze een positie toe te wijzen door een nieuwe rij toe te voegen aan de persoon_positietabel. We doen dit door gebruik te maken van het transactieoverzicht zoals hieronder:

 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; 

Het bovenstaande transactiecodescript voert de eerste insert-instructie met succes uit. Maar de tweede verklaring mislukte omdat er geen positie met id tien in de positietabel was. Daarom werd de hele transactie teruggedraaid.

Omdat de maximale identiteitswaarde in de PersonID-kolom 16 is, heeft de eerste invoeginstructie de identiteitswaarde 17 verbruikt, waarna de transactie is teruggedraaid. Als we daarom de volgende rij in de tabel Persoon invoegen, is de volgende identiteitswaarde 18. Voer de onderstaande instructie uit:

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

Nadat we de personentabel opnieuw hebben gecontroleerd, zien we dat het nieuw toegevoegde record identiteitswaarde 18 bevat.

Twee IDENTITY-kolommen in één tabel

Technisch gezien is het niet mogelijk om twee identiteitskolommen in één tabel te maken. Als we dit doen, genereert SQL Server een fout. Zie de volgende vraag:

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

Wanneer we deze code uitvoeren, zien we de volgende foutmelding:

We kunnen echter twee identiteitskolommen in één tabel maken door de berekende kolom te gebruiken. Met de volgende query wordt een tabel gemaakt met een berekende kolom die gebruikmaakt van de oorspronkelijke identiteitskolom en wordt deze met 1 verlaagd.

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

Vervolgens zullen we enkele gegevens aan deze tabel toevoegen met behulp van de onderstaande opdracht:

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

Ten slotte controleren we de tabelgegevens met behulp van de SELECT-instructie. Het retourneert de volgende uitvoer:

We kunnen in de afbeelding zien hoe de SecondID-kolom fungeert als een tweede identiteitskolom, die met tien afneemt ten opzichte van de startwaarde van 9990.

Misvattingen over de IDENTITY-kolom van SQL Server

De DBA-gebruiker heeft veel misvattingen over SQL Server-identiteitskolommen. Hieronder volgt een lijst met de meest voorkomende misvattingen over identiteitskolommen die kunnen worden gezien:

IDENTITEIT kolom is UNIEK: Volgens de officiële documentatie van SQL Server kan de identiteitseigenschap niet garanderen dat de kolomwaarde uniek is. We moeten een PRIMARY KEY, UNIQUE-beperking of UNIQUE-index gebruiken om de uniciteit van de kolom af te dwingen.

IDENTITEIT kolom genereert opeenvolgende nummers: Officiële documentatie vermeldt duidelijk dat de toegewezen waarden in de identiteitskolom verloren kunnen gaan bij een databasefout of het opnieuw opstarten van de server. Het kan tijdens het invoegen gaten in de identiteitswaarde veroorzaken. Het gat kan ook ontstaan ​​als we de waarde uit de tabel verwijderen, of als de insert-instructie wordt teruggedraaid. De waarden die gaten veroorzaken, kunnen niet verder worden gebruikt.

De IDENTITY-kolom kan bestaande waarden niet automatisch genereren: Het is niet mogelijk dat de identiteitskolom automatisch bestaande waarden genereert totdat de identiteitseigenschap opnieuw is ingesteld met behulp van de DBCC CHECKIDENT-opdracht. Hiermee kunnen we de startwaarde (startwaarde van de rij) van de identiteitseigenschap aanpassen. Na het uitvoeren van deze opdracht zal SQL Server de nieuw gemaakte waarden die al in de tabel aanwezig zijn, niet controleren of niet.

IDENTITY-kolom als PRIMARY KEY is voldoende om de rij te identificeren: Als een primaire sleutel de identiteitskolom in de tabel bevat zonder enige andere unieke beperkingen, kan de kolom dubbele waarden opslaan en voorkomen dat de kolom uniek is. Zoals we weten kan de primaire sleutel geen dubbele waarden opslaan, maar kan de identiteitskolom wel duplicaten opslaan; het wordt aanbevolen om de primaire sleutel en de identiteitseigenschap niet in dezelfde kolom te gebruiken.

Het verkeerde hulpmiddel gebruiken om identiteitswaarden terug te krijgen na een invoeging: Het is ook een veel voorkomende misvatting dat men zich niet bewust is van de verschillen tussen de functies @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT en IDENTITY() om de identiteitswaarde rechtstreeks in te voegen uit de instructie die we zojuist hebben uitgevoerd.

Verschil tussen SEQUENTIE en IDENTITEIT

We gebruiken zowel SEQUENCE als IDENTITY voor het genereren van autonummers. Er zijn echter enkele verschillen, en het belangrijkste verschil is dat identiteit tabelafhankelijk is, terwijl sequentie dat niet is. Laten we hun verschillen samenvatten in de tabelvorm:

Conclusie

Dit artikel geeft een compleet overzicht van de IDENTITY-eigenschap in SQL Server. Hier hebben we geleerd hoe en wanneer identiteitseigenschap wordt gebruikt, de verschillende functies ervan, misvattingen en hoe deze verschilt van de reeks.