Ler em inglês

Partilhar via

Números de sequência

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure

Este artigo explica como usar números de sequência no SQL Server, no Banco de Dados SQL do Azure e na Instância Gerenciada SQL do Azure. Uma sequência é um objeto vinculado ao esquema definido pelo usuário que gera uma sequência de valores numéricos de acordo com a especificação com a qual a sequência foi criada.

Visão geral

A sequência de valores numéricos é gerada em ordem crescente ou decrescente em um intervalo definido e pode circular (repetir) conforme solicitado. As sequências, ao contrário das colunas de identidade, não estão associadas a tabelas. Um aplicativo refere-se a um objeto de sequência para receber seu próximo valor. A relação entre sequências e tabelas é controlada pelo aplicativo. Os aplicativos de usuário podem fazer referência a um objeto de sequência e coordenar as chaves de valores em várias linhas e tabelas.

Uma sequência é criada independentemente das tabelas usando a instrução CREATE SEQUENCE. As opções permitem controlar o incremento, os valores máximos e mínimos, o ponto de partida, a capacidade de reinicialização automática e o cache para melhorar o desempenho. Para obter informações sobre as opções, consulte CREATE SEQUENCE.

Ao contrário dos valores de coluna de identidade, que são gerados quando as linhas são inseridas, uma aplicação pode obter o próximo número de sequência antes de inserir a linha chamando a função NEXT VALUE FOR. O número de sequência é alocado quando NEXT VALUE FOR é chamado, mesmo que o número nunca seja inserido em uma tabela. A função NEXT VALUE FOR pode ser usada como o valor padrão para uma coluna em uma definição de tabela. Use sp_sequence_get_range para obter uma gama de vários números sequenciais ao mesmo tempo.

Uma sequência pode ser definida como qualquer tipo de dados inteiros. Se o tipo de dados não for especificado, o padrão de uma sequência será bigint.

Usando sequências

Use sequências em vez de colunas de identidade nos seguintes cenários:

  • A aplicação requer um número antes de fazer a inserção na tabela.

  • O aplicativo requer o compartilhamento de uma única série de números entre várias tabelas ou várias colunas dentro de uma tabela.

  • O aplicativo deve reiniciar a série numérica quando um número especificado for atingido. Por exemplo, depois de atribuir valores de 1 a 10, o aplicativo começa a atribuir valores de 1 a 10 novamente.

  • O aplicativo requer que os valores de sequência sejam classificados por outro campo. A função NEXT VALUE FOR pode aplicar a cláusula OVER à chamada de função. A cláusula OVER garante que os valores retornados sejam gerados na sequência especificada pela cláusula ORDER BY dentro da cláusula OVER.

  • Um aplicativo requer que vários números sejam atribuídos ao mesmo tempo. Por exemplo, um aplicativo precisa reservar cinco números sequenciais. A solicitação de valores de identidade pode resultar em lacunas na série se outros processos estiverem a emitir números simultaneamente. Chamando sp_sequence_get_range pode recuperar vários números na sequência de uma só vez.

  • Você precisa alterar a especificação da sequência, como o valor de incremento.

Limitações

Ao contrário das colunas de identidade, cujos valores não podem ser alterados, os valores de sequência não são protegidos automaticamente após a inserção na tabela. Para evitar que os valores de sequência sejam alterados, use um gatilho de atualização na tabela para reverter as alterações.

A unicidade não é imposta automaticamente para valores de sequência. A capacidade de reutilizar valores de sequência foi concebida de propósito. Se for necessário que os valores de sequência em uma tabela sejam exclusivos, crie uma restrição exclusiva na coluna. Se for necessário que os valores de sequência em uma tabela sejam exclusivos em todo um grupo de tabelas, crie gatilhos para evitar duplicações causadas por instruções de atualização ou ciclos de números de sequência.

