SQL Server 2012 - Trabajando con Sequences y comparaciones con IDENTITY

¡Hola, chicos!
¡Buen día!

Hoy me encontré con una situación en la que el uso de secuencias, viejas conocidas de Oracle y que a partir de SQL Server 2012 fueron introducidas en el DBMS de Microsoft, vi una solución perfecta al problema al que me enfrentaba.

Introducción: detallando el problema

Este problema consistía en una tabla con decenas de millones de registros y la cual tiene un número secuencial para controlar y unicidad del registro y ahora existía la necesidad de crear otro número secuencial en esta misma tabla, independiente, donde los registros existentes debían recibir NULL y que comenzaría en 1, para controlar otro tipo de información y ser llenado solo cuando ocurre un determinado evento (diferente al número secuencial ya existente, que se genera con cada inserción en la tabla)

Otra situación que se debe evitar a toda costa es que no puedan dos sesiones tomar la misma secuencia y escribir en la tabla y esta posibilidad es bastante posible, ya que esta tabla tiene varias inserciones en sesiones paralelas por segundo.

En este caso, el uso de IDENTIDAD no sería posible, ya que IDENTIDAD se aplica a todos los registros de la tabla, lo que no sería el caso. El uso de estructuras de ranking como ROW_NUMBER podría incluso ser viable, si no fuera tan engorroso calcular este ranking con cada nueva inserción (además de la posibilidad de que dos sesiones obtengan la misma secuencia).

Diferencias entre secuencia e IDENTIDAD

Cómo crear una secuencia

Para solucionar el problema descrito anteriormente, tuve la idea de crear una secuencia, que es un objeto de base de datos especialmente creado para este tipo de necesidad. A diferencia de IDENTITY, puedes usar más de una secuencia en la tabla y los registros anteriores no se modifican. Estas fueron las razones que me llevaron a utilizar esta función de SQL Server para resolver esta situación.

Veamos ahora cómo crear una secuencia:

CREATE SEQUENCE dbo.[seq_Teste]
AS [INT]
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 999999999
    CYCLE
    CACHE
GO

Parámetros explicados:

• EMPEZAR CON: Define el número inicial de la secuencia.
• MINVALUE y MAXVALUE: Delimita el límite de SECUENCIA con su respectivo valor máximo y mínimo. Si el valor no está definido, se asignará el valor máximo y mínimo del tipo de dato elegido.
• INCREMENTAR POR: Define la cantidad que se incrementará en la secuencia. En el ejemplo anterior, el incremento será de 1 en 1.
• CICLO: La propiedad CICLO permite iniciar nuevamente un ciclo desde el momento en que se alcanzan las propiedades MINVALUE y MAXVALUE. Es decir, al alcanzar el valor definido en MAXVALUE, la secuencia comenzará nuevamente en el valor del parámetro MINVALUE (cuando esto ocurra, se generarán valores duplicados en la secuencia, pues ya se ha cubierto todo el rango)
• CACHE: Al usar este parámetro, SQL Server preasigna números de secuencia a través de la propiedad CACHE, siendo el valor predeterminado para esto 15, lo que significa que los siguientes 15 valores disponibles se asignarán en la memoria hasta que se usen y la secuencia ya trata estos números como usados. Cuando se utilizan todos los números CACHE, se vuelven a asignar 15 valores nuevos en la memoria y así continúa el ciclo. Vale la pena recordar que si se reinicia la instancia, los números en el caché se pierden y este "agujero" permanece en la secuencia.

Cómo devolver el siguiente número en una secuencia

Devolver el siguiente número de una secuencia es una tarea muy sencilla:

SELECT NEXT VALUE FOR seq_Teste

Sin embargo, debes tener en cuenta que a diferencia de IDENTITY, para cada inserción debes llamar a secuencia para que devuelva la secuencia y así insertarla en la tabla o crear una restricción predeterminada para automatizar esta tarea.

Cómo devolver el siguiente número de una secuencia automáticamente

Aunque la mayoría de las personas (incluido yo mismo) usan la secuencia manualmente para cada inserción para devolver la siguiente secuencia, esto se puede automatizar usando una RESTRICCIÓN PREDETERMINADA en la tabla:

CREATE TABLE dbo.Teste_Sequence (
    Id INT DEFAULT NEXT VALUE FOR dbo.seq_Teste,
    Nome VARCHAR(100)
)

