La palabra clave IDENTITY es una propiedad en SQL Server. Cuando una columna de la tabla se define con una propiedad de identidad, su valor será un valor incremental generado automáticamente. . Este valor lo crea el servidor automáticamente. Por lo tanto, no podemos ingresar manualmente un valor en una columna de identidad como usuario. Por lo tanto, si marcamos una columna como identidad, SQL Server la completará de forma autoincremental.

Sintaxis

La siguiente es la sintaxis para ilustrar el uso de la propiedad IDENTIDAD en SQL Server:

 IDENTITY[(seed, increment)] 

Los parámetros de sintaxis anteriores se explican a continuación:

Semilla:Indica el valor inicial de la fila cargada en la tabla. Por defecto, su valor es 1.Incremento:Indica el valor incremental, que se suma al valor de identidad de la última fila cargada. Por defecto, su valor es 1.

Entendamos este concepto a través de un ejemplo sencillo.

Supongamos que tenemos un ' Alumno ' mesa, y queremos Identificación del Estudiante que se genere automáticamente. Tenemos una identificación de estudiante inicial de 10 y desea aumentarlo en 1 con cada nueva identificación. En este escenario, se deben definir los siguientes valores.

Semilla: 10

Incremento: 1

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

NOTA: Solo se permite una columna de identificación por tabla en SQL Server.

Ejemplo de IDENTIDAD de SQL Server

Entendamos cómo podemos usar la propiedad de identidad en la tabla. La propiedad de identidad en una columna se puede establecer cuando se crea la nueva tabla o después de su creación. Aquí veremos ambos casos con ejemplos.

Propiedad IDENTIDAD con nueva tabla

La siguiente declaración creará una nueva tabla con la propiedad de identidad en la base de datos 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ón, insertaremos una nueva fila en esta tabla con un PRODUCCIÓN cláusula para ver la identificación de la persona generada automáticamente:

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

Al ejecutar esta consulta se mostrará el siguiente resultado:

Este resultado muestra que la primera fila se ha insertado con el valor diez en el ID de persona columna como se especifica en la columna de identidad de definición de tabla.

Insertemos otra fila en el mesa de persona como a continuación:

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

Esta consulta devolverá el siguiente resultado:

Este resultado muestra que la segunda fila se ha insertado con el valor 11 y la tercera fila con el valor 12 en la columna PersonID.

Propiedad IDENTIDAD con tabla existente

Explicaremos este concepto eliminando primero la tabla anterior y creándola sin propiedad de identidad. Ejecute la siguiente declaración para eliminar la tabla:

 DROP TABLE person; 

A continuación, crearemos una tabla utilizando la siguiente consulta:

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

Si queremos agregar una nueva columna con la propiedad de identidad en una tabla existente, necesitamos usar el comando ALTER. La siguiente consulta agregará PersonID como una columna de identidad en la tabla de personas:

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

Agregar valor explícitamente a la columna de identidad

Si agregamos una nueva fila a la tabla anterior especificando explícitamente el valor de la columna de identidad, SQL Server arrojará un error. Vea la siguiente consulta:

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

La ejecución de esta consulta generará el siguiente error:

Para insertar el valor de la columna de identidad explícitamente, primero debemos establecer el valor IDENTITY_INSERT en ON. A continuación, ejecute la operación de inserción para agregar una nueva fila a la tabla y luego establezca el valor IDENTITY_INSERT en OFF. Vea el siguiente script de código:

 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; 

IDENTIDAD_INSERT EN permite a los usuarios colocar datos en columnas de identidad, mientras IDENTITY_INSERT DESACTIVADO les impide agregar valor a esta columna.

Al ejecutar el script de código se mostrará el siguiente resultado donde podemos ver que el PersonID con valor 14 se insertó correctamente.

Función IDENTIDAD

SQL Server proporciona algunas funciones de identidad para trabajar con las columnas IDENTIDAD en una tabla. Estas funciones de identidad se enumeran a continuación:

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

Echemos un vistazo a las funciones de IDENTIDAD con algunos ejemplos.

Función @@IDENTIDAD

@@IDENTITY es una función definida por el sistema que muestra el último valor de identidad (valor de identidad máximo utilizado) creado en una tabla para la columna IDENTIDAD en la misma sesión. Esta columna de función devuelve el valor de identidad generado por la declaración después de insertar una nueva entrada en una tabla. Devuelve un NULO valor cuando ejecutamos una consulta que no crea valores de IDENTIDAD. Siempre funciona bajo el alcance de la sesión actual. No se puede utilizar de forma remota.

Ejemplo

Supongamos que tenemos el valor de identidad máximo actual en la tabla de personas es 13. Ahora agregaremos un registro en la misma sesión que incrementa el valor de identidad en uno. Luego usaremos la función @@IDENTITY para obtener el último valor de identidad creado en la misma sesión.

Aquí está el script de código completo:

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

La ejecución del script devolverá el siguiente resultado donde podemos ver que el valor de identidad máximo utilizado es 14.

