TechCodeview

La paraula clau IDENTITY és una propietat a SQL Server. Quan es defineix una columna de taula amb una propietat d'identitat, el seu valor serà un valor incremental generat automàticament . Aquest valor el crea automàticament el servidor. Per tant, no podem introduir manualment un valor en una columna d'identitat com a usuari. Per tant, si marquem una columna com a identitat, SQL Server l'omplirà d'una manera d'increment automàtic.

Sintaxi

A continuació es mostra la sintaxi per il·lustrar l'ús de la propietat IDENTITY a SQL Server:

 IDENTITY[(seed, increment)] 

Els paràmetres de sintaxi anteriors s'expliquen a continuació:

Llavor:Indica el valor inicial de la fila carregada a la taula. Per defecte, el seu valor és 1.Increment:Indica el valor incremental, que s'afegeix al valor d'identitat de l'última fila carregada. Per defecte, el seu valor és 1.

Entenem aquest concepte a través d'un exemple senzill.

Suposem que tenim un ' Estudiant taula, i volem ID de l'estudiant per generar automàticament. Tenim un identificador d'estudiant principiant 10 i voleu augmentar-lo en 1 amb cada identificador nou. En aquest escenari, s'han de definir els valors següents.

Llavor: 10

Increment: 1

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

NOTA: Només es permet una columna d'identificació per taula a SQL Server.

Exemple d'IDENTITAT SQL Server

Entendrem com podem utilitzar la propietat identitat a la taula. La propietat d'identitat d'una columna es pot establir quan es crea la taula nova o després d'haver-la creat. Aquí veurem ambdós casos amb exemples.

Propietat IDENTITY amb taula nova

La instrucció següent crearà una taula nova amb la propietat d'identitat a la base de dades especificada:

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

A continuació, inserirem una nova fila en aquesta taula amb un SORTIDA clàusula per veure l'identificador de persona generat automàticament:

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

En executar aquesta consulta, es mostrarà la següent sortida:

Aquesta sortida mostra que la primera fila s'ha inserit amb el valor deu al ID de persona columna tal com s'especifica a la columna d'identitat de definició de la taula.

Inseriu una altra fila al taula de persones com a continuació:

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

Aquesta consulta retornarà la sortida següent:

Aquesta sortida mostra que la segona fila s'ha inserit amb el valor 11 i la tercera fila amb el valor 12 a la columna PersonID.

Propietat IDENTITY amb taula existent

Explicarem aquest concepte suprimint primer la taula anterior i creant-los sense propietat d'identitat. Executeu la instrucció següent per deixar anar la taula:

 DROP TABLE person; 

A continuació, crearem una taula amb la consulta següent:

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

Si volem afegir una nova columna amb la propietat identitat en una taula existent, hem d'utilitzar l'ordre ALTER. La consulta següent afegirà el PersonID com a columna d'identitat a la taula de persones:

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

Afegir valor a la columna d'identitat de manera explícita

Si afegim una nova fila a la taula anterior especificant explícitament el valor de la columna d'identitat, SQL Server generarà un error. Vegeu la consulta següent:

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

L'execució d'aquesta consulta produirà el següent error:

Per inserir explícitament el valor de la columna d'identitat, primer hem d'activar el valor IDENTITY_INSERT. A continuació, executeu l'operació d'inserció per afegir una nova fila a la taula i, a continuació, desactiveu el valor IDENTITY_INSERT. Vegeu l'script de codi següent:

 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 permet als usuaris posar dades a les columnes d'identitat, mentre IDENTITY_INSERT OFF impedeix que aportin valor a aquesta columna.

Si executeu l'script de codi, es mostrarà la sortida següent on podem veure que el PersonID amb el valor 14 s'ha inserit correctament.

Funció IDENTITAT

SQL Server proporciona algunes funcions d'identitat per treballar amb les columnes IDENTITAT d'una taula. Aquestes funcions d'identitat s'enumeren a continuació:

1. Funció @@IDENTITAT
2. Funció SCOPE_IDENTITY().
3. Funció IDENT_CURRENT
4. Funció IDENTITAT

Fem una ullada a les funcions IDENTITAT amb alguns exemples.

Funció @@IDENTITAT

