¿Cómo implementarías secuencias en Microsoft SQL Server?

¿Alguien tiene una buena manera de implementar algo así como una secuencia en el server SQL?

A veces simplemente no desea utilizar un GUID, además del hecho de que son feos como diablos. ¿Tal vez la secuencia que quieres no es numérica? Además, insert una fila y luego preguntarle a la DB qué número es, parece tan hambriento.

Sql Server 2012 ha introducido objects SEQUENCE , que le permiten generar valores numéricos secuenciales no asociados con ninguna tabla.

Crearlos es fácil:

 CREATE SEQUENCE Schema.SequenceName AS int INCREMENT BY 1 ; 

Un ejemplo de usarlos antes de la inserción:

 DECLARE @NextID int ; SET @NextID = NEXT VALUE FOR Schema.SequenceName; -- Some work happens INSERT Schema.Orders (OrderID, Name, Qty) VALUES (@NextID, 'Rim', 2) ; 

Ver mi blog para una mirada en profundidad sobre cómo usar secuencias:

http://sqljunkieshare.com/2011/12/11/sequences-in-sql-server-2012-implementingmanaging-performance/

Como dijo correctamente sqljunkieshare , a partir de SQL Server 2012 hay una característica SEQUENCE incorporada.

La pregunta original no aclara, pero supongo que los requisitos para la secuencia son:

  1. Tiene que proporcionar un set de numbers crecientes únicos
  2. Si varios usuarios solicitan el siguiente valor de la secuencia simultáneamente, todos deberían get valores diferentes. En otras palabras, la exclusividad de los valores generados está garantizada sin importar qué.
  3. Debido a la posibilidad de que algunas transactions se puedan retrotraer, es posible que el resultado final de los numbers generados tenga vacíos.

Me gustaría comentar la statement en la pregunta original:

"Además, insertando una fila y luego preguntando al DB qué número parece tan hackish".

Bueno, no hay mucho que podamos hacer al respecto aquí. El DB es un proveedor de los numbers secuenciales y DB maneja todos estos problemas de concurrency que usted no puede manejar usted mismo. No veo alternativa a preguntar al DB por el siguiente valor de la secuencia. Tiene que haber una operación atómica "dame el siguiente valor de la secuencia" y solo DB puede proporcionar tal operación atómica . Ningún código de cliente puede garantizar que él sea el único que trabaje con la secuencia.

Para responder a la pregunta en el título "¿Cómo implementarías secuencias?" – Estamos usando 2008, que no tiene la característica SEQUENCE , así que después de leer este tema terminé con lo siguiente.

Para cada secuencia que necesito, creo una tabla de ayuda por separado con solo una columna de IDENTITY (de la misma manera que en 2012 crearía un object de secuencia separado).

 CREATE TABLE [dbo].[SequenceContractNumber] ( [ContractNumber] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_SequenceContractNumber] PRIMARY KEY CLUSTERED ([ContractNumber] ASC) ) 

Puede especificar el valor inicial y el incremento para él. Luego creo un procedimiento almacenado que devolvería el siguiente valor de la secuencia. El procedimiento inicia una transacción, inserta una fila en la tabla auxiliar, restring el valor de identidad generado y revierte la transacción. Por lo tanto, la tabla de ayuda siempre permanece vacía.

 CREATE PROCEDURE [dbo].[GetNewContractNumber] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SET XACT_ABORT ON; DECLARE @Result int = 0; IF @@TRANCOUNT > 0 BEGIN -- Procedure is called when there is an active transaction. -- Create a named savepoint -- to be able to roll back only the work done in the procedure. SAVE TRANSACTION ProcedureGetNewContractNumber; END ELSE BEGIN -- Procedure must start its own transaction. BEGIN TRANSACTION ProcedureGetNewContractNumber; END; INSERT INTO dbo.SequenceContractNumber DEFAULT VALUES; SET @Result = SCOPE_IDENTITY(); -- Rollback to a named savepoint or named transaction ROLLBACK TRANSACTION ProcedureGetNewContractNumber; RETURN @Result; END 

Algunas notas sobre el procedimiento.

En primer lugar, no era obvio cómo insert una fila en una tabla que tiene una sola columna de identidad. La respuesta es DEFAULT VALUES .

Luego, quería que el procedimiento funcionara correctamente si se llamaba dentro de otra transacción. El ROLLBACK simple ROLLBACK todo si hay transactions anidadas. En mi caso, necesito retroceder solo INSERT en la tabla auxiliar, así que utilicé SAVE TRANSACTION .

