Melhorias do T-SQL no SQL Server 2012 (Parte 1)

Posted on março 21, 2012

7


Introdução

 

Nesta série de Melhorias do T-SQL no SQL Server 2012, trarei toda semana um novo recurso. Nesta semana iremos falar sobre o novo objeto chamado SEQUENCE.

 

O Sequence nada mais é do que um objeto que realiza a geração de sequências de valores númericos em forma ascendente 0u descendente. O Sequence diferente do Idenitity não é vinculado por padrão a nenhuma tabela podendo pertencer a uma ou mais tabelas. Uma dos grandes ganhos de performance que temos é que, o identity necessita que um registro seja inserido para que se possa gerar um novo número, já o sequence é controlado por uma aplicação sendo assim não é necessário que seja inserido nenhum registro na tabela, basta apenas que seja especificado NEXT VALUE FOR  para que assim o próximo valor seja gerado.

 

Nomenclatura e Opções de Criação do Objeto

Para a criação desse objeto dentro do banco de dados é requerido a seguinte sintaxe:

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 } ]
    [ ; ]

Start With

O primeiro valor que será gerado na criação desse objeto ou seja a partir do valor especificado.

 

Increment By

Depois do primeiro valor inserido, qual será o incremento a partir deste valor.

 

MinValue /NoMinValue

O valor mínimo que poderá ser inserido, se não especificado por padrão será padrão a opção NoMinValue ou seja valor não especificado.

 

MaxValue /NoMaxValue

O valor máximo no qual o sequence pode chegar, se não especificado por padrão será padrão a opção NoMaxValue ou seja valor não especificado.

 

Cycle/No Cycle

Por padrão NoCycle, porém se especificado, essa propriedade faz com que a partir do valor especificado o Sequence realize um restart em seu incremento, começando novamente a partir do 1 número que está na cláusula Start Value.

 

Cache/No Cache

Aumenta a performance durante a criação do Sequence, minimizando assim custos de I/o referente aos cálculos necessários para a geração do valor incremental. Ou seja se o Cache for especificado como 100 o SQL Server manterá em cache 100 valores, sendo assim na hora em que for necessário buscar outro valor, a Engine não precisa realizar cálculos, somente buscar o dados que já está calculado no cache.

 

Pontos Positivos

 

Vale resaltar que:

 

* Objeto definido pelo usuário

Diferente do Identity que não podiamos configurá-lo, o Sequence no traz diversas opções de controle gerando assim uma maior facildade de uso e abstração desse novo recurso.

 

* Não vinculado a nenhuma tabela

É possível que o Sequence seja compartilhado em diversas tabelas, assim temos um maior controle e flexibilidade na hora da administração dos registros.

 

* De fácil migração entre instâncias 

Sendo um objeto dentro do banco de dados, sua migração é facilitada.

Demo

 

Demo1

 

Aqui veremos o seu modo de utilização. Iremos criar um banco de dados chamado TSQLImprovements e logo após criaremos um Sequence para a gestão dos valores incrementais das tabelas.

 

CREATE DATABASE TSQLImprovements

go

 

USE TSQLImprovements

GO

 

CREATE SEQUENCE SQ_ID

AS INT

     START WITH 1

     INCREMENT BY 1;

 

Agora criaremos 2 tabelas uma de Produtos e Consultores.

 

CREATE TABLE Produtos

(

   ID INT NOT NULL PRIMARY KEY,

   Nome VARCHAR(30) NOT NULL,

   DataRegistro DATETIME2 DEFAULT GETDATE()

)

 

CREATE TABLE Consultores

(

  ID BIGINT NOT NULL PRIMARY KEY,

  Nome VARCHAR(100) NOT NULL,

  Sobrenome VARCHAR(150) NOT NULL,

  DataRegistro DATETIME2 DEFAULT GETDATE()

)

 

Para que seja possível utilizar o Sequence para adicionar valores incrementais na tabela, o insert deverá conter a clásula – (NEXT VALUE FOR NomeSequence), assim temos:

 

INSERT INTO Produtos (ID, Nome)

VALUES (NEXT VALUE FOR SQ_ID, ‘Carro’)

 

INSERT INTO Consultores (ID, Nome, Sobrenome)

VALUES (NEXT VALUE FOR SQ_ID, ‘Luan Moreno’, ‘Medeiros Maciel’)

image

 

Os valores foram inseridos de acordo com o Sequence de nome SQ_ID, sendo assim o primeiro valor inserido foi 1 logo o subsequente foi o 2. Porém desse modo todas as vezes que precisarmos utilizar o sequence teremos que especificá-lo. Para que o Sequence faça parte de uma tabela e toda a vez que esta tabela conter uma inserção for gerado um novo Sequence,  especificamos  na propriedade da tabela uma default constraint. Criando a tabela empresa veremos como isso funciona.

 

CREATE TABLE Empresa

(

ID BIGINT NOT NULL PRIMARY KEY DEFAULT (NEXT VALUE FOR SQ_ID),

Nome VARCHAR(100) NOT NULL,

DataRegistro DATETIME2 DEFAULT GETDATE()

)

 

Durante a criação do campo ID foi especificado o DEFAULT (NEXT VALUE FOR SQ_ID), sendo assim todas as vezes que for inserido um novo valor o sequence irá criar automaticamente um número increnmental, adicionando um valor vemos que:

 

INSERT INTO Empresa(Nome)

VALUES (‘Conselho Federal da OAB’)

 

image

 

 

Demo2

Aqui veremos como realizar a criação de um Sequence utilizando todas suas opções e analisaremos um dos cenários que podem ocorrer quando utilizamos esse novo recurso.

 

USE TSQLImprovements

go

 

CREATE SEQUENCE dbo.GenerateDados

    AS INT

     INCREMENT BY 1

    MINVALUE 1

    START WITH 1

     NO MAXVALUE

     NO CACHE

     NO CYCLE;

GO

CREATE TABLE DadosSequence

(

     ID INT NOT NULL,

     Nome VARCHAR(100)

)

 

CREATE TABLE DadosSequencePrimaryKey

(

     ID INT NOT NULL PRIMARY KEY,

     Nome VARCHAR(100)

)

Note que uma tabela possui Primary Key, ou seja não podemos ter valores duplicados nela, sendo assim iremos realizar algumas inserçõe e logo após isso zerar o contador do Sequence

 

SET NOCOUNT ON;

 

INSERT INTO DadosSequence (ID, Nome)

VALUES (NEXT VALUE FOR GenerateDados, ‘InsertDadosSequence’)

GO 10

 

INSERT INTO DadosSequencePrimaryKey (ID, Nome)

VALUES (NEXT VALUE FOR GenerateDados, ‘InsertDadosSequencePrimaryKey’)

GO 10

image

 

ALTER SEQUENCE dbo.GenerateDados

     RESTART WITH 1;

 

Agora se tentarmos realizar novas inserções teremos…

 

(dbo.DadosSequence) – Sem Primary Key

image

 

(dbo.DadosSequencePrimaryKey) – Com Primary Key

image

Quero mostrar que devemos ter muito cuidado quando pensarmos em reiniciar um Sequence, não é porque temos a opção de Reiniciar o Seed que devemos fazer isso sem pensar, pelo Sequence ser um objeto compartilhado, podemos ter esse tipo de cenário, como não podemos inserir um dado duplicado na tabela que possui uma unique constraint, o erro acima é mostrado como uma violação de chave.

Dmv Sequence

 

Para obter informações dos sequences criados dentro de um banco de dados utilizamos uma das novas DMV’s sys.sequences.

 

USE TSQLImprovements

go

 

SELECT *

FROM sys.sequences

image