O objeto de sequência gera números de acordo com sua definição, mas o objeto de sequência não controla como os números são usados. Os números de sequência inseridos em uma tabela podem ter lacunas quando uma transação é revertida, quando um objeto de sequência é compartilhado por várias tabelas ou quando números de sequência são alocados sem usá-los em tabelas. Quando criado com a opção CACHE, um desligamento inesperado, como uma falha de energia, pode resultar na perda dos números de sequência no cache.

Se houver várias instâncias da função NEXT VALUE FOR especificando o mesmo gerador de sequência em uma única instrução Transact-SQL, todas essas instâncias retornarão o mesmo valor para uma determinada linha processada por essa instrução Transact-SQL. Esse comportamento é consistente com o padrão ANSI.

Os números de sequência são gerados fora do escopo da transação atual. Eles são consumidos independentemente de a transação que usa o número de série ser confirmada ou revertida. A validação duplicada só ocorre quando um registro é totalmente preenchido. Isso pode resultar em alguns casos em que o mesmo número é usado para mais de um registro durante a criação, mas depois é identificado como uma duplicata. Se isso ocorrer e outros valores de numeração automática tiverem sido aplicados aos registros subsequentes, isso pode resultar em uma lacuna entre os valores de numeração automática.

Uso típico

Para criar um número de sequência inteiro que aumente em 1 de -2.147.483.648 para 2.147.483.647, use a instrução a seguir.

SQL 
CREATE SEQUENCE Schema.SequenceName
    AS int
    INCREMENT BY 1 ;

Para criar um número de sequência inteiro semelhante a uma coluna de identidade que aumenta em 1 de 1 para 2.147.483.647, use a instrução a seguir.

SQL 
CREATE SEQUENCE Schema.SequenceName
    AS int
    START WITH 1
    INCREMENT BY 1 ;

Gerenciando sequências

Para obter informações sobre sequências, consulte sys.sequences.

Exemplos

Há exemplos adicionais nos artigos CREATE SEQUENCE, NEXT VALUE FORe sp_sequence_get_range.

Um. Usando um número de sequência em uma única tabela

O exemplo a seguir cria um esquema chamado Test, uma tabela chamada Orders e uma sequência chamada CountBy1 e, em seguida, insere linhas na tabela usando a função NEXT VALUE FOR.

SQL 
CREATE SCHEMA Test;
GO

CREATE TABLE Test.Orders
(
    OrderID INT PRIMARY KEY,
    Name VARCHAR (20) NOT NULL,
    Qty INT NOT NULL
);
GO

CREATE SEQUENCE Test.CountBy1
    START WITH 1
    INCREMENT BY 1;
GO

INSERT Test.Orders (OrderID, Name, Qty)
VALUES            ( NEXT VALUE FOR Test.CountBy1, 'Tire', 2);

INSERT test.Orders (OrderID, Name, Qty)
VALUES            ( NEXT VALUE FOR Test.CountBy1, 'Seat', 1);

INSERT test.Orders (OrderID, Name, Qty)
VALUES            ( NEXT VALUE FOR Test.CountBy1, 'Brake', 1);
GO

SELECT *
FROM Test.Orders;
GO

Aqui está o conjunto de resultados.

OrderID Name Qty

1 Tire 2

2 Seat 1

3 Brake 1

B. Chamando NEXT VALUE FOR antes da inserção de uma linha

Usando a tabela Orders criada no exemplo A, o exemplo a seguir declara uma variável chamada @nextIDe, em seguida, usa a função NEXT VALUE FOR para definir a variável para o próximo número de sequência disponível. Presume-se que o aplicativo faz algum processamento do pedido, como fornecer ao cliente o número OrderID de seu pedido potencial, e então valida o pedido. Não importa quanto tempo esse processamento possa levar ou quantos outros pedidos sejam adicionados durante o processo, o número original é preservado para uso por essa conexão. Finalmente, a instrução INSERT adiciona a ordem à tabela Orders.

SQL 
DECLARE @NextID AS INT;

SET @NextID =  NEXT VALUE FOR Test.CountBy1;

INSERT Test.Orders (OrderID, Name, Qty)
VALUES            (@NextID, 'Rim', 2);

C. Usando um número de sequência em várias tabelas

