Sequence diferencias y similitudes con Identity


Siempre a sido necesario tener unicidad en SQL Server, muchos desde los tiempos que aprendimos en la escuela usamos identity, a partir de SQL Server 2012 también contamos con la opción de usar sequence, ambas tienen básicamente la misma función. Y son usadas para crear comúnmente llaves primarias buscando una "secuencia" unica.

Pero cual es la diferencia de estos objetos en el cual el identity es una propiedad de la tabla, como podemos apreciar en la documentación de sequence es un objeto que no reside en la tabla.

1
2
3
4
5
6
7
8
9
CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH <constant> ]
    [ INCREMENT BY <constant> ]
    [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
    [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [ <constant> ] } | { NO CACHE } ]
    [ ; ]


Estos son los argumentos con los que se crea una secuencia, se puede usar cualquier tipo de datos numérico y un tipo de dato definido por el usuario, sino se especifica un dato se usara BigInt, que normalmente debe de estar reservado solamente para casos de BigData.

Tomemos los argumentos que son:

  • Tipo de datos: TINYINT, SMALLINT, INT, BIGINT, DECIMAL y NUMERIC
  • Inicio: Valor de inicio
  • Incremento en: Se incrementa en una cantidad n de números
  • Valor mínimo: El valor mínimo de la secuencia
  • Valor máximo: El valor máximo de la secuencia
  • Ciclo: Si al llegar al máximo se reinicia la secuencia
  • Cache: Para evitar hacer consultas a disco se tiene un cache en memoria definido al momento de creación o por default lo determina SQL Server, si el servicio se detiene por cualquier motivo es posible que se pierdan los valores que ya estaban apartados en el cache.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
USE AdventureWorks2014
GO

CREATE SEQUENCE dbo.ejemploSecuencia AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000000

DROP TABLE dbo.Series
GO 

CREATE TABLE dbo.Series(
 idIdentity INT  IDENTITY(1,1),
 idSequence INT  DEFAULT NEXT VALUE FOR dbo.ejemploSecuencia PRIMARY KEY,
 text  NVARCHAR(10))

DECLARE @count INT
ALTER SEQUENCE ejemploSecuencia RESTART WITH 1
SET @count = 1
WHILE @count < 100
BEGIN
 IF @count%7=0
  BEGIN
   BEGIN TRAN
    INSERT INTO dbo.Series (text)
    VALUES (N'ejemplo')
   ROLLBACK TRAN
  END 
  --PRINT 'Valores invalidos'''
 ELSE
  INSERT INTO dbo.Series (text)
  VALUES (N'ejemplo')
SET @count = @count + 1
END 
GO

En este ejemplo vamos a crear una secuencia de 1,000,000 de registros, luego creamos una tabla con una secuencia y un identity que se incrementan de la misma manera. Tomamos while que haga un modulo de 7 y haga un rollback, como todos sabrán un identity cuando se hace un rollback ese número es usado internamente y no puede ser recuperado lo cual crea un hueco en la numeración, podrán apreciar al correr este ejemplo que el mismo comportamiento existe en sequence, aunque esto puede ser controlado en código por medio sql en código.

1
2
3
4
INSERT INTO dbo.Series
        ( idSequence, text )
VALUES  ( NEXT VALUE FOR dbo.ejemploSecuencia OVER (ORDER BY indice)
          N''  -- text - nvarchar(10))

Para re inicializar una secuencia


1
ALTER SEQUENCE ejemploSecuencia RESTART WITH 1

Las secuencias son un poco mas rápidas que el idenitity aun sin cache, la secuencia es mas difícil que comentan saltos como lo fue el caso de los saltos de 1000 en mil en SQL Server 2012 y pueden programarse para que sigan un orden determinado, las secuencias  pertenecen al formato ANSI por lo que es un standard mas reconocido.

Comentarios