ROLLBACK TRANSACTION sin un savepoint_name o transaction_name retrocede al inicio de la transacción. Al anidar las transactions, esta misma instrucción retrotrae todas las transactions internas a la instrucción BEGIN TRANSACTION más externa.

Así es como uso el procedimiento (dentro de otro procedimiento grande que, por ejemplo, crea un nuevo contrato):

 DECLARE @VarContractNumber int; EXEC @VarContractNumber = dbo.GetNewContractNumber; 

Todo funciona bien si necesita generar valores de secuencia de uno en uno. En el caso de los contratos, cada contrato se crea individualmente, por lo que este enfoque funciona a la perfección. Puedo estar seguro de que todos los contratos siempre tienen numbers de contrato únicos.

NB: solo para evitar posibles preguntas. Estos numbers de contrato son adicionales a la key de identidad sustituta que tiene mi tabla de contratos. La key sustituta es la key interna que se utiliza para la integridad referencel. El número de contrato generado es un número fácil de usar que está impreso en el contrato. Además, la misma tabla de Contratos contiene contratos finales y Propuestas, que pueden convertirse en contratos o pueden permanecer como propuestas para siempre. Tanto las Propuestas como los Contratos tienen datos muy similares, es por eso que se mantienen en la misma tabla. La propuesta puede convertirse en un contrato simplemente cambiando la bandera en una fila. Las propuestas están numeradas usando una secuencia separada de numbers, para la cual tengo una segunda tabla SequenceProposalNumber y un segundo procedimiento GetNewProposalNumber .


Recientemente, sin embargo, me encontré con un problema. Necesitaba generar valores de secuencia en un lote, en lugar de uno por uno.

Necesito un procedimiento que procese todos los pagos que se recibieron durante un trimestre determinado de una vez. El resultado de tal procesamiento podría ser ~ 20,000 transactions que quiero registrar en la tabla de Transactions . Tengo un layout similar aquí. Transactions tabla de Transactions tiene una columna de IDENTITY interna que el usuario final nunca ve y tiene un Número de transacción amigable para el hombre que se imprimiría en la statement. Entonces, necesito una forma de generar un número dado de valores únicos en un lote.

Esencialmente, utilicé el mismo enfoque, pero hay algunas peculiaridades.

Primero, no hay una forma directa de insert múltiples filas en una tabla con solo una columna de IDENTITY . Aunque hay una solución alternativa mediante (ab) el uso de MERGE , al final no la utilicé. Decidí que era más fácil agregar una columna de Filler ficticia. Mi tabla de Secuencia siempre estará vacía, por lo que la columna adicional realmente no importa.

La tabla de ayuda se ve así:

 CREATE TABLE [dbo].[SequenceS2TransactionNumber] ( [S2TransactionNumber] [int] IDENTITY(1,1) NOT NULL, [Filler] [int] NULL, CONSTRAINT [PK_SequenceS2TransactionNumber] PRIMARY KEY CLUSTERED ([S2TransactionNumber] ASC) ) 

El procedimiento se ve así:

 -- Description: Returns a list of new unique S2 Transaction numbers of the given size -- The caller should create a temp table #NewS2TransactionNumbers, -- which would hold the result CREATE PROCEDURE [dbo].[GetNewS2TransactionNumbers] @ParamCount int -- not NULL AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SET XACT_ABORT ON; IF @@TRANCOUNT > 0 BEGIN -- Procedure is called when there is an active transaction. -- Create a named savepoint -- to be able to roll back only the work done in the procedure. SAVE TRANSACTION ProcedureGetNewS2TransactionNos; END ELSE BEGIN -- Procedure must start its own transaction. BEGIN TRANSACTION ProcedureGetNewS2TransactionNos; END; DECLARE @VarNumberCount int; SET @VarNumberCount = ( SELECT TOP(1) dbo.Numbers.Number FROM dbo.Numbers ORDER BY dbo.Numbers.Number DESC ); -- table variable is not affected by the ROLLBACK, so use it for temporary storage DECLARE @TableTransactionNumbers table ( ID int NOT NULL ); IF @VarNumberCount >= @ParamCount BEGIN -- the Numbers table is large enough to provide the given number of rows INSERT INTO dbo.SequenceS2TransactionNumber (Filler) OUTPUT inserted.S2TransactionNumber AS ID INTO @TableTransactionNumbers(ID) -- save generated unique numbers into a table variable first SELECT TOP(@ParamCount) dbo.Numbers.Number FROM dbo.Numbers OPTION (MAXDOP 1); END ELSE BEGIN -- the Numbers table is not large enough to provide the given number of rows -- expand the Numbers table by cross joining it with itself INSERT INTO dbo.SequenceS2TransactionNumber (Filler) OUTPUT inserted.S2TransactionNumber AS ID INTO @TableTransactionNumbers(ID) -- save generated unique numbers into a table variable first SELECT TOP(@ParamCount) n1.Number FROM dbo.Numbers AS n1 CROSS JOIN dbo.Numbers AS n2 OPTION (MAXDOP 1); END; /* -- this method can be used if the SequenceS2TransactionNumber -- had only one identity column MERGE INTO dbo.SequenceS2TransactionNumber USING ( SELECT * FROM dbo.Numbers WHERE dbo.Numbers.Number <= @ParamCount ) AS T ON 1 = 0 WHEN NOT MATCHED THEN INSERT DEFAULT VALUES OUTPUT inserted.S2TransactionNumber -- return generated unique numbers directly to the caller ; */ -- Rollback to a named savepoint or named transaction ROLLBACK TRANSACTION ProcedureGetNewS2TransactionNos; IF object_id('tempdb..#NewS2TransactionNumbers') IS NOT NULL BEGIN INSERT INTO #NewS2TransactionNumbers (ID) SELECT TT.ID FROM @TableTransactionNumbers AS TT; END END 

