GuilleSQL :: Microsoft SQL Server, SSIS, y más !!

Secuencias en SQL Server 2012: Bienvenidas


Una de las nuevas aportaciones de SQL Server 2012 son las Secuencias, que nos permiten definir una secuencia de valores para poder utilizar al insertar filas sobre una o varias tablas, resultando una muy interesante alternativa a la utilización de columnas con la propiedad IDENTITY o incluso de Tablas de Contadores, y que además ayudará a facilitar las migraciones de ORACLE a SQL Server. El presente artículo describe esta nueva característica de SQL Server 2012 y recuerda algunos inconvenientes del tradicional IDENTITY.

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.

 


[Fecha del Artículo (UTC): 05/06/2013]
[Autor: GuilleSQL]



Escribir un Comentario

Para poder escribir un comentario, debe Iniciar Sesión con un usuario.

Si no dispone de un usuario, puede Registrarse y hacerse miembro.

Si dispone de un usuario, pero no recuerda sus credenciales de acceso, puede Restablecer su Contraseña.

Miembros de
Miembros de GITCA (Global IT Community Association)

Menu de Usuario
  Iniciar Sesión
  Registrarse
  Restablecer Contraseña
  Ventajas de Registrarse

Acerca de
  Contigo desde Oct 2007
  771 usuarios registrados
  86146 pageloads/mes
  Ranking Alexa 498160

Social Networks
Sigue a Portal GuilleSQL en Linkedin !!
Sigue a Portal GuilleSQL en Twitter !!



Archivo

Junio de 2017 (3)
Mayo de 2017 (1)
Marzo de 2017 (3)
Enero de 2017 (4)
Junio de 2016 (1)
Mayo de 2016 (2)
Abril de 2016 (2)
Septiembre de 2015 (2)
Agosto de 2015 (2)
Junio de 2015 (10)
Mayo de 2015 (4)
Abril de 2015 (8)
Marzo de 2015 (11)
Octubre de 2014 (3)
Septiembre de 2014 (7)
Agosto de 2014 (5)
Julio de 2014 (2)
Mayo de 2014 (4)
Abril de 2014 (4)
Marzo de 2014 (4)
Febrero de 2014 (1)
Enero de 2014 (5)
Diciembre de 2013 (8)
Noviembre de 2013 (2)
Octubre de 2013 (7)
Septiembre de 2013 (6)
Agosto de 2013 (1)
Julio de 2013 (6)
Junio de 2013 (11)
Mayo de 2013 (7)
Abril de 2013 (6)
Febrero de 2013 (5)
Enero de 2013 (7)
Diciembre de 2012 (12)
Noviembre de 2012 (13)
Octubre de 2012 (5)
Septiembre de 2012 (3)
Agosto de 2012 (6)
Julio de 2012 (4)
Junio de 2012 (1)
Mayo de 2012 (2)
Abril de 2012 (7)
Marzo de 2012 (16)
Febrero de 2012 (9)
Enero de 2012 (5)
Diciembre de 2011 (10)
Noviembre de 2011 (10)
Octubre de 2011 (4)
Septiembre de 2011 (5)
Agosto de 2011 (2)
Julio de 2011 (2)
Junio de 2011 (4)
Mayo de 2011 (2)
Abril de 2011 (6)
Marzo de 2011 (4)
Febrero de 2011 (10)
Enero de 2011 (5)
Diciembre de 2010 (6)
Noviembre de 2010 (4)
Octubre de 2010 (8)
Septiembre de 2010 (4)
Agosto de 2010 (1)
Julio de 2010 (3)
Mayo de 2010 (5)
Abril de 2010 (6)
Marzo de 2010 (8)
Febrero de 2010 (3)
Enero de 2010 (1)
Diciembre de 2009 (9)
Noviembre de 2009 (14)
Octubre de 2009 (2)
Septiembre de 2009 (8)
Agosto de 2009 (2)
Julio de 2009 (10)
Junio de 2009 (9)
Mayo de 2009 (10)
Abril de 2009 (9)
Marzo de 2009 (3)
Febrero de 2009 (2)
Enero de 2009 (3)
Noviembre de 2008 (2)
Octubre de 2008 (2)
Septiembre de 2008 (2)
Agosto de 2008 (5)
Julio de 2008 (5)
Junio de 2008 (1)
Mayo de 2008 (3)
Abril de 2008 (2)
Marzo de 2008 (2)
Febrero de 2008 (2)
Enero de 2008 (5)
Noviembre de 2007 (2)
Octubre de 2007 (2)






Esta información se proporciona "como está" sin garantías de ninguna clase, y no otorga ningún derecho.
This information is provided "AS IS" with no warranties, and confers no rights.

Copyright © 2007 GuilleSQL, todos los derechos reservados.
GuilleSQL.com y GuilleSQL.net son también parte de Portal GuilleSQL.

Visitas recibidas (Page Loads) en GuilleSQL (fuente: StatCounter):

screen resolution stats
Visitas