Una secuencia permite definir un valor numérico incremental, que puede ser reseteado, configurado como cíclico, configurado con valores máximos y mínimos, y que además pueden ser utilizado en múltiples tablas, ya que no está enlazado a una columna de una tabla específica, sino que es un objeto completamente independiente que podremos utilizar mediante la función NEXT VALUE FOR. Además, al ser totalmente transparente para las tablas, se eliminan muchos de los inconvenientes que tiene utilizar la propiedad IDENTITY.
Algunos inconvenientes y otros detalles de la propiedad IDENTITY
Recordemos que la propiedad IDENTITY no se puede poner o quitar de una columna existente, algo que resulta bastante molesto, especialmente si tenemos en cuenta que el valor de una columna IDENTITY no puede ser modificado después de haber sido insertado, por lo que en caso de necesidad, nos veríamos obligados a eliminar las filas y volverlas a insertar, con todas las implicaciones que esto puede llegar a tener (ej: Integridad Referencial, necesidades de almacenamiento, bloqueos, etc), y teniendo en cuenta que para las nuevas filas se generarán nuevos valores de Identidad.
Además, si necesitásemos insertar manualmente valores en una columna IDENTITY (es decir, insertar el valor que nosotros deseemos y no utilizar el autonumérico correspondiente), deberíamos habilitar la inserción de identidad (SET IDENTITY_INSERT dbo.MiTabla ON), para insertar las filas correspondientes (ojo, insertar, de actualizar nos olvidamos), y seguidamente volver a deshabilitar la inserción de identidad (SET IDENTITY_INSERT dbo.MiTabla OFF). Para acabar de quitarnos las ganas de utilizar la propiedad IDENTITY, sólo podemos habilitar la inserción de identidad (SET IDENTITY_INSERT) en una única tabla a la vez.
Para ayudarnos en todas estas tareas relacionadas con las columnas IDENTITY, también podemos utilizar el comando DBCC CHECKIDENT, que nos permite poder consultar el estado actual de una columna IDENTITY (ej: DBCC CHECKIDENT('dbo.MiTable', NORESEED) ), así como resetear el valor identidad, es decir, poder especificar cuál será el siguiente valor que se debe asignar (ej: DBCC CHECKIDENT('dbo.MiTable', RESEED, 120) ) para que se asigne 121 como el próximo valor identidad).
Algo que también deberemos tener en cuenta es que en caso de truncar una tabla que tenga una columna IDENTITY (TRUNCATE TABLE) el valor IDENTITY será reseteado de forma implícita, algo que no ocurrirá en otros casos (ej: al hacer un DELETE de la tabla).
En caso de que necesitamos cargar una tabla completa que tiene una columna IDENTITY (ej: estamos moviendo información de una aplicación a otra, un proceso de migración, regenerar una tabla, etc.), nos tocará crear una nueva tabla sobre la que insertar las filas (habilitando SET IDENTITY_INSERT antes de empezar y deshabilitándolo al finalizar), con todas las implicaciones y problemas que conlleva este tipo de tareas (ej: renombrado de las tablas, eliminar y volver a crear las Claves Externas, gestión correcta de bloqueos y transacciones, ajustar el siguiente valor identidad que deseamos asignar con DBCC CHECKIDENT, etc.).
También tenemos disponible la función del sistema @@IDENTITY, que podemos consultar justo después de insertar una fila sobre una tabla que tiene una columna IDENTITY, de modo que podamos conocer cuál es el valor IDENTITY que acabamos de insertar. Adicionalmente, tenemos otras funciones del sistema similares, como SCOPE_IDENTITY() e IDENT_CURRENT(). Esta última, permite conocer el último valor IDENTIDAD insertado en una tabla, similar al comando DBCC CHECKIDENT(), aunque en este caso se trata de una función del sistema (ej: SELECT IDENT_CURRENT('dbo.MiTable') ).
Además, en caso de necesidad, también tenemos las funciones IDENT_SEED() e IDENT_INCR(), que nos permiten consultar cuales fueron los valores semilla e incremento utilizados en la creación de la columna identidad (ej: SELECT IDENT_SEED('dbo.MiTable'), IDENT_INCR('dbo.MiTable') ). Aunque quizás para este tipo de menesteres, nos pueda interesar más consultar la vista del catálogo sys.identity_columns.
Utilizando Secuencias en SQL Server 2012
OK. Ya me he autoconvencido. Quiero utilizar Secuencias. ¿Cómo lo hago? Podemos crear una secuencia ejecutando un comando CREATE SEQUENCE similar al siguiente:
CREATE SEQUENCE dbo.MySequence
AS INTEGER
START WITH 7
INCREMENT BY 5
MINVALUE 2
MAXVALUE 63
CYCLE;
|
Como se puede apreciar, dicho comando creará una nueva Secuencia, que empezará en el valor 7, se incrementará de 5 en 5, dentro de un rango entre el 2 y el 63, y al alcanzar su valor máximo empezará de nuevo a asignar valores desde el 2, ya que está configurado como cíclico.
A partir de este momento, podremos utilizar nuestra Secuencia utilizando la función NEXT VALUE FOR. Por ejemplo, podríamos ejecutar varias veces la sentencia SELECT NEXT VALUE FOR dbo.MySequence para comprobar el funcionamiento de la Secuencia que acabamos de crear, sin necesidad de insertar ningún valor en ninguna tabla.
Después de probarlo, podemos eliminar y volver a crear nuestra secuencia, o bien resetearla ejecutando un comando como el siguiente: ALTER SEQUENCE dbo.MySequence RESTART
En ambos casos, lo podríamos hacer de forma totalmente transparente, y digo esto, por los problemas que hemos comentamos relacionados con la propiedad IDENTITY. Mola.
Y ahora podríamos utilizar nuestra Secuencia, por ejemplo al insertar filas nuevas en una tabla desde nuestros Procedimientos Almacenados, ejecutando algo como: INSERT INTO dbo.MiTable VALUES (NEXT VALUE FOR dbo.MySequence)
O también podríamos utilizar nuestra Secuencia, por ejemplo creando una restricción de tipo DEFAULT para una columna que llame a la función NEXT VALUE FOR, y así conseguir un efecto similar al de una columna IDENTIDAD (más o menos, ya que esta columna será actualizable, excepto que la protejamos nosotros mismos con un Trigger, por ejemplo).
De hecho, incluso podríamos plantearnos sustituir nuestras columnas IDENTITY por Secuencias, haciendo algo como lo siguiente (ojo, que es sólo un ejemplo):
- Crear una nueva columna para utilizar con una Secuencia, ejecutando un ALTER TABLE ADD COLUMN.
- Actualizar dicha columnas con el mismo valor que tiene la columna IDENTITY, utilizando una sentencia UPDATE.
- Eliminar la columna IDENTITY, ejecutando un ALTER TABLE DROP COLUMN.
- Establecer la nueva columna como NOT NULL.
- Crear la Secuencia, estableciendo como valor inicial un valor superior a un SELECT MAX() de la nueva columna que hemos creado, para lo cual ejecutaremos una sentencia CREATE SEQUENCE.
- Crear una restricción DEFAULT sobre la nueva columna que obtenga automáticamente un valor de la Secuencia, ejecutando algo como: ALTER TABLE dbo.MiTable ADD CONSTRAINT DefSequence DEFAULT (NEXT VALUE FOR dbo.MySequence) FOR MiCampo
Como podemos ver, de este modo podemos utilizar la misma Secuencia para alimentar varias tablas, algo que no podríamos hacer con columnas IDENTITY, ya que la propiedad IDENTITY está vinculada a una columna específica de una tabla, por lo que necesitaríamos tener dos columnas con la propiedad IDENTITY (cada una de las cuales sería totalmente independiente). Como alternativa, podríamos crearnos una tabla de Contadores, de la cual nosotros mismos pudiéramos coger un valor (SELECT) e incrementarlos (UPDATE) antes de utilizarlo. Una solución más laboriosa, que se utilizas en muchas aplicaciones.
Las Secuencias también nos permiten poder obtener un bloque o conjunto de valores contiguos en un único paso, utilizando el procedimiento almacenado sp_sequence_get_range. Este procedimiento nos puede dar mucho juego en diferentes escenarios. Quizás, el único inconveniente de utilizar este procedimiento almacenado, es que nos devuelve el siguiente valor a asignar por la secuencia (y opcionalmente, el último valor del bloque que hemos solicitado), de tal modo que nosotros deberemos de buscarnos la vida para gestionar dicho bloque, quizás desde Transact-SQL, quizás desde una aplicación .Net, o desde donde fuere.
DECLARE @range_first_value sql_variant, @rangFirstVal sql_variant ;
EXEC sp_sequence_get_range @sequence_name = N'Test.RangeSeq', @range_size = 4, @range_first_value = @rangFirstVal OUTPUT ;
|
Otro detalle a tener en cuenta, es que tenemos disponible la vista del catálogo sys.sequences, que podremos consultar para obtener información detallada de configuración de las Secuencias existentes en nuestra Base de Datos.
Despedida y Cierre
Hasta aquí llega el presente artículo, en el cual hemos realizado una introducción general a las Secuencias en SQL Server 2012, así como hemos aprovechado para recordar ciertos detalles e inconvenientes de trabajar con las columnas IDENTITY, la principal alternativa que teníamos en versiones anteriores de SQL Server.
Poco más por hoy. Como siempre, confío que la lectura resulte de interés.