Ejemplo:

Cómo restablecer el valor de la secuencia

En ocasiones, es necesario restablecer el contador de secuencia o cambiarlo a un valor específico. Para hacer esto, podemos usar ALTER SEQUENCE para esta tarea:

ALTER SEQUENCE seq_Teste RESTART WITH 1

En el ejemplo anterior, estamos restableciendo el valor de la secuencia a 1.

Ejemplo:

Recuperar el valor actual de la secuencia, sin aumentar la secuencia

Para recuperar el valor actual de la secuencia, simplemente consulte la vista sys.sequences:

SELECT current_value 
FROM sys.sequences
WHERE name = 'seq_Teste'

Cómo cambiar una secuencia

El cambio de una secuencia sigue los mismos parámetros que la creación y se puede cambiar en cualquier momento.

Ejemplos:

ALTER SEQUENCE seq_Teste MAXVALUE 99999
ALTER SEQUENCE seq_Teste CACHE

Cómo eliminar una secuencia

Eliminar una secuencia en la base de datos de SQL Server es tan simple como cualquier otro objeto de base de datos y se puede hacer usando la declaración DROP:

DROP SEQUENCE dbo.Sua_Sequence
GO

Permisos de secuencia

Como ya se mencionó, las secuencias son objetos independientes en la base de datos y por lo tanto también tienen permisos independientes:

CREAR SECUENCIA: Para crear una secuencia, debe tener el permiso CREATE SEQUENCE, ALTER o CONTROL en el esquema. Los usuarios de los roles db_owner y db_ddladmin pueden crear, cambiar y eliminar secuencias y los usuarios de los roles db_owner y db_datawriter pueden usar la secuencia para devolver el siguiente número de la secuencia.

Ejemplo de subvención:

GRANT CREATE SEQUENCE ON SCHEMA::dbo TO [DOMINIO\usuario]

ALTERAR SECUENCIA: Para cambiar una secuencia, debe tener permiso ALTER en el esquema.

Ejemplo de subvención:

GRANT ALTER ON OBJECT::dbo.Sua_Sequence TO [DOMINIO\usuario]

SECUENCIA DE CAÍDA: Para eliminar una secuencia, debe tener permiso ALTER o CONTROL en el esquema.

Demostración de una secuencia única para más de una tabla.

Código fuente de prueba

CREATE SEQUENCE dbo.[seq_Pessoa]
AS [INT]
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 999999999
    CYCLE
    CACHE
GO

CREATE TABLE dbo.Pessoa_Fisica (
    Id INT DEFAULT NEXT VALUE FOR dbo.seq_Pessoa,
    Nome VARCHAR(100),
    CPF VARCHAR(11)
)

CREATE TABLE dbo.Pessoa_Juridica (
    Id INT DEFAULT NEXT VALUE FOR dbo.seq_Pessoa,
    Nome VARCHAR(100),
    CNPJ VARCHAR(14)
)

INSERT INTO dbo.Pessoa_Fisica (Nome, CPF)
VALUES('Dirceu Resende', '11111111111')

INSERT INTO dbo.Pessoa_Juridica (Nome, CNPJ)
VALUES('Dirceu Resende Ltda', '22222222222222')

INSERT INTO dbo.Pessoa_Fisica (Nome, CPF)
VALUES('Dirceu Resende 2', '33333333333')

INSERT INTO dbo.Pessoa_Juridica (Nome, CNPJ)
VALUES('Dirceu Resende ME', '44444444444444')

SELECT * FROM dbo.Pessoa_Fisica
SELECT * FROM dbo.Pessoa_Juridica

Resultado

¡Eso es todo, amigos!
Espero que os haya gustado y hasta el próximo post.

Nota: ¿Necesita utilizar una secuencia en una función definida por el usuario, ya sea escalar, agregada o con valores de tabla, y no puede hacerlo? Mira la solución en el post. Usar secuencias en funciones definidas por el usuario en SQL Server 🙂

secuencia del servidor SQL cómo utilizar el trabajo reiniciar restablecer crear eliminar cambiar volver obtener el valor actual siguiente valor

secuencia del servidor SQL cómo utilizar el trabajo reiniciar restablecer crear eliminar cambiar volver obtener el valor actual siguiente valor