Función SCOPE_IDENTITY()

SCOPE_IDENTITY() es una función definida por el sistema para mostrar el valor de identidad más reciente en una tabla bajo el alcance actual. Este alcance puede ser un módulo, un disparador, una función o un procedimiento almacenado. Es similar a la función @@IDENTITY(), excepto que esta función solo tiene un alcance limitado. La función SCOPE_IDENTITY devuelve NULL si la ejecutamos antes de la operación de inserción que genera un valor en el mismo alcance.

Ejemplo

El siguiente código utiliza las funciones @@IDENTITY y SCOPE_IDENTITY() en la misma sesión. Este ejemplo primero mostrará el último valor de identidad y luego insertará una fila en la tabla. A continuación, ejecuta ambas funciones de identidad.

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

La ejecución del código mostrará el mismo valor en la sesión actual y un alcance similar. Vea la siguiente imagen de salida:

Ahora veremos en qué se diferencian ambas funciones con un ejemplo. Primero, crearemos dos tablas llamadas datos_empleado y departamento usando la siguiente declaración:

 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ón, creamos un disparador INSERT en la tabla de datos_empleados. Este disparador se invoca para insertar una fila en la tabla de departamento cada vez que insertamos una fila en la tabla de datos_empleados.

La siguiente consulta crea un disparador para insertar un valor predeterminado 'ÉL' en la tabla de departamento en cada consulta de inserción en la tabla de datos_empleados:

ruta establecida en java

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

Después de crear un activador, insertaremos un registro en la tabla de datos_empleados y veremos el resultado de las funciones @@IDENTITY y SCOPE_IDENTITY().

 INSERT INTO employee_data VALUES ('John Mathew'); 

La ejecución de la consulta agregará una fila a la tabla Employee_data y generará un valor de identidad en la misma sesión. Una vez que se ejecuta la consulta de inserción en la tabla de datos_empleados, automáticamente llama a un disparador para agregar una fila en la tabla de departamento. El valor inicial de identidad es 1 para los datos_empleado y 100 para la tabla de departamentos.

Finalmente, ejecutamos las siguientes declaraciones que muestran la salida 100 para la función SELECT @@IDENTITY y 1 para la función SCOPE_IDENTITY porque devuelven un valor de identidad solo en el mismo alcance.

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

Aquí está el resultado:

Función IDENT_CURRENT()

IDENT_CURRENT es una función definida por el sistema para mostrar el valor de IDENTIDAD más reciente generado para una tabla determinada bajo cualquier conexión. Esta función no considera el alcance de la consulta SQL que crea el valor de identidad. Esta función requiere el nombre de la tabla para la cual queremos obtener el valor de identidad.

Ejemplo

Podemos entenderlo abriendo primero las dos ventanas de conexión. Insertaremos un registro en la primera ventana que genera el valor de identidad 15 en la tabla de personas. A continuación, podemos verificar este valor de identidad en otra ventana de conexión donde podemos ver el mismo resultado. Aquí está el código completo:

 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; 

La ejecución de los códigos anteriores en dos ventanas diferentes mostrará el mismo valor de identidad.

Función IDENTIDAD()

La función IDENTITY() es una función definida por el sistema. utilizado para insertar una columna de identidad en una nueva tabla . Esta función es diferente de la propiedad IDENTITY que usamos con las declaraciones CREATE TABLE y ALTER TABLE. Podemos usar esta función solo en una instrucción SELECT INTO, que se usa al transferir datos de una tabla a otra.

La siguiente sintaxis ilustra el uso de esta función en SQL Server:

 IDENTITY (data_type , seed , increment) AS column_name 

Si una tabla de origen tiene una columna IDENTIDAD, la tabla formada con un comando SELECT INTO la hereda de forma predeterminada. Por ejemplo , previamente hemos creado una persona de mesa con una columna de identidad. Supongamos que creamos una nueva tabla que hereda la tabla de personas usando las declaraciones SELECT INTO con la función IDENTITY(). En ese caso, obtendremos un error porque la tabla fuente ya tiene una columna de identidad. Vea la siguiente consulta:

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

La ejecución de la declaración anterior devolverá el siguiente mensaje de error:

Creemos una nueva tabla sin propiedad de identidad usando la siguiente declaración:

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

Luego, copie esta tabla usando la instrucción SELECT INTO que incluye la función IDENTIDAD de la siguiente manera:

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

Una vez que se ejecuta la declaración, podemos verificarla usando el sp_help comando que muestra las propiedades de la tabla.

Puedes ver la columna IDENTIDAD en el TENTABLE propiedades según las condiciones especificadas.

Si usamos esta función con la instrucción SELECT, SQL Server mostrará el siguiente mensaje de error:

Mensaje 177, Nivel 15, Estado 1, Línea 2 La función IDENTIDAD sólo se puede utilizar cuando la instrucción SELECT tiene una cláusula INTO.

Reutilizar valores de IDENTIDAD