Este exemplo pressupõe que um processo de monitoramento de linha de produção receba notificação de eventos que ocorrem durante todo o workshop. Cada evento recebe um número de EventID único e monotonicamente crescente. Todos os eventos usam o mesmo número de sequência EventID para que os relatórios que combinam todos os eventos possam identificar exclusivamente cada evento. No entanto, os dados do evento são armazenados em três tabelas diferentes, dependendo do tipo de evento. O exemplo de código cria um esquema chamado Audit, uma sequência chamada EventCountere três tabelas que usam a sequência EventCounter como um valor padrão. Em seguida, o exemplo adiciona linhas às três tabelas e consulta os resultados.

SQL 
CREATE SCHEMA Audit;
GO

CREATE SEQUENCE Audit.EventCounter
    AS INT
    START WITH 1
    INCREMENT BY 1;
GO

CREATE TABLE Audit.ProcessEvents
(
    EventID INT DEFAULT ( NEXT VALUE FOR Audit.EventCounter) PRIMARY KEY CLUSTERED,
    EventTime DATETIME DEFAULT (getdate()) NOT NULL,
    EventCode NVARCHAR (5) NOT NULL,
    Description NVARCHAR (300) NULL
);
GO

CREATE TABLE Audit.ErrorEvents
(
    EventID INT DEFAULT ( NEXT VALUE FOR Audit.EventCounter) PRIMARY KEY CLUSTERED,
    EventTime DATETIME DEFAULT (getdate()) NOT NULL,
    EquipmentID INT NULL,
    ErrorNumber INT NOT NULL,
    EventDesc NVARCHAR (256) NULL
);
GO

CREATE TABLE Audit.StartStopEvents
(
    EventID INT DEFAULT ( NEXT VALUE FOR Audit.EventCounter) PRIMARY KEY CLUSTERED,
    EventTime DATETIME DEFAULT (getdate()) NOT NULL,
    EquipmentID INT NOT NULL,
    StartOrStop BIT NOT NULL
);
GO

INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
VALUES                      (248, 0);

INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
VALUES                      (72, 0);

INSERT Audit.ProcessEvents (EventCode, Description)
VALUES                    (2735, 'Clean room temperature 18 degrees C.');

INSERT Audit.ProcessEvents (EventCode, Description)
VALUES                    (18, 'Spin rate threshold exceeded.');

INSERT Audit.ErrorEvents (EquipmentID, ErrorNumber, EventDesc)
VALUES                  (248, 82, 'Feeder jam');

INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
VALUES                      (248, 1);

INSERT Audit.ProcessEvents (EventCode, Description)
VALUES                    (1841, 'Central feed in bypass mode.');

SELECT EventID,
       EventTime,
       Description
FROM Audit.ProcessEvents
UNION
SELECT EventID,
       EventTime,
       EventDesc
FROM Audit.ErrorEvents
UNION
SELECT EventID,
       EventTime,
       CASE StartOrStop WHEN 0 THEN 'Start' ELSE 'Stop' END
FROM Audit.StartStopEvents
ORDER BY EventID;

Aqui está o conjunto de resultados.

EventID EventTime Description

1 2009-11-02 15:00:51.157 Start

2 2009-11-02 15:00:51.160 Start

3 2009-11-02 15:00:51.167 Clean room temperature 18 degrees C.

4 2009-11-02 15:00:51.167 Spin rate threshold exceeded.

5 2009-11-02 15:00:51.173 Feeder jam

6 2009-11-02 15:00:51.177 Stop

7 2009-11-02 15:00:51.180 Central feed in bypass mode.

D. Gerando números de sequência repetidos em um conjunto de resultados

O exemplo a seguir demonstra dois recursos de números de sequência: ciclagem e uso de NEXT VALUE FOR numa instrução select.

SQL 
CREATE SEQUENCE CountBy5
    AS TINYINT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 5
    CYCLE;
GO

SELECT  NEXT VALUE FOR CountBy5 AS SurveyGroup,
       Name
FROM sys.objects;
GO

