Secuencias en SQL Server

Como crear Secuencias en SQL Server

Se puede definir una secuencia como un conjunto de valores que parten de un valor inicial, tienen un incremento o decremento, lo que significa que la secuencia puede ser ascendente o descendente y pueden tener un valor final.

SQL Server permite la creación de secuencias que pueden ser utilizadas para la generación de códigos en las tablas. Lo más importante de las secuencias es que no están ligadas a ningún campo en una tabla. Se recomienda usar la opción de Secuencias en lugar de usar la propiedad Identity, es necesario incidir en sugerir adicionalmente que no use la propiedad Identity.



Tipos de datos permitidos en secuencias

El tipo de dato de la secuencia es un dato Entero, los tipos de datos permitidos son

Tipo de dato Valores
Tinyint Rango 0 to 255
smallint Rango -32,768 to 32,767
int Rango -2,147,483,648 to 2,147,483,647
bigint Rango -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Este es el tipo de dato por defecto.

decimal y numeric con una escala de CERO.
Un tipo de dato definido por el usuario creado en base a los tipos anteriores.

(Ver tipos de datos definidos por el usuario)

La propiedad Identity

Identity es una propiedad que permite que un campo en una tabla incremente su valor de manera automática al insertar los registros en ella. Para el uso de la propiedad Identity el tipo de dato debe ser entero Int. Es necesario definir un valor inicial y un valor de incremento.

Es importante anotar que Identity no asegura la unicidad de valor, esta únicamente es posible con la restricciones Primary key, Unique o con el índice Unique. Solamente puede existir una columna por tabla con la propiedad Identidad. (Ver Identity)

Secuencia vs. Identity

En SQL Server se debe usar una secuencia en lugar de la propiedad Identity en los siguientes casos:

  • La aplicación requiere obtener el valor antes de insertar el registro.
  • La aplicación requiere compartir series de números entre multiples tablas o multiples columnas en las tablas.
  • La aplicación requiere reiniciar el valor de la serie con un valor especíifico. Por ejemplo, reiniciar una secuencia que fue creada desde 1 hasta 100 con los mismos valores.
  • La aplicación requiere valores que son ordenados por otro campo. La instrucción “NEXT VALUE FOR function” puede aplicarse la cláusula Over en la función de llamada.
  • Una aplicación requiere múltiples valores asignados al mismo tiempo. Por ejemplo, una aplicación necesita obtener tres números seguidos al mismo tiempo.

Como crear una secuencia en SQL Server

Instrucción Create Sequence
Crea una secuencia en SQL Server.

Create Sequence [Esquema. ] NombreDeSecuencia
[ AS [ TipoEntero | TipoEnteroDefinidoPorElUsuario ] ]
[ START WITH <constante> ]
[ INCREMENT BY <constante> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]

Donde
NombreDeSecuencia: es el nombre de la secuencia a crear.
TipoEntero: Tipo de dato entero de SQL Server. La tabla está definida líneas arriba.
TipoEnteroDefinidoPorElUsuario: Tipo de dato definido por el usuario en base a los números enteros de SQL Server. (Ver tipos de datos definidos por el usuario)
Start With: define el valor inicial
Increment by: Define el incremento o decremento.
MinValue: Especifica el valor mínimo, por defecto es CERO para el tipo tinyint y un valor negativo para el resto de tipos.
MaxValue: Especifica el valor máximo. El valor por defecto está definido de acuerdo al valor máximo del tipo de dato entero. (Ver tabla arriba)
Cycle: Permite que la secuencia se reinice cuando llega a su valor mínimo o máximo, dependiendo si es ascendente o descendente.

Ejercicios

Usando la base de datos Northwind

USE Northwind
go

1. Crear una secuencia con los valores por defecto

Create sequence ValoresPorDefecto
go

Para visualizar los datos de la secuencia
Select name, start_value, increment, maximum_value, minimum_value,
is_cycling, type, system_type_id, current_value
from sys.sequences where name = ‘ValoresPorDefecto’
go

Note que el ID del tipo de dato es 127, para visualizar el tipo de dato
select * from sys.types where system_type_id = 127
go