No podemos reutilizar los valores de identidad en la tabla de SQL Server. Cuando eliminamos cualquier fila de la tabla de la columna de identidad, se creará un espacio en la columna de identidad. Además, SQL Server creará un espacio cuando insertemos una nueva fila en la columna de identidad y la declaración falle o se revierta. El espacio indica que los valores de identidad se pierden y no se pueden generar nuevamente en la columna IDENTIDAD.

Considere el siguiente ejemplo para entenderlo de manera práctica. Ya tenemos una tabla de personas que contiene los siguientes datos:

A continuación, crearemos dos tablas más llamadas 'posición' , y ' posición_persona ' usando la siguiente declaración:

 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ón, intentamos insertar un nuevo registro en la tabla de personas y asignarles una posición agregando una nueva fila en la tabla person_position. Haremos esto utilizando la declaración de transacción como se muestra a continuación:

 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; 

El script del código de transacción anterior ejecuta la primera instrucción de inserción con éxito. Pero la segunda declaración falló porque no había ninguna posición con id diez en la tabla de posiciones. Por lo tanto, se revirtió toda la transacción.

Dado que tenemos el valor de identidad máximo en la columna PersonID es 16, la primera declaración de inserción consumió el valor de identidad 17 y luego la transacción se revirtió. Por lo tanto, si insertamos la siguiente fila en la tabla Persona, el siguiente valor de identidad será 18. Ejecute la siguiente declaración:

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

Después de revisar la tabla de personas nuevamente, vemos que el registro recién agregado contiene el valor de identidad 18.

Dos columnas de IDENTIDAD en una sola tabla

Técnicamente, no es posible crear dos columnas de identidad en una sola tabla. Si hacemos esto, SQL Server arroja un error. Vea la siguiente consulta:

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

Cuando ejecutamos este código, veremos el siguiente error:

Sin embargo, podemos crear dos columnas de identidad en una sola tabla utilizando la columna calculada. La siguiente consulta crea una tabla con una columna calculada que utiliza la columna de identidad original y la reduce en 1.

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

A continuación, agregaremos algunos datos a esta tabla usando el siguiente comando:

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

Finalmente, verificamos los datos de la tabla usando la declaración SELECT. Devuelve el siguiente resultado:

Podemos ver en la imagen como la columna SecondID actúa como una segunda columna de identidad, disminuyendo en diez desde el valor inicial de 9990.

Conceptos erróneos de la columna IDENTIDAD de SQL Server

El usuario de DBA tiene muchos conceptos erróneos sobre las columnas de identidad de SQL Server. La siguiente es la lista de los conceptos erróneos más comunes sobre las columnas de identidad que se podrían ver:

La columna IDENTIDAD es ÚNICA: Según la documentación oficial de SQL Server, la propiedad de identidad no puede garantizar que el valor de la columna sea único. Debemos utilizar una CLAVE PRIMARIA, una restricción ÚNICA o un índice ÚNICO para imponer la unicidad de la columna.

La columna IDENTIDAD genera números consecutivos: La documentación oficial establece claramente que los valores asignados en la columna de identidad se pueden perder ante una falla de la base de datos o un reinicio del servidor. Puede provocar espacios en el valor de identidad durante la inserción. El espacio también se puede crear cuando eliminamos el valor de la tabla o cuando se revierte la instrucción de inserción. Los valores que generan lagunas no se pueden utilizar más.

La columna IDENTIDAD no puede generar automáticamente valores existentes: No es posible que la columna de identidad genere automáticamente valores existentes hasta que se reinicie la propiedad de identidad mediante el comando DBCC CHECKIDENT. Nos permite ajustar el valor inicial (valor inicial de la fila) de la propiedad de identidad. Después de ejecutar este comando, SQL Server no verificará los valores recién creados que ya están presentes en la tabla o no.

La columna IDENTIDAD como CLAVE PRIMARIA es suficiente para identificar la fila: Si una clave principal contiene la columna de identidad en la tabla sin ninguna otra restricción única, la columna puede almacenar valores duplicados y evitar la unicidad de la columna. Como sabemos, la clave principal no puede almacenar valores duplicados, pero la columna de identidad puede almacenar duplicados; se recomienda no utilizar la clave principal y la propiedad de identidad en la misma columna.

Usar la herramienta incorrecta para recuperar valores de identidad después de una inserción: También es un error común el desconocimiento de las diferencias entre las funciones @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT e IDENTITY() para obtener el valor de identidad directamente insertado desde la declaración que acabamos de ejecutar.

Diferencia entre SECUENCIA e IDENTIDAD

Usamos tanto SECUENCIA como IDENTIDAD para generar números de automóviles. Sin embargo, tiene algunas diferencias, y la principal diferencia es que la identidad depende de la tabla, mientras que la secuencia no. Resumamos sus diferencias en forma tabular:

Conclusión

Este artículo brindará una descripción completa de la propiedad IDENTITY en SQL Server. Aquí hemos aprendido cómo y cuándo se utiliza la propiedad de identidad, sus diferentes funciones, conceptos erróneos y en qué se diferencia de la secuencia.