La @@IDENTITY és una funció definida pel sistema que mostra l'últim valor d'identitat (valor màxim d'identitat utilitzat) creat en una taula per a la columna IDENTITAT a la mateixa sessió. Aquesta columna de funció retorna el valor d'identitat generat per la instrucció després d'inserir una nova entrada en una taula. Torna a NUL valor quan executem una consulta que no crea valors IDENTITY. Sempre funciona sota l'àmbit de la sessió actual. No es pot utilitzar de forma remota.

Exemple

Suposem que tenim el valor d'identitat màxim actual a la taula de persones és 13. Ara afegirem un registre a la mateixa sessió que incrementi el valor d'identitat en un. A continuació, utilitzarem la funció @@IDENTITAT per obtenir l'últim valor d'identitat creat a la mateixa sessió.

Aquí teniu l'script de codi complet:

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

L'execució de l'script retornarà la següent sortida on podem veure que el valor màxim d'identitat utilitzat és 14.

Funció SCOPE_IDENTITY().

SCOPE_IDENTITY() és una funció definida pel sistema per mostrar el valor d'identitat més recent en una taula sota l'abast actual. Aquest àmbit pot ser un mòdul, activador, funció o procediment emmagatzemat. És similar a la funció @@IDENTITY(), excepte que aquesta funció només té un abast limitat. La funció SCOPE_IDENTITY retorna NULL si l'executem abans de l'operació d'inserció que genera un valor en el mateix àmbit.

Exemple

El codi següent utilitza la funció @@IDENTITY i SCOPE_IDENTITY() a la mateixa sessió. Aquest exemple mostrarà primer l'últim valor d'identitat i després inserirà una fila a la taula. A continuació, executa les dues funcions d'identitat.

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

Si executeu el codi, es mostrarà el mateix valor a la sessió actual i un àmbit similar. Vegeu la imatge de sortida següent:

Ara veurem com es diferencien ambdues funcions amb un exemple. Primer, crearem dues taules anomenades dades_empleats i departament utilitzant la declaració següent:

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

A continuació, creem un activador INSERT a la taula employee_data. Aquest activador s'invoca per inserir una fila a la taula de departament sempre que inserim una fila a la taula employee_data.

La consulta següent crea un activador per inserir un valor predeterminat 'IT' a la taula de departaments de cada consulta d'inserció a la taula de dades_empleat:

classe abstracta

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

Després de crear un activador, inserirem un registre a la taula de dades_empleat i veurem la sortida de les funcions @@IDENTITY i SCOPE_IDENTITY().

 INSERT INTO employee_data VALUES ('John Mathew'); 

L'execució de la consulta afegirà una fila a la taula employee_data i generarà un valor d'identitat a la mateixa sessió. Un cop s'executa la consulta d'inserció a la taula de dades_empleat, crida automàticament un activador per afegir una fila a la taula de departament. El valor de llavor d'identitat és 1 per a les dades de l'empleat i 100 per a la taula de departament.

Finalment, executem les instruccions següents que mostren la sortida 100 per a la funció SELECT @@IDENTITY i 1 per a la funció SCOPE_IDENTITY perquè només retornen el valor d'identitat en el mateix àmbit.

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

Aquí teniu el resultat:

IDENT_CURRENT() Funció

IDENT_CURRENT és una funció definida pel sistema per mostrar el valor d'IDENTITAT més recent generat per a una taula determinada sota qualsevol connexió. Aquesta funció no considera l'abast de la consulta SQL que crea el valor d'identitat. Aquesta funció requereix el nom de la taula per a la qual volem obtenir el valor d'identitat.

Exemple

Ho podem entendre obrint primer les dues finestres de connexió. Inserirem un registre a la primera finestra que genera el valor d'identitat 15 a la taula de persones. A continuació, podem verificar aquest valor d'identitat en una altra finestra de connexió on podem veure la mateixa sortida. Aquí teniu el codi 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; 

Si executeu els codis anteriors en dues finestres diferents, es mostrarà el mateix valor d'identitat.

Funció IDENTITY().

La funció IDENTITY() és una funció definida pel sistema s'utilitza per inserir una columna d'identitat en una taula nova . Aquesta funció és diferent de la propietat IDENTITY que fem servir amb les sentències CREATE TABLE i ALTER TABLE. Podem utilitzar aquesta funció només en una instrucció SELECT INTO, que s'utilitza durant la transferència de dades d'una taula a una altra.

La sintaxi següent il·lustra l'ús d'aquesta funció a SQL Server:

 IDENTITY (data_type , seed , increment) AS column_name 

Si una taula d'origen té una columna IDENTITAT, la taula formada amb una ordre SELECT INTO l'hereta per defecte. Per exemple , prèviament hem creat una persona de taula amb una columna d'identitat. Suposem que creem una taula nova que hereta la taula de la persona mitjançant les sentències SELECT INTO amb la funció IDENTITY(). En aquest cas, obtindrem un error perquè la taula font ja té una columna d'identitat. Vegeu la consulta següent:

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

L'execució de la instrucció anterior retornarà el següent missatge d'error:

Creem una taula nova sense propietat d'identitat mitjançant la instrucció següent:

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

A continuació, copieu aquesta taula utilitzant la instrucció SELECT INTO que inclou la funció IDENTITAT de la manera següent:

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

Un cop executada la instrucció, podem verificar-la mitjançant el sp_help ordre que mostra les propietats de la taula.

Podeu veure la columna IDENTITAT a TEMPLABLE propietats segons les condicions especificades.

Si utilitzem aquesta funció amb la instrucció SELECT, SQL Server mostrarà el següent missatge d'error:

Msg 177, Nivell 15, Estat 1, Línia 2 La funció IDENTITAT només es pot utilitzar quan la sentència SELECT té una clàusula INTO.

Reutilització dels valors IDENTITY

No podem reutilitzar els valors d'identitat de la taula SQL Server. Quan suprimim qualsevol fila de la taula de la columna d'identitat, es crearà un buit a la columna d'identitat. A més, SQL Server crearà un buit quan inserim una nova fila a la columna d'identitat i la declaració fallarà o es revertirà. El buit indica que els valors d'identitat s'han perdut i que no es poden tornar a generar a la columna IDENTITAT.

Considereu l'exemple següent per entendre-ho pràcticament. Ja tenim una taula de persones que conté les dades següents:

A continuació, crearem dues taules més anomenades 'posició' , i ' persona_posició ' utilitzant la següent declaració:

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

A continuació, intentem inserir un registre nou a la taula persona i assignar-los una posició afegint una nova fila a la taula persona_posició. Ho farem utilitzant l'extracte de la transacció de la següent manera:

 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; 

L'script de codi de transacció anterior executa la primera instrucció d'inserció correctament. Però la segona declaració va fallar ja que no hi havia cap posició amb id ten a la taula de posicions. Per tant, es va revertir tota la transacció.

Com que el valor màxim d'identitat a la columna PersonID és 16, la primera instrucció d'inserció va consumir el valor d'identitat 17 i, a continuació, la transacció es va revertir. Per tant, si inserim la següent fila a la taula Persona, el següent valor d'identitat serà 18. Executeu la instrucció següent:

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

Després de comprovar de nou la taula de persones, veiem que el registre recentment afegit conté el valor d'identitat 18.

Dues columnes IDENTITAT en una única taula

Tècnicament, no és possible crear dues columnes d'identitat en una sola taula. Si fem això, SQL Server llança un error. Consulteu la consulta següent:

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

Quan executem aquest codi, veurem el següent error:

Tanmateix, podem crear dues columnes d'identitat en una sola taula mitjançant la columna calculada. La consulta següent crea una taula amb una columna calculada que utilitza la columna d'identitat original i la disminueix en 1.

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

A continuació, afegirem algunes dades a aquesta taula mitjançant l'ordre següent:

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

Finalment, comprovem les dades de la taula mitjançant la instrucció SELECT. Retorna la següent sortida:

Podem veure a la imatge com la columna SecondID actua com a segona columna d'identitat, disminuint en deu des del valor inicial de 9990.

Concepcions errònies de la columna IDENTITAT de SQL Server

L'usuari de DBA té moltes idees errònies sobre les columnes d'identitat de SQL Server. A continuació es mostren la llista de les idees errònies més comunes sobre les columnes d'identitat que es veurien:

La columna IDENTITAT és ÚNICA: Segons la documentació oficial d'SQL Server, la propietat d'identitat no pot garantir que el valor de la columna sigui únic. Hem d'utilitzar una CLAU PRIMÀRIA, una restricció ÚNICA o un índex ÚNIC per aplicar la singularitat de la columna.

La columna IDENTITAT genera números consecutius: La documentació oficial indica clarament que els valors assignats a la columna d'identitat es poden perdre en cas d'error de la base de dades o reinici del servidor. Pot provocar buits en el valor d'identitat durant la inserció. El buit també es pot crear quan suprimim el valor de la taula, o quan la instrucció d'inserció es reverti. Els valors que generen llacunes no es poden utilitzar més.

La columna IDENTITY no pot generar automàticament els valors existents: No és possible que la columna d'identitat generi automàticament els valors existents fins que la propietat d'identitat es torni a crear mitjançant l'ordre DBCC CHECKIDENT. Ens permet ajustar el valor de llavor (valor inicial de la fila) de la propietat d'identitat. Després d'executar aquesta ordre, SQL Server no comprovarà els valors de nova creació que ja estan presents a la taula o no.

La columna IDENTITY com a CLAU PRIMÀRIA és suficient per identificar la fila: Si una clau primària conté la columna d'identitat a la taula sense cap altra restricció única, la columna pot emmagatzemar valors duplicats i evitar la singularitat de la columna. Com sabem, la clau primària no pot emmagatzemar valors duplicats, però la columna d'identitat pot emmagatzemar duplicats; es recomana no utilitzar la clau primària i la propietat d'identitat a la mateixa columna.

Utilitzant l'eina incorrecta per recuperar els valors d'identitat després d'una inserció: També és una concepció errònia comú sobre el desconeixement de les diferències entre les funcions @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT i IDENTITY() per aconseguir que el valor d'identitat s'insereixi directament des de la instrucció que acabem d'executar.

Diferència entre SEQUÈNCIA i IDENTITAT

Utilitzem tant SEQUENCE com IDENTITY per generar números automàtics. Tanmateix, té algunes diferències, i la diferència principal és que la identitat depèn de la taula, mentre que la seqüència no. Resumim les seves diferències en forma tabular:

Conclusió

Aquest article donarà una visió general completa de la propietat IDENTITY a SQL Server. Aquí hem après com i quan s'utilitza la propietat d'identitat, les seves diferents funcions, idees errònies i com és diferent de la seqüència.