Buscar este blog

martes, 22 de octubre de 2013

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).

lunes, 23 de septiembre de 2013

Script para encontrar código SQL entre los objetos de tu base de datos

En algunas ocasiones, buscando problemas de rendimiento en bases de datos, nos enfrentamos a una lista de consultas que son las causantes de dichos problemas.

Una vez obtenida esa lista de consultas, el problema a resolver es: ¿Dónde están codificadas esas consultas? ¿Forman parte del código de nuestra aplicación o, por el contrario, su código T-SQL está en alguno de nuestros objetos de la base de datos; véase un trigger, un procedimiento almacenado, una función o una vista?

Si se da el segundo caso, y no somos capaces de reconocer el objeto que está ejecutando la consulta que buscamos, disponemos de una vista de sistema (entre las vistas de objetos del catálogo) que nos ayudará a encontrar dónde se esconde: sys.sql_modules.

Así, una consulta como la siguiente es la que necesitamos para encontrar el código:

* Si tu versión de SQL Server es anterior a SQL Server 2005, hay que usar la tabla de sistema sys.syscomments.

lunes, 2 de septiembre de 2013

Programar triggers correctamente: qué son y cómo se usan inserted y deleted

Revisando recientemente la programación de algunos triggers, se han detectado algunos errores que se suelen cometer cuando se desconoce la idiosincrasia de estos objetos y sus tablas especiales inserted y deleted.

martes, 9 de abril de 2013

Parámetros OUTPUT en procedimientos almacenados: recuperar su valor desde la llamada

Los procedimientos almacenados de SQL Server pueden devolver valores bien sea a través de su valor de retorno, bien sea mediante los denominados parámetros OUTPUT.

Ahora bien, para que un procedimiento almacenado nos devuelva el valor de un parámetro OUPUT deberemos tener en cuenta dos cosas: que sea declarado como OUTPUT en el propio procedimiento y que en la ejecución del mismo se especifique la palabra clave OUPUT junto al parámetro en el que queremos que se nos devuelva el valor.

jueves, 17 de enero de 2013

ExecuteNonQuery devuelve -1 si se indica SET NOCOUNT ON

Ejecutando un procedimiento almacenado que, a su vez llamaba a un método en un assembly instalado en el CLR de SQL Server, obtenía un error inesperado. El error me decía que una de las instrucciones SQL ejecutadas dentro del assembly estaba fallando. Sin embargo, si omitía el procesamiento del error, me daba cuenta de que la sentencia se había ejecutado correctamente.

miércoles, 9 de enero de 2013

Microsoft quiere punto y coma al final de cada sentencia T-SQL

Microsoft publica en la MSDN la "Lista de características desusadas del motor de base de datos de SQL Server 2012" o, lo que es lo mismo, las deprecated features.

Esta lista es útil para mantener nuestra aplicación actualizada, de tal forma que podamos seguir usando las nuevas funcionalidades de las sucesivas versiones SQL Server. Mantener en nuestro código alguna característica marcada como obsoleta o en desuso implicaría que no podríamos ejecutarlo en la versión de SQL que ya no la soportase.

Pues bien, en dicha lista aparecen tanto las características que no serán soportadas en la próxima versión de SQL Server como las que estarán en desuso en futuras versiones. Esta segunda lista es muy amplia, pero si leemos detenidamente en ella nos encontraremos con, al menos, una característica que nos llamará la atención:
"Not ending Transact-SQL statements with a semicolon."
Es decir, Microsoft pretende que deje de ser opcional -como lo es hasta ahora- finalizar las sentencias SQL con o sin punto y coma. Hasta ahora, su opcionalidad ha provocado que millones de líneas de código Transact SQL se haya escrito sin punto y coma al finalizar cada sentencia. Incluso el código que Microsoft implementa en las bases de datos de sistema no incluye este símbolo final.

Así pues, parece descabellado que se pretenda que a partir de una incierta futura versión de SQL Server sea obligatorio, como así pretende Microsoft, finalizar cada sentencia con punto y coma. En tal circunstancia, todo el código anterior debería ser revisado... ¡Todo, sin excepción! No se trataría de buscar una determinada característica en él y reemplazarla, sino de cambiarlo entero. Incluso aquel programador acostumbrado a finalizar sus sentencias con dicho signo de puntuación puede haber olvidado ponerlo en alguna ocasión, ya que su T-SQL era igualmente válido.

A través del grupo SQL Server Pofessionals de LinkedIn descubrimos que Microsoft ya mostró su intención  de obligarnos a finalizar las sentencias con punto y coma con la versión SQL Server 2005. A día de hoy, sigue pareciendo imposible que eso suceda. En cualquier caso, ¿empezaríais a programar usando punto y coma para terminar vuestras instrucciones? Parece una buena opción, salvo que se os olvide alguno en el camino y luego no seáis capaces de encontrar el punto y coma desaparecido entre las líneas de código que ya consideráis "correctas".

¿Qué opináis?