Y así es como se usa (dentro de un gran procedimiento almacenado que calcula las transactions):

 -- Generate a batch of new unique transaction numbers -- and store them in #NewS2TransactionNumbers DECLARE @VarTransactionCount int; SET @VarTransactionCount = ... CREATE TABLE #NewS2TransactionNumbers(ID int NOT NULL); EXEC dbo.GetNewS2TransactionNumbers @ParamCount = @VarTransactionCount; -- use the generated numbers... SELECT ID FROM #NewS2TransactionNumbers AS TT; 

Aquí hay algunas cosas que requieren explicación.

Necesito insert un número dado de filas en la tabla SequenceS2TransactionNumber . Yo uso una tabla de Numbers auxiliares para esto. Esta tabla simplemente contiene numbers integers de 1 a 100,000. También se usa en otros lugares del sistema. Compruebo si hay suficientes filas en la tabla de Numbers y la expandí a 100,000 * 100,000 uniéndose entre sí si es necesario.

Tengo que save el resultado de la inserción masiva en algún lugar y pasarlo a la persona que llama de alguna manera. Una forma de pasar una tabla fuera del procedimiento almacenado es usar una tabla temporal. No puedo usar el parámetro con valores de tabla aquí, porque es de solo lectura desafortunadamente. Además, no puedo insert directamente los valores de secuencia generados en la tabla temporal #NewS2TransactionNumbers . No puedo usar #NewS2TransactionNumbers en la cláusula OUTPUT , porque ROLLBACK lo limpiará. Afortunadamente, las variables de tabla no se ven afectadas por el ROLLBACK .

Entonces, utilizo la variable de tabla @TableTransactionNumbers como destino de la cláusula OUTPUT . Luego ROLLBACK la transacción para limpiar la tabla de Secuencia. Luego copie los valores de secuencia generados de la variable de tabla @TableTransactionNumbers a la tabla temporal #NewS2TransactionNumbers , ya que solo la tabla temporal #NewS2TransactionNumbers puede ser visible para la persona que llama del procedimiento almacenado. La variable de tabla @TableTransactionNumbers no es visible para la persona que llama del procedimiento almacenado.

Además, es posible usar la cláusula OUTPUT para enviar la secuencia generada directamente a la persona que llama (como puede ver en la variante comentada que usa MERGE ). Funciona bien por sí mismo, pero necesitaba los valores generados en alguna tabla para su posterior procesamiento en el procedimiento almacenado de llamadas. Cuando intenté algo como esto:

 INSERT INTO @TableTransactions (ID) EXEC dbo.GetNewS2TransactionNumbers @ParamCount = @VarTransactionCount; 

Me estaba dando un error

No se puede usar la instrucción ROLLBACK dentro de una instrucción INSERT-EXEC.

Pero, necesito ROLLBACK dentro del EXEC , es por eso que terminé teniendo tantas tablas temporales.

Después de todo esto, qué bueno sería cambiar a la última versión del server SQL que tiene un object SEQUENCE adecuado.

Una columna de Identidad es más o less análoga a una secuencia.

