Novidade – SQL Server 2016 – Comando Truncate Table agora com With Partitions

Boa tarde, boa tarde, pessoal!!!

Boa tarde, amantes do SQL Server e Comunidade de Banco de Dados.

Nos últimos dias a Microsoft confirmou a data de lançamento da nova versão do SQL Server, estou se referindo ao Microsoft SQL Server 2016 que será lançado oficial no dia 01 de Junho com toda força de um produto mais que consolidado na área de tecnologia, na verdade o líder no seguimento de banco de dados mundia, segundo as informações do Gartners.

Lógicamente com este lançamento se aproximando todas as possíveis novidades, segredos e melhorias criadas pelos times de engenheiros e desenvolvimento do SQL Server estão sendo divulgadas massisamente nos principais blogs e sites de bancos de dados, como de costume eu também vou tentando surfar nesta onda de lançamento e trazendo aos poucos algumas coisinhas que eu venho conhecendo e utilizando nas últimas CTPs e posteriormente as RCs liberadas pela Microsoft em relação ao SQL Server 2016.

Vale ressaltar que a última RC nomeada RC 3 esta disponível para download através do link: https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016. Aproveitando-se desta onda de lançamento do SQL Server 2016. No artigo de hoje quero destacar uma novidade que a anos a Microsoft estava devendo principalmente para os administradores de banco de dados, estou fazendo referência ao comando Truncate Table e a partir de agora a sua capacidade de remover todas as linhas de uma determinada tabela alocadas em uma partição específica.

Introdução

Qual foi a última vez que você utilizou o comando Truncate Table? Você começa a pensar e se lembra que pode ter sido ontem, antes de ontem, uma semana atrás, um mês atrás, um anos atrás, nossa você para, pensa, observa e chega a seguinte conclusão, meu deus como tempo esta passando e um ainda uso este comando que por aproximadamente mais de 16 anos não passou por nenhuma mudança, é realmente isso aconteceu e por incrível que parece aconteceu justamente com o comando Truncate Table que na minha opinião é um dos mais básicos e não menos importante comandos existentes dentro da linguagem Transact-SQL.

Como sabemos, nem tudo é um mar de rosas, nem sempre todos os itens listados pela comunidade no website Microsoft Connect é bem recebido ou acabam fazendo parte do backlog de itens que devem ou deveriam ser implementados em novas versões ou atualizações do produto, ainda mais se pensarmos que o comando Truncate Table esta presente no SQL Server desde as suas primeiras versões, isso em consideração as versões 6.5 e 7 do SQL Server lançadas antes do anos 2000, lá se vão mais de 16 anos que este produto esta presente na linguagem Transact-SQL e não havia passou por mudanças tão consideráveis.

Por muitos anos a comunidade de banco de dados, profissionais de banco de dados e desenvolvedores por diversas vezes questionavam a Microsoft em relação a quando o seria possível trabalhar com particionamento de tabela, o tempo passou e em 2008 foi introduzida esta capacidade a partir da versão 2008 do SQL Server, muito bem, depois disso era possível imaginar que a capacidade para remover uma parte, pedaço ou melhor dizendo partição de uma tabela seria rápidamente adicionada ao SQL Server, mas não foi bem assim que aconteceu, o tempo foi passando, novas versões e edições foram lançadas e a equipe de engenheiros do SQL Server não se manifestou em relação a esta capacidade, mas como a esperança é a última que supostamente morre, e graças a deus ela nunca vai morrer a Microsoft e seu time ouviu os pedidos da comunidade.

 

Demorou mas ela chegou…

Realmente demorou muito no meu ponto de vista, mas este demora tem algumas explicações e considerações, dentre elas, algumas limitações internas existente no SQL Server até a versão 2008 que não permitiam este tipo de implementação, dentre as quais a principal delas era a falta de capacidade de se trabalhar com particionamento de tabela divididas fisicamente em filegroups, limitação que foi contornada e adicionada como um das maiores novidades do Microsoft SQL Server 2008 em seu lançamento e posteriormente melhorada na versão 2008 R2.

 

