Ir al contenido principal

Columnas Identity: Insertar valores en columnas con la propiedad Identity

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:
  • 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).
El primero de los problemas no debería suponer inconveniente alguno, aunque puede ser un factor de decisión para acabar usando Identity o no.

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

Entradas populares de este blog

Aprendiendo a usar LEFT OUTER JOIN

En esta entrada pretendemos explicar los diferentes resultados obtenidos por distintas construcciones de consultas que, aparentemente, deberían producir el mismo conjunto de resultados. Así, veremos las diferencias entre filtrar los resultados de una query en la unión (Join) mediante condiciones ON y mediante cláusulas WHERE.

Variantes del SELECT COUNT con DISTINCT

Seguramente, muchos de vosotros habréis usado en innumerables ocasiones la función de T-SQL COUNT , que no hace sino devolver un número de registros: de una tabla, de un conjunto de resultados, etc... En una de sus aplicaciones, combinado con el DISTINCT -uno de los dos argumentos que admite- COUNT nos devuelve el número de valores únicos no nulos de la tabla o conjunto de resultados que estemos consultando. Pero ¡ojo! Cuidado con la sintaxis , o podemos obtener el valor equivocado sin darnos cuenta. No es lo mismo: SELECT COUNT (DISTINCT NombreCampo) FROM NombreTabla que: SELECT COUNT(*), DISTINCT NombreCampo FROM NombreTabla

Script para obtener el tamaño de todas las tablas de la base de datos

En algunas ocasiones podemos vernos con la necesidad de conocer qué tablas de nuestra base de datos están ocupando más espacio en disco. Por ejemplo, si disponemos de SQL Server Express , cuyas bases de datos están limitadas a 4GB o 10GB, según la versión que estemos usando -4, hasta 2005; 10, a partir de 2008-, aparte de usar las opciones de comprimir la base de datos, poner el log en el modo simple de recuperación o ajustar las políticas de crecimiento automático de nuestros ficheros, podemos necesitar averiguar qué tablas crecen más para tomar las decisiones oportunas.