Podrías usar tablas simples y usarlas como secuencias. Eso significa que tus insertos siempre serán:

 BEGIN TRANSACTION SELECT number from plain old table.. UPDATE plain old table, set the number to be the next number INSERT your row COMMIT 

Pero no hagas esto El locking sería malo …

Empecé en SQL Server y para mí, el esquema de "secuencia" de Oracle parecía un truco. Supongo que vienes de la otra dirección y hacia ti, y scope_identity () parece un truco.

Superalo. Cuando fueres haz lo que vieres.

La forma en que solía resolver este problema era una tabla 'Secuencias' que almacena todas mis secuencias y un 'próximo' procedimiento almacenado.

Tabla Sql:

 CREATE TABLE Sequences ( name VARCHAR(30) NOT NULL, value BIGINT DEFAULT 0 NOT NULL, CONSTRAINT PK_Sequences PRIMARY KEY (name) ); 

PK_Secuencias se usa solo para garantizar que nunca haya secuencias con el mismo nombre.

Procedimiento almacenado Sql:

 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'nextVal') AND type in (N'P', N'PC')) DROP PROCEDURE nextVal; GO CREATE PROCEDURE nextval @name VARCHAR(30) AS BEGIN DECLARE @value BIGINT BEGIN TRANSACTION UPDATE Sequences SET @value=value=value + 1 WHERE name = @name; -- SELECT @value=value FROM Sequences WHERE name=@name COMMIT TRANSACTION SELECT @value AS nextval END; 

Inserta algunas secuencias:

 INSERT INTO Sequences(name, value) VALUES ('SEQ_Workshop', 0); INSERT INTO Sequences(name, value) VALUES ('SEQ_Participant', 0); INSERT INTO Sequences(name, value) VALUES ('SEQ_Invoice', 0); 

Finalmente, obtenga el siguiente valor de una secuencia,

 execute nextval 'SEQ_Participant'; 