Para obtener el valor inicial de acuerdo al tipo de dato bigint. Tenga en cuenta que al ejecutar la
siguiente instrucción, el valor de la secuencia se va incrementando 1. En la tabla en la parte superior se puede visualizar el rango del tipo de dato binint.

select next value for ValoresPorDefecto
go

2. Crear una secuencia llamada EquipoBasket que inicia en 1 y termina en 12.

Create sequence EquipoBasket
As int
start with 1 increment by 1
minValue 1
maxvalue 12
Cycle
go

Visualizar los valores, ejecute mas de 12 veces la siguiente instrucción. Note que al llegar al valor
máximo se reinicia en el valor mínimo por la especificación de la cláusula Cycle.

select next value for EquipoBasket
go

Eliminar la secuencia
Drop sequence EquipoBasket
go

3. Crear una secuencia que permita especificar el código para los departamentos en una empresa.

Create sequence SecuenciaDepartamentos
As tinyint
Start With 1
INCREMENT By 1
go

Crear la tabla de Departamentos

Create Table Departamentos
(
DepartamentosCodigo tinyint,
DepartamentosDescripcion nvarchar(150),
constraint DepartamentosPK Primary key (DepartamentosCodigo)
)
go



Usar la secuencia para obtener el valor para el código usando NEXT VALUE FOR…
Insertar Departamentos

Insert into Departamentos (DepartamentosCodigo,DepartamentosDescripcion)
values (Next value FOR SecuenciaDepartamentos, ‘Gerencia General’)
go
Insert into Departamentos (DepartamentosCodigo,DepartamentosDescripcion)
values (Next value FOR SecuenciaDepartamentos, ‘Producción’)
go
Insert into Departamentos (DepartamentosCodigo,DepartamentosDescripcion)
values (Next value FOR SecuenciaDepartamentos, ‘Contabilidad’)
go

Consultar la tabla
Select * from Departamentos
go

Se sugiere crear algún algoritmo o código para generar un código que no sea únicamente números.

4. Ver las secuencias creadas en la base de datos

select * from sys.sequences
go

(adsbygoogle = window.adsbygoogle || []).push({});

Modificación de una Secuencia

Instrucción Alter Sequence

Modifica los argumentos de una secuencia existente.
Importante: para cambiar el tipo de dato numérico de la secuencia, esta se debe eliminar
y luego volver a crear con el nuevo tipo.

Sintaxis:

ALTER SEQUENCE [Esquema. ] NombreDeSecuencia
[ RESTART [ WITH <constant> ] ]
[ INCREMENT BY <constant> ]
[ { MINVALUE <constant> } | { NO MINVALUE } ]
[ { MAXVALUE <constant> } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]

Donde:
NombreDeSecuencia: es el nombre de la secuencia a modificar.
Restart With: define el valor en el que reiniciará la secuencia.
Increment by: Define el incremento o decremento.
MinValue: Especifica el valor mínimo, por defecto es CERO para el tipo tinyint y un valor negativo para el resto de tipos.
MaxValue: Especifica el valor máximo. El valor por defecto está definido de acuerdo al valor máximo del tipo de dato entero. (Ver tabla arriba)
Cycle: Permite que la secuencia se reinice cuando llega a su valor mínimo o máximo, dependiendo si es ascendente o descendente.

Ejercicios

5. Crear una secuencia con valores por defecto y luego modificarla para que su valor inicial sea 10 y se incremente  de 5 en 5

Create sequence PruebaCambio
go

Alter sequence PruebaCambio
restart with 10
increment by 5
go

Visualizar los valores de la secuencia

Select name, start_value, increment, maximum_value, minimum_value,
is_cycling, type, system_type_id, current_value
from sys.sequences where name = ‘PruebaCambio’
go

Eliminar un secuencia

Instrucción Drop Sequence
Elimina una secuencia de la base de datos

Sintaxis:
Drop sequence [Esquema.]NombreSecuencia

Donde:
Esquema: es el nombre del esquema donde se encuentra la secuencia. (Ver esquemas)
NombreSecuencia: nombre de la secuencia a eliminar.

Ejercicios

6. Eliminar la secuencia PruebaCambio

Drop sequence PruebaCambio
go