E. Gerando números de sequência para um conjunto de resultados usando a cláusula OVER

O exemplo a seguir usa a cláusula OVER para classificar o resultado definido por Name antes de adicionar a coluna de número de sequência.

SQL 
USE AdventureWorks2022;
GO

CREATE SCHEMA Samples;
GO

CREATE SEQUENCE Samples.IDLabel
    AS TINYINT
    START WITH 1
    INCREMENT BY 1;
GO

SELECT  NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID,
       ProductID,
       Name,
       ProductNumber
FROM Production.Product
WHERE Name LIKE '%nut%';

F. Redefinir o número de sequência

O exemplo E consumiu os primeiros 79 dos Samples.IDLabel números sequenciais. (Sua versão do AdventureWorks2022 pode retornar um número diferente de resultados.) Execute o seguinte para consumir os próximos 79 números de sequência (80 a 158).

SQL 
SELECT  NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID,
       ProductID,
       Name,
       ProductNumber
FROM Production.Product
WHERE Name LIKE '%nut%';

Execute a instrução a seguir para reiniciar a sequência de Samples.IDLabel.

SQL 
ALTER SEQUENCE Samples.IDLabel
RESTART WITH 1 ;

Execute a instrução select novamente para verificar se a sequência Samples.IDLabel foi reiniciada com o número 1.

SQL 
SELECT  NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID,
       ProductID,
       Name,
       ProductNumber
FROM Production.Product
WHERE Name LIKE '%nut%';

G. Alterar uma tabela de identidade para uma sequência

O exemplo a seguir cria um esquema e uma tabela contendo três linhas para o exemplo. Em seguida, o exemplo adiciona uma nova coluna e descarta a coluna antiga.

SQL 
CREATE SCHEMA Test;
GO

CREATE TABLE Test.Department
(
    DepartmentID SMALLINT IDENTITY (1, 1) NOT NULL,
    Name NVARCHAR (100) NOT NULL,
    GroupName NVARCHAR (100) NOT NULL CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED (DepartmentID ASC)
);
GO

INSERT Test.Department (Name, GroupName)
VALUES                ('Engineering', 'Research and Development');
GO

INSERT Test.Department (Name, GroupName)
VALUES                ('Tool Design', 'Research and Development');
GO

INSERT Test.Department (Name, GroupName)
VALUES                ('Sales', 'Sales and Marketing');
GO

SELECT *
FROM Test.Department;
GO

ALTER TABLE Test.Department
    ADD DepartmentIDNew SMALLINT NULL;
GO

UPDATE Test.Department
    SET DepartmentIDNew = DepartmentID;
GO

ALTER TABLE Test.Department DROP CONSTRAINT [PK_Department_DepartmentID];

ALTER TABLE Test.Department DROP COLUMN DepartmentID;
GO

EXECUTE sp_rename 'Test.Department.DepartmentIDNew', 'DepartmentID', 'COLUMN';
GO

ALTER TABLE Test.Department ALTER COLUMN DepartmentID SMALLINT NOT NULL;

ALTER TABLE Test.Department
    ADD CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED (DepartmentID ASC);

SELECT MAX(DepartmentID)
FROM Test.Department;

CREATE SEQUENCE Test.DeptSeq
    AS SMALLINT
    START WITH 4
    INCREMENT BY 1;
GO

ALTER TABLE Test.Department
    ADD CONSTRAINT DefSequence DEFAULT ( NEXT VALUE FOR Test.DeptSeq) FOR DepartmentID;
GO

SELECT DepartmentID,
       Name,
       GroupName
FROM Test.Department;

INSERT Test.Department (Name, GroupName)
VALUES                ('Audit', 'Quality Assurance');
GO

SELECT DepartmentID,
       Name,
       GroupName
FROM Test.Department;

Transact-SQL instruções que utilizam SELECT * receberão a nova coluna como última, em vez de como primeira. Se isso não for aceitável, você deverá criar uma tabela totalmente nova, mover os dados para ela e recriar as permissões na nova tabela.

Conteúdo relacionado