Opção With Partition

A primeira aparição da opção With Partition relacionada ao Truncate Table ocorreu no CTP 2.1 lançado em meados de Maio/Junho de 2015, apresentando uma sintaxe bastante comum:

TRUNCATE TABLE 
    [ { database_name .[ schema_name ] . | schema_name . } ]
    table_name
    [ WITH ( PARTITIONS ( { <partition_number_expression> | <range> } 
    [ , ...n ] ) ) ]
[ ; ]

<range> ::=
<partition_number_expression> TO <partition_number_expression>

Importante: Desde então manteve-se presente nas demais CTPs(Community Technical Preview), posteriormente nas RCs (Release Candidate) e por fim na RC3 lançado recentimente como a versão mais próxima da versão final do SQL Server 2016.

Cenário

Como de costume o cenário que vamos utilizar para trabalhar com este recurso é algo bastante simples, estou utilizando em meu ambiente de testes o Microsoft Windows Server 2016 CTP 5 em conjunto com o Microsoft SQL Server 2016 RC3, caso você deseja fazer download de um destes produtos utilize os links listados abaixo:

Em nosso ambiente de testes não vou necessariamente criar uma tabela particionada em diversos filegroups, na verdade vamos similar um particionamento através de partition function criando os ranges de dados no mesmo filegroup, o objetivo deste código não é mostrar como podemos criar um particionamento de tabelas, mas sim como a opção With Partition pode e deve ser utilizada em um ambiente neste caso uma tabela particionada mesmo que seja com um único filegroup.


Utilizando o Truncate Table With Partition

Vamos então colocar a mão na massa, criando nosso ambiente de testes, para isso começamos criando um novo banco de dados chamado NewSQLServer 2016, conforme o exemplo abaixo:

— Criando o Banco de Dados —

Create Database NewSQLServer2016

Go

 

— Acessando o Banco de Dados —

Use NewSQLServer2016

Go

Agora realmente vamos começar a estruturar nosso ambiente para poder utilizar a opção With Partition, a primeiro passo é criar uma nova partition function, recurso extremamente importante e essencial para a distribuição das faixas de alocação dos dados que serão posteriormente inseridos em nossa tabela particionada.

— Passo 1 – Criando uma nova Partition Function —

CREATE PARTITION FUNCTION [PFRegistro] (int)

AS RANGE RIGHT FOR VALUES

 (10000, 30000,

  50000, 70000, 90000);

Go

 

Depois da partition function criada, outro elemente importante e obrigatório que devemos criar é o partition schema, sendo este, utilizado em conjunto com nossa function para repassar o dado identificado na partition function e armazenar dentro da área física determinada para este valor dentro da sua respectiva faixa. Note que neste exemplo não criamos novos filegroups, estamos utilizando o filegroup padrão primary para armazenar todas as faixas de valores que serão distribuídas nas partições que vamos utilizar.

Outro detalhe muito importante, que nosso partition schema terá 6(seis) áreas de armazenamento de dados, você talvez pode ficar na dúvida em relação a isso, mas na verdade sempre que estamos trabalhando com particionamento de dados, devemos criar a quantidade de partições para alocar nossas faixas e mais uma partição para trabalhar sempre com os valores acima da última faixa que vamos armazenar, neste caso, teremos então as seis partições de armazenamento, sendo elas:

  • Partição 1 – entre 0 até 10000;
  • Partição 2 – entre 10001 até 30000;
  • Partição 3 – entre 30001 até 50000;
  • Partição 4 – entre 50001 até 70000;
  • Partição 5 – entre 70001 até 90000; e
  • Partição 6 – Acima de 90001.

 

Segue abaixo o passo 2:

— Passo 2 – Criando um novo Partition Schema —
CREATE PARTITION SCHEME [PSRegistro]

 AS PARTITION [PFRegistro]

 TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);

 

