La propiedad Identity sobre una columna de una tabla de la base de datos, hace que en ésta sus valores se generen secuencialmente, a partir de una raíz, para cada registro que insertemos en ella. Las columnas identity son de tipo numérico, generalmente entero, quedando los tipos de datos que soportan dicha propiedad los siguientes: tinyint, smallint, int, bigint, decimal(p,0), o numeric(p,0).
Identity supone una gran ayuda para generar claves sobre tablas. Sus ventajas son, principalmente, que es SQL Server quien se encarga de rellenar su valor y que jamás se va a repetir el valor de la misma, ya que SQL Server genera el siguiente valor disponible para cada registro insertado. Esto evita problemas de concurrencia. Es muy típico en las aplicaciones obtener el máximo valor de la columna clave, sumarle 1 e insertar el siguiente registro. Este mecanismo no presenta ningún problema si somos el único usuario conectado a la base de datos. Sin embargo, en cuanto haya dos usuarios conectados a la misma base de datos, puede darse el escenario en el que ambos leen el mismo valor y, por consiguiente, intentan insertar el mismo valor, produciendo así una violación de la clave primaria.
Por supuesto, este escenario es controlable, mediante gestión de concurrencia, transacciones, etc. Pero todo ello tiene un coste, tanto de programación como de rendimiento. Identity aparece aquí al rescate.
Identity también tiene sus potenciales problemas:
Para el último de los puntos, existe una solución desde SQL Server 2005, mediante el uso de la cláusula OUTPUT.
Centrémonos ahora en el punto intermedio, que es el que da título a esta entrada:
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'TestIdentity' when IDENTITY_INSERT is set to OFF.
En ese mensaje tenemos una pista de lo que podemos hacer. Activando la opción IDENTITY_INSERT sobre la tabla, podremos insertar el valor que queramos. Por ejemplo, de la siguiente manera:
Es sencillo, y nos da cierto control sobre columnas con la propiedad Identity. La única restricción que debemos tener en cuenta, es que no podemos ejecutar simultáneamente SET IDENTITY_INSERT ON para más de una tabla en una sesión/conexión.
Pero ¡ojo! si hacemos uso de SET IDENTITY_INSERT ON, nada ni nadie nos asegura que no vayamos a tener valores repetidos en las columnas Identity. Así pues, si no estamos usando estas columnas como clave primaria de la tabla, es conveniente crear una restricción de unicidad sobre ellas mediante la creación de un índice único. La decisión de si ha de ser clustered o no ya depende de otros factores que debemos valorar (si normalmente hacemos consultas en las que en el WHERE se va a usar esta columna u otra, etc).
Identity supone una gran ayuda para generar claves sobre tablas. Sus ventajas son, principalmente, que es SQL Server quien se encarga de rellenar su valor y que jamás se va a repetir el valor de la misma, ya que SQL Server genera el siguiente valor disponible para cada registro insertado. Esto evita problemas de concurrencia. Es muy típico en las aplicaciones obtener el máximo valor de la columna clave, sumarle 1 e insertar el siguiente registro. Este mecanismo no presenta ningún problema si somos el único usuario conectado a la base de datos. Sin embargo, en cuanto haya dos usuarios conectados a la misma base de datos, puede darse el escenario en el que ambos leen el mismo valor y, por consiguiente, intentan insertar el mismo valor, produciendo así una violación de la clave primaria.
Por supuesto, este escenario es controlable, mediante gestión de concurrencia, transacciones, etc. Pero todo ello tiene un coste, tanto de programación como de rendimiento. Identity aparece aquí al rescate.
Identity también tiene sus potenciales problemas:
- Si se borran registros, se quedarán saltos en la numeración que jamás se usarán.
- Inicialmente, no podemos elegir el valor que se inserta en cada registro
- Es más complicado obtener el número recién insertado para seguir operando con el objeto en nuestra aplicación (supongamos que creamos un cliente nuevo en nuestra aplicación de ventas, y queremos saber su código para mantener una referencia a él en caso de que queramos hacer posteriores actualizaciones).
Para el último de los puntos, existe una solución desde SQL Server 2005, mediante el uso de la cláusula OUTPUT.
Centrémonos ahora en el punto intermedio, que es el que da título a esta entrada:
Cómo insertar un valor en una columna Identity
Es sencillo. En un principio, si intentamos insertar un valor en una columna de este tipo, obtendremos el siguiente mensaje de error:Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'TestIdentity' when IDENTITY_INSERT is set to OFF.
En ese mensaje tenemos una pista de lo que podemos hacer. Activando la opción IDENTITY_INSERT sobre la tabla, podremos insertar el valor que queramos. Por ejemplo, de la siguiente manera:
Es sencillo, y nos da cierto control sobre columnas con la propiedad Identity. La única restricción que debemos tener en cuenta, es que no podemos ejecutar simultáneamente SET IDENTITY_INSERT ON para más de una tabla en una sesión/conexión.
Actualización:
Como decíamos al principio, el propósito de las columnas de tipo Identity es el de servir de claves únicas, ya sean clustered o no, ya sean la clave primaria o no. Podemos pensar que, como delegamos en SQL Server la responsabilidad de insertar nuevos valores en estas columnas, de manera sucesiva y única, nunca vamos a tener valores repetidos en la misma.Pero ¡ojo! si hacemos uso de SET IDENTITY_INSERT ON, nada ni nadie nos asegura que no vayamos a tener valores repetidos en las columnas Identity. Así pues, si no estamos usando estas columnas como clave primaria de la tabla, es conveniente crear una restricción de unicidad sobre ellas mediante la creación de un índice único. La decisión de si ha de ser clustered o no ya depende de otros factores que debemos valorar (si normalmente hacemos consultas en las que en el WHERE se va a usar esta columna u otra, etc).
Comentarios
Publicar un comentario