lunes, 10 de diciembre de 2018

IDENTITY en SQL SERVER

SET IDENTITY_INSERT

 


Todos hemos escuchando acerca de las secuencias, que en SQL SERVER se llaman identidades, siempre irán en un campo de tipo int, y generalmente se utilizan para los id secuenciales de una tabla.

Las identidades están compuestas por el número inicial y posteriormente, el incremento que tendrá dicha secuencia, es cómodo el uso de identidades, ya que no tendremos que insertar el valor manualmente y se tendrá un control automático de los consecutivos de una tabla, pero veamos el ejemplo que es como aprendemos,

Consideremos la siguiente tabla:


CREATE TABLE PAISES
(
       ID INT PRIMARY KEY IDENTITY (1,1),
       CODIGOPAIS VARCHAR(4),
       NOMBRE VARCHAR(100),
       COMENTARIOS VARCHAR(MAX),
       CREADO DATE,
       MODIFICADO DATE
)
ON CATALOGOS
 




Como podemos observar en el campo ID INT PRIMARY KEY, tenemos una nueva restricción u opción que es IDENTITY, lleva dos parámetros, el primero indica el número inicial, y el segundo el incremento que se sumará, pero veamos un ejemplo:

INSERT INTO PAISES (CODIGOPAIS, NOMBRE, COMENTARIOS, CREADO, MODIFICADO) VALUES
('MX', 'MEXICO', 'COMENTARIO1', GETDATE(), GETDATE())

Como podemos deducir, en este código estamos insertando valores a la base de datos, a la tabla PAISES que acabamos de crear, si nos fijamos, en ningún momento estamos insertando el campo id, antes bien iniciamos con el nombre.

Ejecutamos el comando, y después realizaremos un SELECT a la tabla, para comprobar que se insertó el numero 1.


SELECT * FROM PAISES
 
 Como ven, en ningún momento se especifico el campo ID, sin embargo, la base de datos, insertó de manera automática dicho id, ésto, debido a que el campo ID es un campo IDENTITY, ahora vamos a insertar un nuevo registro mas

INSERT INTO PAISES (CODIGOPAIS, NOMBRE, COMENTARIOS, CREADO, MODIFICADO) VALUES
('GT', 'GUATEMALA', 'COMENTARIO1', GETDATE(), GETDATE())


Como ven, hemos ingresado un segundo registro, de la mima manera que el anterior, sin especificar el ID, sin embargo, insertó el ID numero 2

¿Que pasa si borramos un registro?. Por ejemplo el ID =  2



DELETE FROM PAISES
WHERE ID = 2

Una vez eliminado, ingresaremos un nuevo registro. ¿Que registro insertará la identidad, el 2 que acabamos de borrar o el 3?

Pues para responder esa pregunta es necesario ejecutar el script




INSERT INTO PAISES (CODIGOPAIS, NOMBRE, COMENTARIOS, CREADO, MODIFICADO) VALUES
('USA', 'UNITED STATES', 'COMENTARIO1', GETDATE(), GETDATE())


Como pueden observar, el siguiente registro fue le número 3, pues el número 2 fue eliminado, sin embargo, continua en la memoria de la base de datos, y es por eso que omite, el id 2. 

Pero que pasa si yo, le asigno arbitrariamente un ID, al campo, para empezar debemos especificar en la lista de campos, la columna Id, y el valor que debemos insertarle, en este caso insertaremos el 2, que si recordamos, se utilizó, pero después se eliminó. Probemos utilizar el siguiente código:

INSERT INTO PAISES (id, CODIGOPAIS, NOMBRE, COMENTARIOS, CREADO, MODIFICADO) VALUES
(2, 'USA', 'UNITED STATES', 'COMENTARIO1', GETDATE(), GETDATE())

Al ejecutar ese script obtendrán el siguiente mensaje de error:


Cannot insert explicit value for identity column in table 'PAISES' when IDENTITY_INSERT is set to OFF.

¿Porque no pudimos insertar datos especificando el campo de identidad? Pues precisamente por eso existe el manejador, nos ayudará a cometer errores, pero si aún así nos sentimos muy duchos, y queremos insertar de cualquier manera el dato, siempre está la opción de deshabilitar temporalmente la identidad, eso sí, bajo nuestro propio riesgo.

Para deshabilitar las propiedad utilizaremos la sentencia.


SET IDENTITY_INSERT  PAISES ON
INSERT INTO PAISES (id, CODIGOPAIS, NOMBRE, COMENTARIOS, CREADO, MODIFICADO) VALUES
(2, 'GT', 'GUATEMALA', 'COMENTARIO1', GETDATE(), GETDATE())
SET IDENTITY_INSERT PAISES OFF
 

La primer línea especifica que la identidad para insertar datos en la tabla paises está encendida, cuando esta propiedad esté en ON, quiere decir que si podremos insertar una identidad que nosotros indiquemos, al final, apagamos  nuevamente la opción para evitar errores futuros.



Pues bien queridos lectores hasta aquí el post, cualquier duda, no duden en preguntar, con gusto les responderé, espero que este post haya sido de su agrado, pero sobre todo de utilidad. Espero compartan, comenten, den +1 o clic a algún anuncio que les interese.

Saludos


 



 
 

 

Banderas de Visitantes

Flag Counter