No passo 3 realizaremos a criação da nossa tabela particionada, direcionando sua área de alocação de dados para nosso partition schema denominado PSRegistro, conforme o bloco de código abaixo apresenta:

— Passo 3 – Criando a tabela TabelaParticionada —

CREATE TABLE dbo.TabelaParticionada

  (NumRegistro INT NOT NULL,

  Dados char(1000) NULL,

  DataCadastro datetime NOT NULL)

ON [PSRegistro](NumRegistro)

GO

 

Agora podemos inserir as linhas de registros que serão distribuídos de acordo com seu respectivo valor dentro de cada faixa e partição de dados, conforme apresenta o passo 4 listado abaixo:

– Passo 4 – Populando a Tabela – TabelaParticionada —

INSERT dbo.TabelaParticionada

SELECT TOP 10000

       s1.number * 1000 + s2.number * 100 + s1.number As NumRegistro,

       Replicate(‘SQL Server 2016 ‘,S2.number+1) As Dados,

       GETDATE()+S2.number As DataCadastro

FROM master..spt_values s1 CROSS JOIN master..spt_values s2

WHERE s1.number BETWEEN 0 AND 999 AND s1.type = ‘P’

AND s2.number BETWEEN 0 AND 99 AND s2.type = ‘P’

 

Ufa, esta chegando no final, agora todo nosso cenário será melhor entendido, graças o passo 5 que nos permite identificar a relação de partições de dados, seus limites de valores e quantidade de valores dentro de cada faixa, com isso, poderemos identificar quais ou qual partição queremos remover de acordo com seus valores apresentados, para isso utilizamos a seguir o passo 5:

— Passo 5 – Obtendo a lista de Partitions criadas para TabelaParticionada —

SELECT $PARTITION.[PFRegistro] (NumRegistro) AS ‘Partition’,

         COUNT(*) AS TotalRegistros,

         MIN(NumRegistro) AS RegistroInicial,

         MAX(NumRegistro) AS RegistroFinal

FROM dbo.TabelaParticionada

GROUP BY $PARTITION.[PFRegistro] (NumRegistro)

Go

 

Por fim o passo 6, onde poderemos realizar a exclusão das partições e seus registros, utilizando o comando Truncate Table em conjunto com a opção With Partition, conforme apresenta o código abaixo:

— Passo 6 – Realizando a exclusão dos dados alocados na partição 1 e em conjunto com as partições 4, 5 e 6 —

TRUNCATE TABLE dbo.TabelaParticionada

WITH (PARTITIONS (2, 4 TO 6));

Go


 

Conclusão

Muito bem, chegamos ao final de mais um artigo, olha por incrível que parece esse deu um pouco de trabalho para elaborar, principalmente no momento para popular os dados na tabela, pois não gostaria de construir um código que acaba-se sobrecarregando o processamento do meu SQL Server, mas por diversas vezes acabei derrubando o meu ambiente(kkkkk).

Bom espero que você tenha gostado, que esta simples novidade adicionada ao Microsoft SQL Server 2016 possa mostrar uma nova capacidade de remover dados trabalhando diretamente com as áreas de alocação de uma determinada tabela. Com certeza esta e outras melhorias adicionadas ao SQL Server 2016 trazem a cada vez que utilizamos uma nova maneira de se obter um resultado ou resolução de uma problema de forma mais simples, fácil e rápida, requisitos que hoje são primordias para qualquer profissional ou empresa.

Mais uma vez obrigado, um grande abraço e fique ligado, pois até o final de 2016 muitas novidades do SQL Server 2016 estarão sendo passadas a limpo aqui no meu blog.

Valeu.

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação pela Uninove - Campus São Roque. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Cursando Mestrado em Ciências da Computação - UFSCar - Campus - Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, para versões: 2000, 2005, 2008, 2008 R2, 2012 e 2014. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

Um comentário em “Novidade – SQL Server 2016 – Comando Truncate Table agora com With Partitions”

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s