Secuencias en SQL Server

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. Además se poder crear secuencias cíclicas, es decir, secuencias que al llegar a su valor final se reinician a su valor inicial.

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.

Tipos de datos permitidos en secuencias

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

  • 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

Características

  • Identity es una propiedad que permite que un campo de tipo int 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 multiples valores asignados al mismo tiempo. Por ejemplo, una aplicación necesita obtener tres números seguidos al mismo tiempo.

USE Northwind
go

Creación de una secuencia

Instrucción Create Sequence
Crea una secuencia en SQL Server.
Create Sequence [Esquema. ] NombreDeSecuencia
[ AS [ TipoEntero | TipoEnteroDefinidoPorElUsuario ] ]
[ START WITH ]
[ INCREMENT BY ]
[ { MINVALUE [ ] } | { NO MINVALUE } ]
[ { MAXVALUE [ ] } | { 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

Ejemplo 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.
select next value for ValoresPorDefecto
go

Ejemplo 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

Ejemplo 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

Ejemplo 4

Ver las secuencias creadas en la base de datos
select * from sys.sequences
go

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 ] ]
[ INCREMENT BY ]
[ { MINVALUE } | { NO MINVALUE } ]
[ { MAXVALUE } | { 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.

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

Ejemplo 6

Eliminar la secuencia PruebaCambio
Drop sequence PruebaCambio
go