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.