En el artículo Columnas IDENTITY (Parte 1) estudiamos los conceptos básicos de una columna de identidad, como se configura y los aspectos que debemos tener en cuenta al momento de utilizar este tipo especial de columna. Esta es una continuación de dicho artículo, por lo que recomendamos leer primero la parte 1.
En este artículo vamos a ver como insertar un valor explicito en una columna de tipo Identity. Pero antes quería recalcar que en una tabla solo puede haber un campo definido como columna de identidad, solo uno. Comenzamos creando nuestra base de datos y tabla de prueba:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
--Creación de base de datos de prueba CREATE DATABASE TestIdentity GO USE TestIdentity GO --Creación de tabla de prueba utilizando una columna Identity como llave primaria CREATE TABLE dbo.RastreaOrdenes ( Id Int IDENTITY(1,1) NOT NULL CONSTRAINT Pk_RastreaOrdenes PRIMARY KEY, NroOrden Int NOT NULL, FechaOperacion date NOT NULL, TextoOperación varchar(50)) GO |
Insertamos un par de registros de prueba:
|
1 2 3 4 5 6 7 8 9 |
--Insertar un registro incluyendo un dato en la columna Identity INSERT INTO dbo.RastreaOrdenes (Id, NroOrden, FechaOperacion, TextoOperación) VALUES (1, 1054, SYSDATETIME(), 'Creación de la Orden') GO --Insertar un registro INSERT INTO dbo.RastreaOrdenes (NroOrden, FechaOperacion, TextoOperación) VALUES (1054, SYSDATETIME(), 'Creación de la Orden') GO |
Forzamos un error para generar un salto en la secuencia de identidad
|
1 2 3 4 |
--Inserta un registro sin incluir un dato obligatorio, para forzar un error de insersión INSERT INTO RastreaOrdenes (NroOrden, TextoOperación) VALUES (935, 'Modificación de la Orden') GO |
Al ejecutar esta sentencia, debe retornar el siguiente error:
The statement has been terminated.
Insertamos un registro de prueba adicional
|
1 2 3 4 |
--Insertamos un registro de prueba INSERT INTO dbo.RastreaOrdenes (NroOrden, FechaOperacion, TextoOperación) VALUES (1328, SYSDATETIME(), 'Creación de esta Orden') GO |
Y esto es lo que tenemos hasta el momento:
|
1 2 |
SELECT * FROM dbo.RastreaOrdenes GO |
Observamos que el numero 3 no se encuentra. Ya en el artículo anterior explicamos que esto pasa por que, cuando intenté ingresar el tercer registro no se especificó el valor de una columna que es obligatoria. SQL Server retorna error y no inserta el registro, pero si aumenta el numero de la secuencia.
Entonces, que pasa si yo deseo ingresar un registro con el valor de Id e 3? Acaso no vimos en el primer artículo que si especifico un valor para la columna de identidad, retorna error?
Hay una forma de hacerlo y es utilizando la propiedad IDENTITY_INSERT. Vamos a ver como:
|
1 2 3 4 5 6 7 8 9 10 11 |
--queremos insertar un registro para completar un registro con el numero 3 SET IDENTITY_INSERT dbo.RastreaOrdenes ON GO --Insertar un registro indicando explicitamente un valor para la columna Identity INSERT INTO dbo.RastreaOrdenes (Id, NroOrden, FechaOperacion, TextoOperación) VALUES (3, 983, SYSDATETIME(), 'Modificación de la Orden') GO SELECT * FROM RastreaOrdenes GO |
Como vemos, ya hemos logrado insertar un registro completando la serie con el número que faltaba. Pero, como hemos configurado la propiedad IDENTITY_INSERT con el valor de OFF (apagada) estamos obligados a seguir indicando explicitamente el valor en el campo Id. Veamos:
|
1 2 3 4 |
--Insertamos un registro sin indicar el valor para el campo Id INSERT INTO dbo.RastreaOrdenes (NroOrden, FechaOperacion, TextoOperación) VALUES (1120, SYSDATETIME(), 'Modificación de la Orden') GO |
Al ejecutar esta sentencia, obtenemos el siguiente mensaje de error:
Entonces, para que nuevamente el valor de la columna de identidad se autogenere de manera automática, se tiene que “prender” la propiedad IDENTITY_INSERT:
|
1 2 |
SET IDENTITY_INSERT OFF GO |
OTROS CONCEPTOS RELATIVOS A COLUMNAS IDENTITY
IDENT_SEED: Permite averiguar el numero inicial de la columna de identidad.
|
1 |
SELECT IDENT_SEED('dbo.RastreaOrdenes') |
IDENT_INCR: Permite averiguar el incremento de la columna de identidad.
|
1 |
SELECT IDENT_INCR('dbo.RastreaOrdenes') |
IDENT_CURRENT: Permite averiguar el numero actual de la columna de identidad.
|
1 |
SELECT IDENT_CURRENT('dbo.RastreaOrdenes') |
SCOPE_IDENTITY: Permite averiguar el último numero generado por la columna de identidad. Esta función utilizada en la transacción de inserción de un registro permite averiguar el numero generado en tiempo de ejecución.
|
1 |
SELECT SCOPE_IDENTITY() |
Si deseamos eliminar uno o varios registros, para no alterar la continuidad de la secuencia se puede reconfigurar el valor actual de la secuencia. Para ello utilizamos un Data Base Control Command (DBCC). Hay que prestar atención para que no se repita un valor, si el campo es llave primaria:
|
1 2 |
--Reconfigurar la semilla o el numero correlativo de la tabla para que reinicie en 3 DBCC CHECKIDENT ( 'dbo.RastreaOrdenes', RESEED, 3 ) |
|
1 2 |
--Reconfigurar la semilla o el numero correlativo de la tabla para que comience desde 0 DBCC CHECKIDENT ( 'dbo.RastreaOrdenes', RESEED, 0 ) |
no se muestra los codigos que se necesitan
Ya se actualizaron los códigos en el artículo. Gracias por el aviso!
Hola, una pregunta: En qué casos conviene usar una columna identity y cuando no, es decir cuando optar por una identity o una clave natural. Gracias
Ricardo: En todos los casos es mejor usar una clave subrogada (artificial) con números enteros. Los números enteros son más rápidos de procesar, por lo que favorecen la velocidad en la lectura y la resolución de los JOIN.
La columna Identity solo soportan tipos de datos enteros, y es práctica por que te libera de la necesidad de agregar código para controlar el correlativo.
Saludos
Alberto
Hola amigo, excelente articulo,
solo una presición, en el artículo 1 dices lo siguiente:
“Una tabla puede tener más de una columna IDENTITY.”
en el articulo 2 contradices esta afirmación,
Pero antes quería recalcar que en una tabla solo puede haber una campo definido como columna de identidad, solo uno.
esto podria causar algo de confución, yo mismo realice la prueba, y es como lo dices en el articulo 1, puede haber mas de un campo identity en una misma tabla,
saludos
Muchas gracias Mario por tu aporte. Vamos a hacer la corrección de inmediato.
Saludos