Short Scripts – Outubro 2016


Muito bom dia!!

Olá comunidade, estou retornando com um pouco de atraso, mas não se esquecendo das responsabilidades, caso você não tenha entendido estou me referindo ao atraso em publicar mais um post dedicado a sessão Short Scripts que deveria ter ocorrido no mês de setembro.

No decorrer de 2016 fui realizando algumas mudanças nas principais sessões do meu blog e alterando o calendário de publicação o que refletiu na alteração das publicações relacioandas as sessões: Material de Apoio e Short Scripts.

O post de hoje

Falando deste post, vou compartilhar com vocês short scripts dedicados exclusivamente ao novo Microsoft SQL Server, estou me referindo a versão 2016 lançada em Junho deste ano.

Dentre algumas novidades a Microsoft implmentou diversas inovações e melhorias na linguagem Transact-SQL, entre as quais destaco:

  • AT TIME Zone;

  • Data Masking;

  • DMV Sys.dm_exec_function_stats;

  • Função String_Split();

  • Json;

  • Temporal Table;

  • Truncate Table With Partition; e

  • View Sys.Time_Zone_Info.

Short Scripts

A seguir apresento a relação de Short Scripts de hoje:

— Short Script 1 – Utilizando a instrução AT TIME Zone —
SELECT SalesOrderID,
OrderDate,
OrderDate AT TIME ZONE ‘Pacific Standard Time’ AS   OrderDate_TimeZonePST
FROM Sales.SalesOrderHeader
Go

— Short Script 2 – Aplicando mascaramento de dados com DataMasking —
CREATE TABLE DDM_Questions
(myemail VARCHAR(300) MASKED WITH (FUNCTION=’email()’))
GO

INSERT dbo.DDM_Questions (myemail)
VALUES (‘@dog.com’);
GO
SELECT myemail FROM dbo.DDM_Questions;
GO
— Short Script 3 – Utilizando a DMV sys.dm_exec_function_stats —

USE AdventureWorks2016
GO
Select  ProductID,
LocationID,
Shelf,
Bin,
Quantity,
ModifiedDate,
dbo.ufnGetStock(ProductInventory.ProductID)  As ‘Stock’,
dbo.ufnGetProductDealerPrice(ProductInventory.ProductID,
GETDATE()) As ‘Dealer Price’,
dbo.ufnGetProductStandardCost(ProductInventory.ProductID,
GETDATE()-100) As ‘Standard Cost’
From Production.ProductInventory
Go
SELECT Concat(DB_NAME(database_id), ‘.’ ,  OBJECT_SCHEMA_NAME(OBJECT_ID, database_id), ‘.’,  OBJECT_NAME(OBJECT_ID, database_id)) AS Function_Name,
QS.last_execution_time,
QS.max_worker_time,
QS.max_physical_reads,
QS.max_logical_reads,
QS.max_logical_writes,
T.Text
FROM sys.dm_exec_function_stats QS CROSS APPLY
sys.dm_exec_sql_text(sql_handle) T
Where  database_id = DB_ID()
Order by last_execution_time
Go
— Short Script 4 – Realizando spliting de dados com a função String_Split() —
— Exemplo 1 – Separando de forma simples uma string –
SELECT *
FROM STRING_SPLIT(‘Junior,Galvão,MVP,SQL Server’,’,’)
Go
— Exemplo 2 – Fazendo uso de variáveis como parâmetros de entrada de valores –
DECLARE @string VARCHAR(100) = ‘Microsoft,SQL Server,2016,RC0′,
@separador CHAR(1) =’,’
SELECT *
FROM STRING_SPLIT(@string,@separador)
Go
— Exemplo 3 – Armazenando o resultado da divisão de uma string em uma nova tabela –
DECLARE @string VARCHAR(100) = ‘Microsoft,SQL Server,2016,RC0′,
@separador CHAR(1) =’,’
SELECT * INTO #SplitTable
FROM STRING_SPLIT(@string,@separador)
GO

 

— Short Script 5 – Gerando dados no formato Json —
— Exemplo 1 – Utilizando a claúsula JSON Auto —
Select Top 20
AddressID, AddressLine1,
City, PostalCode,
ModifiedDate
from [Person].[Address]
For JSON Auto
Go

— Exemplo 2 – Utilizando a claúsula JSON Path —
Select Top 5
AddressID, AddressLine1,
City, PostalCode,
ModifiedDate
from [Person].[Address]
For JSON Path
Go
— Exemplo 3 – Utilizando a claúsula JSON Path em conjunto com uma variável –
DECLARE @MyJson as NVARCHAR(MAX)
SET @MyJson = (SELECT ‘Pedro’ as Nome, ‘Galvão’ as Sobrenome,
35 as Idade, Getdate() as DataAtual
FOR JSON PATH)
Print (@MyJson)

Go

— Short Script 6 – Criando uma nova Temporal Table —
CREATE TABLE [dbo].[Orders](
[OrdersID] int PRIMARY KEY CLUSTERED,
[Quantity] int NOT NULL,
[UnitPrice] money not null,
[OrderDate] datetime2 NOT NULL,
[SysStartTime] datetime2(0) GENERATED ALWAYS AS ROW START NOT NULL,
[SysEndTime] datetime2(0) GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME ([SysStartTime],  [SysEndTime]))
WITH (SYSTEM_VERSIONING = ON )
Go

— Short Script 7 – Realizando Truncate Table With Partition —
— Passo 1 – Criando uma nova Partition Function —
CREATE PARTITION FUNCTION [PFRegistro] (int)
AS RANGE RIGHT FOR VALUES
(10000, 30000,
50000, 70000,
90000);
Go

— Passo 2 – Criando um novo Partition Schema —
CREATE PARTITION SCHEME [PSRegistro]
AS PARTITION [PFRegistro]
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);— 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— 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’
— 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
— Passo 6 – Realizando a exclusao da particao 1 e tambem da particao 4 ate particao 6 —
TRUNCATE TABLE dbo.TabelaParticionada
WITH (PARTITIONS (2, 4 TO 6));
Go

 

— Short Script 8 – Consultando a relação de TIME Zone —
Select Name,
    Current_UTC_OffSet,
Is_Currently_DST
From Sys.Time_Zone_Info
Go


Bom pessoal, chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar e ilustrar algumas das novidades adicionados ao Microsoft SQL Server 2016.

Acredito que você tenha observado que estes códigos já são bastante conhecidos no meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server 2016, publicados no decorrer deste ano.

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

Agradecimento

Mais uma vez obrigado por sua visita, um forte abraço, nos encontramos em breve.

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.