Cierto código c # para get el siguiente valor de la tabla de Secuencias,

 public long getNextVal() { long nextval = -1; SqlConnection connection = new SqlConnection("your connection string"); try { //Connect and execute the select sql command. connection.Open(); SqlCommand command = new SqlCommand("nextval", connection); command.CommandType = CommandType.StonetworkingProcedure; command.Parameters.Add("@name", SqlDbType.NVarChar).Value = "SEQ_Participant"; nextval = Int64.Parse(command.ExecuteScalar().ToString()); command.Dispose(); } catch (Exception) { } finally { connection.Dispose(); } return nextval; } 

En SQL Server 2012, puede simplemente usar

 CREATE SEQUENCE 

En 2005 y 2008, puede get una list arbitraria de numbers secuenciales utilizando una expresión de tabla común.

Aquí hay un ejemplo (tenga en count que la opción MAXRECURSION es importante):

 DECLARE @MinValue INT = 1; DECLARE @MaxValue INT = 1000; WITH IndexMaker (IndexNumber) AS ( SELECT @MinValue AS IndexNumber UNION ALL SELECT IndexNumber + 1 FROM IndexMaker WHERE IndexNumber < @MaxValue ) SELECT IndexNumber FROM IndexMaker ORDER BY IndexNumber OPTION (MAXRECURSION 0) 

Crea una tabla de etapas con un identificador.

Antes de cargar la tabla de etapas, trunque y reste el identificador para comenzar en 1.

Cargue su table Cada fila ahora tiene un valor único de 1 a N.

Crea una tabla que contenga numbers de secuencia. Esto podría ser varias filas, una para cada secuencia.

Busca el número de secuencia de la tabla de secuencias que creaste. Actualice el número de secuencia agregando el número de filas en la tabla de etapas al número de secuencia.

Actualice el identificador de la tabla de etapas agregando el número de secuencia al que lo miró. Este es un process sencillo de un paso. o Cargue su tabla de objectives, agregue el número de secuencia al identificador mientras carga en ETL. Esto puede aprovechar el cargador masivo y permitir otras transformaciones.

Considere el siguiente fragment.

 CREATE TABLE [SEQUENCE]( [NAME] [varchar](100) NOT NULL, [NEXT_AVAILABLE_ID] [int] NOT NULL, CONSTRAINT [PK_SEQUENCES] PRIMARY KEY CLUSTERED ( [NAME] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE PROCEDURE CLAIM_IDS (@sequenceName varchar(100), @howMany int) AS BEGIN DECLARE @result int update SEQUENCE set @result = NEXT_AVAILABLE_ID, NEXT_AVAILABLE_ID = NEXT_AVAILABLE_ID + @howMany where Name = @sequenceName Select @result as AVAILABLE_ID END GO 

Las secuencias implementadas por Oracle requieren una llamada a la database antes del inserto. las identidades implementadas por SQL Server requieren una llamada a la database después de la inserción.

Uno no es más hambriento que el otro. El efecto neto es el mismo: dependencia / dependencia en el almacén de datos para proporcionar valores únicos de keys artificiales y (en la mayoría de los casos) dos llamadas a la tienda.

Supongo que su model relacional se basa en keys artificiales, y en este context, ofreceré la siguiente observación:

Nunca debemos tratar de imbuir keys artificiales con significado; su único propósito debería ser vincular loggings relacionados.

¿Cuál es su necesidad relacionada con orderar datos? ¿se puede manejar en la vista (presentación) o es un verdadero atributo de sus datos que debe persistir?

Como afirma sqljunkiesshare , las secuencias se agregaron a SQL Server 2012. Aquí se explica cómo hacerlo en la GUI. Este es el error de:

 CREATE SEQUENCE Schema.SequenceName AS int INCREMENT BY 1 ; 
  1. En el Explorador de objects , expanda la carpeta Progtwigbilidad
  2. Debajo de la carpeta Progtwigbilidad , haga clic derecho en la carpeta Secuencias como se muestra a continuación:

enter image description here

  1. Subrayados están los valores que actualizaría para get el equivalente de la statement SQL anterior, sin embargo, consideraría cambiarlos según sus necesidades (ver notas a continuación).

enter image description here

Notas:

  • El valor de inicio pnetworkingeterminado, el valor mínimo y el valor máximo se determinaron por el range del tipo de datos que era un int en este caso. Consulte aquí para get más ranges de types de datos si desea usar algo que no sea int .

  • Es muy probable que quieras que tu secuencia comience en 1 y que también quieras que tu valor mínimo sea 1.

Estoy totalmente de acuerdo y lo hice el año pasado en un proyecto.

Acabo de crear una tabla con el nombre de la secuencia, el valor actual y la cantidad de incremento.

Luego creé un 2 procs para agregarlos y eliminarlos. Y 2 funciones para get siguiente, y ponerse al día.

Si desea insert datos con una key secuencial, pero no desea tener que consultar nuevamente la database para get la key recién insertada, creo que sus únicas dos opciones son:

  1. Realice la inserción a través de un procedimiento almacenado que devuelve el valor key recién insertado
  2. Implemente la secuencia del lado del cliente (para que sepa la nueva key antes de insertla)

Si estoy haciendo generación de keys en el lado del cliente, me encantan los GUID. Creo que son hermosos como diablos.

 row["ID"] = Guid.NewGuid(); 

Esa línea debería colocarse en el capó de un deportivo en alguna parte.

Si está utilizando SQL Server 2005, tiene la opción de utilizar Row_Number

El otro problema con las columnas de identidad es que si tiene más de una tabla donde los numbers de secuencia deben ser únicos, una columna de identidad no funciona. Y como menciona Corey Trager, un tipo de implementación de secuencia propia puede presentar algunos problemas de locking.

Las soluciones más sencillas equivalentes parecen ser la creación de una tabla de SQL Server con una sola columna para la identidad, que toma el lugar de un tipo separado de object de "secuencia". Por ejemplo, si en Oracle tendría dos tablas de una secuencia como Dogs <- sequence object -> Cats, en SQL Server crearía tres objects de database, todas las tablas como Dogs <- Pets with identity column -> Gatos. Debería insert una fila en la tabla Pets para get el número de secuencia donde normalmente usaría NEXTVAL y luego insertlo en la tabla Dogs o Cats como lo haría normalmente una vez que obtenga el tipo de mascota real del usuario. Cualquier columna común adicional podría trasladarse de las tablas Perros / Gatos a la tabla Supertipo de Mascotas, con algunas consecuencias que 1) habría una fila para cada número de secuencia, 2) cualquier columna que no pueda ser poblada al get el número de secuencia lo haría necesita tener valores pnetworkingeterminados y 3) requeriría una unión para get todas las columnas.

Por SQL puedes usar esta estrategia;

 CREATE SEQUENCE [dbo].[SequenceFile] AS int START WITH 1 INCREMENT BY 1 ; 

y lea el siguiente valor único con este SQL

 SELECT NEXT VALUE FOR [dbo].[SequenceFile]