@01 – O que Acontece?

No 1º post da sessão O que Acontece. Vou apresentar através da ferramenta Process Monitor o que acontece no disco rígido durante o processamento de um simples comando Select executado através do Azure Data Studio.


Muito boa tarde galera! Salve 2020….

Seja bem-vindo ao primeiro post de 2020, em adicional ao @01 – O que Acontece? Post da nova sessão que estou lançando hoje aqui no meu blog.

Antes de apresentar este primeiro post, quero destacar alguns pontos em sobre esta nova sessão, em destaque o tipo de conteúdo você leitor vai encontrar a partir de hoje nos posts relacionados a ela:

1 – Os posts que serão publicados nesta sessão envolvem um pouco do Micrososft SQL Server, na verdade o objetivo dela é tentar mostrar como ele SQL Server, o qual aqui eu vou denominar como caixa, pensa exclusivamente fora dela, ou seja, como este grandioso SGBD (Sistema Gerenciador de Banco de Dados) faz para que tenhamos nossos dados armazenados ou apresentados em tela, ou como nós podemos de uma forma simples aprender, conhecer e desvendar os comportamentos realizados por ele além do uso das habituais ferramentas de administração: SQL Server Management Studio ou Azure Data Studio.

2 – Os posts aqui publicados, não apresentam o objetivo de demonstrar recursos, comandos, funcionalidades ou ensinar algo novo, na verdade eu quero tentar mostrar o que acontece muitas vezes quando por exemplo você esta processando um simples comando Select buscando milhões de dados em uma tabela, e o SQL Server Management Studio vai apresentando aos poucos os registros. Serão exclusivamente estes cenários, comportamentos e formas de atuação envolvendo o SQL Server e o ambiente que ele se encontra;

3 – Os posts aqui publicados não terão uma estrutura padrão, na verdade, O que Acontece, foi idealizado da mesma forma que inicialmente começamos a pensar. Vamos reunindo conhecimentos, ideias, possibilidades, hipóteses, analisando alternativas, mensurando teorias, até tentar construir algo mais concreto;

4 – Não será estabelecido um calendário de publicação, ao contrário, sempre que algum pensamento fora da caixa pairar sobre a minha cabeça, ou coisas do meu dia-a-dia relacionados as minhas experiências profissionais ou acadêmicas, novos posts serão publicados, bem como, os atuais atualizados e corrigidos;

5 – Não vou me ater ao certo ou errado, melhor ou pior, tecnicamente perfeito ou melhor tecnicamente, o que eu quero é tentar como eu já destaquei ilustrar o que acontece do lado de fora do SQL Server, o que ele muitas vezes esta realizando e não temos ideia do que está acontecendo; e

6 – Vou tentar em cada post trazer uma ferramenta, aplicativo ou utilitário existente no próprio sistema operacional que possa nos ajudar a observar e entender o que está acontecendo de preferência em tempo real, em adicional, se possível utilizarei vídeos para elucidar de forma mais didática o objetivo do post.

Continue Lendo “@01 – O que Acontece?”

Dica do Mês – Identificando os números ausentes em uma sequência numérica


Olá pessoal, bom dia.

Tudo bem? Estamos no ultimo final de semana do mês de Setembro, a primavera chegou, já estamos vivendo a última estação do ano, 2019 voando, passando de forma extremamente rápida, o que muitas vezes não nos deixa perceber o quanto nossa vida é uma correria.

Mesmo com este correira, temos que tentar aproveitar todo tempo disponível dentro das 24 horas diárias para se dedicar também ao que gostamos de fazer, e por isso estou aqui novamente em mais um post do meu blog, que honra ter encontrar aqui neste sábado, sua presença é muito importante.

Ainda mais na sessão Dica do Mês, a qual foi criada á alguns anos com objetivo de compartilhar algo que possa ser considerada como uma dica ou melhores práticas para se trabalhar na área de banco de dados, mais especificamente com o Microsoft SQL Server.

A dica de hoje esta envolvida diretamente com os dados manipulados diariamente por empresas, instituições ou organizações públicas e privadas, me refiro a lançamento feitos através de um SIG – Sistema de Informação Gerencial ou ERP – Enterprise Resource Planning, que muitas vezes utilizamos para dar entrada em lançamento contábeis, movimentações fiscais e financeiras, ou “basicamente” cadastrar uma nota fiscal de recebimento de produto no almoxarifado da empresa.

Para facilitar o entendimento, e server como base de exemplo para nossa dica, dentre as mais diversas áreas, setores e departamentos que constituem uma empresa, selecione para esta dica, uma das mais tradicionais áreas conhecida por muitos como Controle de Estoque vinculado com os lançamentos contábeis.

Mais ainda não ficou claro o objetivo desta dica, não é mesmo? Tenho a certeza que não, mas tenha calma, você vai descobrir a seguir o que estou querendo te apresentar.

Continue Lendo “Dica do Mês – Identificando os números ausentes em uma sequência numérica”

Dica do Mês – Como ficar “quase louco” utilizando análise combinatória no Microsoft SQL Server


Olá, boa noite.

Estamos no mês de março, carnaval já passou, o primeiro trimestre de 2019 está acabando, e para minha alegria te encontro mais uma vez no meu blog, caso esta seja a sua primeira visita ou acesso, fico mais feliz ainda, seja muito bem-vindo.

Este é mais um post da sessão Dica do Mês, sessão dedicada a compartilhar bimestralmente dicas, novidades, curiosidades e demais assuntos, conteúdos e informações relacionadas ao Microsoft SQL Server, Banco de Dados e Tecnologias de Banco de Dados.

No post de hoje, quero compartilhar com vocês a minha mais nova “loucura” criada em meus ambientes de estudos acadêmicos para ser utilizada no Microsoft SQL Server através do uso da Análise Combinatória, isso mesmo mais uma vez a matemática esta presente em nossas vidas e desta vez foi justamente para permitir a criação de um script que permite criar todas as sequência de combinações de letras e números afim de construir um gerador de placas de carros para todos os estados brasileiros.

 

Não parece realmente coisa de louco, minha esposa disse que sim, eu também acho (kkkkk).

Pois bem, ficou curioso para saber como eu criei mais esta “loucura”? Calma, daqui a pouco eu conto mais sobre isso para você.

Sendo assim, sem mais delongas, vamos em frente, vou tentar mitigar a sua curiosidade e ao mesmo também satisfazer os meus objetivos. Seja bem-vindo ao post – Dica do Mês – Como ficar “quase louco” utilizando análise combinatória no Microsoft SQL Server.


Introdução

Muito se fala que a área de banco de dados, e posteriormente os Sistemas Gerenciadores de Bancos de Dados (SGBDs) nasceram dos métodos, técnicas e também das teorias existentes na Matemática.

A cada dia eu tenho mais certeza que esta analogia é verdadeira, e neste post, eu pretendo justamente mostrar como mais uma vez esta fantástica e grandiosa área de estudos e conhecimentos pode nos ajudar a transformar algo que parece ter um nível de raciocínio tão complexo ou talvez impossível, em algo na verdade simples, fácil e de rápida compreensão.

Para ser mais direto, estou me referindo a análise combinatória, uma das mais variadas áreas de conhecimento e aprendizado existentes na Matemática e que este mero ser humano demorou um bom tempo para conseguir entender de verdade e aplicar de forma bem racional.

ANÁLISE COMBINATÓRIA

Podemos determinar a análise combinatória como sendo um conjunto de possibilidades constituídos por elementos finitos, a mesma baseia-se em critérios que possibilitam a contagem. Realizamos o seu estudo na lógica matemática, analisando possibilidades e combinações.

Por exemplo: Descubra quantos números com 3 algarismos conseguimos formar com o conjunto numérico {1, 2, 3}, olha a teoria de conjuntos aí gente….

  • Conjunto de elementos finito: {1, 2, 3}.
  • Conjunto de possibilidades de números com 3 algarismos: {123, 132, 213, 231, 312, 321}.

A análise combinatória estuda os seguintes conteúdos:

  • Princípio fundamental da contagem;
  • Fatorial;
  • Permutação simples;
  • Permutação com repetição;
  • Arranjo simples; e
  • Combinação simples.

Não vou abortar todos estes conteúdos de estudo utilizado pela análise combinatória neste post, mas sim o que mais entendo como importante e de extrema necessidade para o cenário que estaremos utilizando a posterior, sendo estes:

  • Permutação Simples; e
  • Permutação com repetição.

Permutação simples

Na permutação os elementos que compõem o agrupamento mudam de ordem, ou seja, de posição. Determinamos a quantidade possível de permutação dos elementos de um conjunto, com a seguinte expressão:

Pn = n!
Pn = n . (n-1) . (n-2) . (n-3)…..1!

Exemplo: Em uma eleição para representante de sala de aula, 3 alunos candidataram-se: Fernanda, Eduardo e Malú. Quais são os possíveis resultados dessa eleição?

  • Fernanda (F);
  • Eduardo (E); e
  • Malú (M).

Os possíveis resultados dessa eleição podem ser dados com uma permutação simples, acompanhe:

n = 3 (Quantidade de candidatos concorrendo a representante)

Pn = n!

Pn = 3 . 2 . 1!
Pn = 6

Para a eleição de representante, temos 6 possibilidades de resultado, em relação a posição dos candidatos, ou seja, 1º, 2º e 3º lugar.

Veja a seguir os possíveis resultados dessa eleição:

Resultado 1

Resultado 2 Resultado 3 Resultado 4 Resultado 5 Resultado 6
FEM FME EFM EMF MEF

MFE

 

Permutação com repetição

Nessa permutação alguns elementos que compõem o evento experimental são repetidos, quando isso ocorrer devemos aplicar a seguinte fórmula:

Pn(n1,n2,n3…nk)=n!n1!⋅n2!⋅n3!…nk!

  • Pn(n1,n2,n3…nk) = permutação com repetição
  • n! = total de elemetos do evento
  • n1!⋅n2!⋅n3!…nk! = Elementos repetidos do evento

Exemplo: Quantos anagramas são possíveis formar com a palavra CASA. A palavra CASA possui:
4 letras (n) e duas vogais que se repetem (n1).

  • n! = 4!
  • n1! = 2!

Pn(n1)=n!n1!

Pn(n1)=4!2!

Pn(n1)=4⋅3⋅2⋅1!2⋅1!

Pn(n1)=242=12

Anagramas da palavra CASA sem repetição

CASA

ACSA ASCA ASAC SCAA

CSAA

AASC AACS CAAS SAAC SACA

ACAS

Bom, agora que conhecemos um pouco destes conceitos, você pode estar se perguntando:

“O que o Microsoft SQL Server tem haver com isso?”.

Então, tudo, pois ele faz justamente uso destes elementos e dos demais quando queremos realizar as combinações das mais variadas possíveis que envolvem letras, letras e números, ou somente números.

E aí, até aqui tudo tranquilo? Espero que sim, pois daqui em diante começaremos a preparar nosso ambiente e aplicaremos a análise combinatória e as permutações para colocarmos em funcionamento a minha “loucura”.

Para você ter a ideia do nível de loucura que estaremos trabalhando, ao realizar o uso das vinte e seis letras do alfabeto em nosso idioma da língua portuguesa teremos basicamente a seguinte permutação com repetição:

  • n! = 3!
  • n1! = 26!

Ou seja, de forma mais simples, vamos permutar: 26 letras * 26 letras * 26 letras, o que não apresentara um total de: 17.756 (Dezessete Mil, Setecentos e Cinquenta e Seis) combinações de letras distintas.

Mas não terminamos isso nossa caminhada, depois de realizar estas combinações de letras (17.756), vamos fazer uso dos arranjos (este conteúdo eu não abordei), que nos permitirá criar em tempo real todos os agrupamentos entre letras e números (de 0 até 9999), estabelecendo a seguinte fórmula:

Arranjos = LetrasCombinadas(17756) * Numeracao(0…9999) = 177.542.244

os anagramas que vimos apouco, como por exemplo: AAA-0001, o qual vai nos permitir obter um total de: 177.542.244 (Cento e Setenta e Sete Milhões, Quinhentos e Quarente e Dois Mil, Duzentas e Quarenta e Quatro) agrupamentos ou arranjos únicos e distintos que teremos a disposição para serem armazenados.

Não é algo de louco mesmo?

NOSSO AMBIENTE

Como de costume vamos utilizar um ambiente isolado dos demais bancos de dados que você possa conter, desta maneira nosso cenário será constituído dos seguintes elementos:

  • Banco de Dados: GeradorDePlacas;
  • Tabelas: LetrasCombinadas, Numeracao, Placas e FaixasDePlacasPorEstado;
  • CTEs: CTEMeuAlfabeto; e
  • Stored Procedure: P_PesquisarPlacas.

Criando o ambiente

Através do Bloco de Código 1 apresentado abaixo, vamos realizar a criação dos respectivos elementos destacados anteriormente:

 

— Bloco de Código 1 —

 

— Criando o Banco de Dados —

Create Database GeradorDePlacas

Go

 

— Acessando o Banco de Dados —

Use GeradorDePlacas

Go

 

— Desativando a contagem de linhas —

Set NoCount On

Go

 

— Criando a Tabela LetrasCombinadas para armazenar todas as combinações de Letras —

Create Table LetrasCombinadas

(CodigoSequencialLetrasCombinadas SmallInt Primary Key Identity(1,1) Not Null,

SequencialDeLetrasCombinadas Char(3) Not Null)

Go

 

— Criando a Tabela Numeracao para armazenar a faixa numérica de 1 até 9999 —

Create Table Numeracao

(CodigoNumeracao SmallInt Primary Key Identity(1,1) Not Null)

Go

 

— Criando a Tabela Placas para armazenar o CodigoSequencialLetrasCombinadas e o número da Placa —

Create Table Placas

(CodigoSequencialPlacas Int Primary Key Identity(1,1) Not Null,

CodigoSequencialLetrasCombinadas SmallInt Not Null,

CodigoSequencialNumeroPlacas SmallInt Not Null)

Go

 

— Criando a Tabela FaixasDeFaixasDePlacasPorEstado para armazenar as faixas de placas por Estado —

Create Table FaixasDePlacasPorEstado

(CodigoSequencialFaixasDePlacasPoEstado TinyInt Primary Key Identity(1,1) Not Null,

CodigoSequencialFaixasDeLetrasNumerosInicial Char(7) Not Null,

CodigoSequencialFaixasDeLetrasNumerosFinal Char(7) Not Null,

FaixasDePlacasPorEstadoNomeDoEstado Varchar(30) Not Null)

Go

 

— Inserindo a distribuição de Faixas de Placas Por Estado —

Insert Into FaixasDePlacasPorEstado Values (‘AAA0001′,’BEZ9999′,’Paraná’)

Insert Into FaixasDePlacasPorEstado Values (‘BFA0001′,’GKI9999′,’São Paulo’)

Insert Into FaixasDePlacasPorEstado Values (‘GKJ0001′,’HOK9999′,’Minas Gerais’)

Insert Into FaixasDePlacasPorEstado Values (‘HOL0001′,’HQE9999′,’Maranhão’)

Insert Into FaixasDePlacasPorEstado Values (‘HQF0001′,’HTW9999′,’Mato Grosso do Sul’)

Insert Into FaixasDePlacasPorEstado Values (‘HTX0001′,’HZA9999′,’Ceará’)

Insert Into FaixasDePlacasPorEstado Values (‘HZB0001′,’IAP9999′,’Sergipe’)

Insert Into FaixasDePlacasPorEstado Values (‘IAQ0001′,’JDO9999′,’Rio Grande do Sul’)

Insert Into FaixasDePlacasPorEstado Values (‘JDP0001′,’JKR9999′,’Distrito Federal’)

Insert Into FaixasDePlacasPorEstado Values (‘JKS0001′,’JSZ9999′,’Bahia’)

Insert Into FaixasDePlacasPorEstado Values (‘JTA0001′,’JWE9999′,’Pará’)

Insert Into FaixasDePlacasPorEstado Values (‘JWF0001′,’JXY9999′,’Amazonas’)

Insert Into FaixasDePlacasPorEstado Values (‘JXZ0001′,’KAU9999′,’Mato Grosso’)

Insert Into FaixasDePlacasPorEstado Values (‘KAV0001′,’KFC9999′,’Goiás’)

Insert Into FaixasDePlacasPorEstado Values (‘KFD0001′,’KME9999′,’Pernambuco’)

Insert Into FaixasDePlacasPorEstado Values (‘KMF0001′,’LVE9999′,’Rio de Janeiro’)

Insert Into FaixasDePlacasPorEstado Values (‘LVF0001′,’LWQ9999′,’Piauí’)

Insert Into FaixasDePlacasPorEstado Values (‘LWR0001′,’MMM9999′,’Santa Catarina’)

Insert Into FaixasDePlacasPorEstado Values (‘MMN0001′,’MOW9999′,’Paraíba’)

Insert Into FaixasDePlacasPorEstado Values (‘MOX0001′,’MTZ9999′,’Espírito Santo’)

Insert Into FaixasDePlacasPorEstado Values (‘MUA0001′,’MVK9999′,’Alagoas’)

Insert Into FaixasDePlacasPorEstado Values (‘MVL0001′,’MXG9999′,’Tocantins’)

Insert Into FaixasDePlacasPorEstado Values (‘MXH0001′,’MZM9999′,’Rio Grande do Norte’)

Insert Into FaixasDePlacasPorEstado Values (‘MZN0001′,’NAG9999′,’Acre’)

Insert Into FaixasDePlacasPorEstado Values (‘NAH0001′,’NBA9999′,’Roraima’)

Insert Into FaixasDePlacasPorEstado Values (‘NBB0001′,’NEH9999′,’Rondônia’)

Insert Into FaixasDePlacasPorEstado Values (‘NEI0001′,’NFB9999′,’Amapá’)

Insert Into FaixasDePlacasPorEstado Values (‘NFC0001′,’NGZ9999′,’Goiás’)

Insert Into FaixasDePlacasPorEstado Values (‘NHA0001′,’NHT9999′,’Maranhão’)

Insert Into FaixasDePlacasPorEstado Values (‘NHU0001′,’NIX9999′,’Piauí’)

Insert Into FaixasDePlacasPorEstado Values (‘NIY0001′,’NJW9999′,’Mato Grosso’)

Insert Into FaixasDePlacasPorEstado Values (‘NJX0001′,’NLU9999′,’Goiás’)

Insert Into FaixasDePlacasPorEstado Values (‘NLV0001′,’NMO9999′,’Alagoas’)

Insert Into FaixasDePlacasPorEstado Values (‘NMP0001′,’NNI9999′,’Maranhão’)

Insert Into FaixasDePlacasPorEstado Values (‘NNJ0001′,’NOH9999′,’Rio Grande do Norte’)

Insert Into FaixasDePlacasPorEstado Values (‘NOI0001′,’NPB9999′,’Amazonas’)

Insert Into FaixasDePlacasPorEstado Values (‘NPC0001′,’NPQ9999′,’Mato Grosso’)

Insert Into FaixasDePlacasPorEstado Values (‘NPR0001′,’NQK9999′,’Paraíba’)

Insert Into FaixasDePlacasPorEstado Values (‘NQL0001′,’NRE9999′,’Ceará’)

Insert Into FaixasDePlacasPorEstado Values (‘NRF0001′,’NSD9999′,’Mato Grosso do Sul’)

Insert Into FaixasDePlacasPorEstado Values (‘NSE0001′,’NTC9999′,’Pará’)

Insert Into FaixasDePlacasPorEstado Values (‘NTD0001′,’NTW9999′,’Bahia’)

Insert Into FaixasDePlacasPorEstado Values (‘NTX0001′,’NUG9999′,’Mato Grosso’)

Insert Into FaixasDePlacasPorEstado Values (‘NUH0001′,’NUL9999′,’Roraima’)

Insert Into FaixasDePlacasPorEstado Values (‘NUM0001′,’NVF9999′,’Ceará’)

Insert Into FaixasDePlacasPorEstado Values (‘NVG0001′,’NVN9999′,’Sergipe’)

Insert Into FaixasDePlacasPorEstado Values (‘NVO0001′,’NWR9999′,’Goiás’)

Insert Into FaixasDePlacasPorEstado Values (‘NWS0001′,’NXQ9999′,’Maranhão’)

Insert Into FaixasDePlacasPorEstado Values (‘NXR0001′,’NXT9999′,’Acre’)

Insert Into FaixasDePlacasPorEstado Values (‘NXU0001′,’NXW9999′,’Pernambuco’)

Insert Into FaixasDePlacasPorEstado Values (‘NXX0001′,’NYG9999′,’Minas Gerais’)

Insert Into FaixasDePlacasPorEstado Values (‘NYH0001′,’NZZ9999′,’Bahia’)

Insert Into FaixasDePlacasPorEstado Values (‘OAA0001′,’OAO9999′,’Amazonas’)

Insert Into FaixasDePlacasPorEstado Values (‘OAP0001′,’OBS9999′,’Mato Grosso’)

Insert Into FaixasDePlacasPorEstado Values (‘OBT0001′,’OCA9999′,’Pará’)

Insert Into FaixasDePlacasPorEstado Values (‘OCB0001′,’OCU9999′,’Ceará’)

Insert Into FaixasDePlacasPorEstado Values (‘OCV0001′,’ODT9999′,’Espírito Santo’)

Insert Into FaixasDePlacasPorEstado Values (‘ODU0001′,’OEI9999′,’Piauí’)

Insert Into FaixasDePlacasPorEstado Values (‘OEJ0001′,’OES9999′,’Sergipe’)

Insert Into FaixasDePlacasPorEstado Values (‘OET0001′,’OFH9999′,’Paraíba’)

Insert Into FaixasDePlacasPorEstado Values (‘OFI0001′,’OFW9999′,’Pará’)

Insert Into FaixasDePlacasPorEstado Values (‘OFX0001′,’OGG9999′,’Paraíba’)

Insert Into FaixasDePlacasPorEstado Values (‘OGH0001′,’OHA9999′,’Goiás’)

Insert Into FaixasDePlacasPorEstado Values (‘OHB0001′,’OHK9999′,’Alagoas’)

Insert Into FaixasDePlacasPorEstado Values (‘OHL0001′,’OHW9999′,’Rondônia’)

Insert Into FaixasDePlacasPorEstado Values (‘OHX0001′,’OIQ9999′,’Ceará’)

Insert Into FaixasDePlacasPorEstado Values (‘OIR0001′,’OJK9999′,’Maranhão’)

Insert Into FaixasDePlacasPorEstado Values (‘OJR0001′,’OKC9999′,’Rio Grande do Norte’)

Insert Into FaixasDePlacasPorEstado Values (‘OKI0001′,’OLG9999′,’Bahia’)

Insert Into FaixasDePlacasPorEstado Values (‘OLH0001′,’OLN9999′,’Tocantins’)

Insert Into FaixasDePlacasPorEstado Values (‘OLO0001′,’OMH9999′,’Minas Gerais’)

Insert Into FaixasDePlacasPorEstado Values (‘OMI0001′,’OOF9999′,’Goiás’)

Insert Into FaixasDePlacasPorEstado Values (‘OOG0001′,’OOU9999′,’Mato Grosso do Sul’)

Insert Into FaixasDePlacasPorEstado Values (‘OOV0001′,’ORC9999′,’Minas Gerais’)

Insert Into FaixasDePlacasPorEstado Values (‘ORD0001′,’ORM9999′,’Alagoas’)

Insert Into FaixasDePlacasPorEstado Values (‘ORN0001′,’OSV9999′,’Ceará’)

Insert Into FaixasDePlacasPorEstado Values (‘OSW0001′,’OTZ9999′,’Pará’)

Insert Into FaixasDePlacasPorEstado Values (‘OUA0001′,’OUE9999′,’Piauí’)

Insert Into FaixasDePlacasPorEstado Values (‘OUF0001′,’OVD9999′,’Bahia’)

Insert Into FaixasDePlacasPorEstado Values (‘OVE0001′,’OWC9999′,’Espírito Santo’)

Insert Into FaixasDePlacasPorEstado Values (‘OWD0001′,’OYG9999′,’Santa Catarina’)

Insert Into FaixasDePlacasPorEstado Values (‘PEE0001′,’PFQ9999′,’Pernambuco’)

Insert Into FaixasDePlacasPorEstado Values (‘PFR0001′,’PGK9999′,’Pernambuco’)

Insert Into FaixasDePlacasPorEstado Values (‘PGL0001′,’PHE9999′,’Pernambuco’)

Insert Into FaixasDePlacasPorEstado Values (‘SAV0001′,’SAV9999′,’São Paulo’)

Go

 

— Validando os dados inseridos na Tabela FaixasDePlacasPorEstado —

Select * From FaixasDePlacasPorEstado
Go

 

— Inserindo 9999 linhas de registros lógicos na Tabela Numeracao —

Insert Into Numeracao Default Values

Go 9999

 

— Validando os dados inseridos na Tabela Numeracao —

Select * From Numeracao

Go

Muito bem, agora que temos nossa estrutura quase toda montada, vamos avançar mais um pouco e fazer uso da análise combinatória, conforme o Bloco de Código 2 declarado abaixo:

 

— Bloco de Código 2 —

 

— Montando a CTE Recursiva para Gerar todas as combinações de Letras —

;With CTEMeuAlfabeto

As

(Select * From (Values (‘A’),(‘B’),(‘C’),(‘D’),(‘E’),(‘F’),(‘G’),(‘H’),(‘I’),(‘J’),(‘K’),

(‘L’),(‘M’),(‘N’),(‘O’),(‘P’),(‘Q’),(‘R’),(‘S’),(‘T’),(‘U’),(‘V’),

(‘W’),(‘X’),(‘Y’),(‘Z’)) As Alfabeto (LetrasDoAlfabeto)

)

— Inserindo as combinações de letras na Tabela LetrasCombinadas —

Insert Into LetrasCombinadas

Select Distinct Concat(A1.LetrasDoAlfabeto, A2.LetrasDoAlfabeto, A3.LetrasDoAlfabeto) As ConcatenacaoLetrasCombinadas

From CTEMeuAlfabeto A1

Cross Join CTEMeuAlfabeto A2 — Aqui que a mágia acontece —

Cross Join CTEMeuAlfabeto A3 — O cross join vai cruzar e combinar todas as letras —

Order By ConcatenacaoLetrasCombinadas Asc

Go

 

— Validando os dados inseridos na Tabela LetrasCombinadas —

Select * From LetrasCombinadas

Go

Nosso Bloco de Código 2 já deve ter sido processado, pois ele é especificamente o centro das atenções para conseguirmos criar todas as combinações possíveis de letras do nosso alfabeto, que estará criando em poucos segundos um total de: 17.756 (Dezessete Mil, Setecentos e Cinquenta e Seis) combinações de letras distintas.

Ufa, estamos avançando, você vai poder notar neste post, que estou fazendo uso de diversos comandos e técnicas existentes no SQL Server desde as primeiras versões como também outros implementados nas versões mais atuais, dentre eles destaco o uso de Tabela Derivada conforme o comando Select From (Values()) existente desde a versão 2000, e também da CTE – Common Table Expression adicionado ao Microsoft SQL Server a partir da versão 2005.

Vamos avançar mais ainda, pois o objetivo deste post não é mostrar somente o uso da análise combinatória, ao contrário, o estudo aqui criado, me permitiu elaborar alguns cenários que me permitiram adotar formas e técnicas diferentes de realizar todas as combinações entre letras e números afim de processar, criar e armazenar todas as placas criadas justamente na tabela denominada placas.

Destaco que foram criados 3 (três) cenários de estudo de acordo com percepções e análises que realizei, fazendo uso de recursos e técnicas distintas visando identificar o que poderia ser melhor utilizada:

  • Cenário 1 – Inserindo dados na Tabela de Placas através de Loop Condicional;
  • Cenário 2 – Inserindo dados na Tabela de Placas através de CTE Recursiva com Junção Cruzada; e
  • Cenário 3 – Inserindo dados na Tabela de Placas através de Junção Cruzada.

Nota: Tenho a certeza que você vai poder criar outros cenários e novas análises, como também, utilizar os mais variados recursos e funcionalidades existentes no Microsoft SQL Server, reforço mais uma vez que estes cenários são meras amostras de estudo e comparações do meu entendimento.

O objetivo de ter criado estes cenários, possibilitou realizarmos comparações de tempo de processamento no que relaciona ao uso da CPU e consumo de memória RAM para cada um dos cenários.

A Tabela 1 declarada abaixo apresenta um resumo dos valores obtidos durante 5 rodadas de processamento executadas em meu ambiente:

Cenário

Média de Uso de CPU Média do Uso de Memória em GBs Média de Uso de Disco Rígido Tempo Mínimo de processamento

Tempo Máximo de processamento

01 57% 3.5 Gbs 65% 19 Hrs e 23 segundos 21Hrs e 18 segundos
02 34% 2.8 Gbs 42% 33 minutos e 6 segundos 42 minutos e 27 segundos
03 18% 2.0 Gbs 24% 6 minutos e 13 segundos 10 minutos e 25 segundos

Tabela 1 – Resumo comparativo do uso de CPU, Memória e Disco, em conjunto com os tempos de processamento demandados para cada cenário.

Show, agora que temos este pequeno resumo dos tempos de processamento e uso dos principais recursos de hardware, já podemos conhecer cada um dos cenários elaborados abaixo, conforme apresenta o Bloco de Código 3 a seguir:

— Bloco de Código 3 —

 

— Cenário 1 –

 

— Inserindo dados na Tabela de Placas através de Loop Condicional – 19Hrs e 33s de processamento —

 

— Limpando a Tabela de Placas —

Truncate Table Placas

Go

 

— Declarando as variáveis de controle —

Declare @ContadorSequencialTotalPlacasInseridas Int = 1,

@ContadorSequencialParcialPlacasInseridas Int = 1,

@CodigoSequencialLetrasCombinadas Int = 1

 

— Abrindo o Loop de Inserção —

While @ContadorSequencialTotalPlacasInseridas <=(Select Max(CodigoSequencialLetrasCombinadas) From LetrasCombinadas)

Begin

 

Set @CodigoSequencialLetrasCombinadas=(Select CodigoSequencialLetrasCombinadas From LetrasCombinadas

Where CodigoSequencialLetrasCombinadas = @ContadorSequencialTotalPlacasInseridas)

 

While @ContadorSequencialParcialPlacasInseridas <=9999 — Contador a cada 9999 incrementa a variável @ContadorSequencialTotalPlacasInseridas

Begin

Insert Into Placas (CodigoSequencialLetrasCombinadas, CodigoSequencialNumeroPlacas)

Values (@CodigoSequencialLetrasCombinadas, @ContadorSequencialParcialPlacasInseridas)

Set @ContadorSequencialParcialPlacasInseridas         +=1

End

 

Set @ContadorSequencialParcialPlacasInseridas = 1

Set @CodigoSequencialLetrasCombinadas +=1

Set @ContadorSequencialTotalPlacasInseridas +=1

 

End

 

— Cenário 2 –

 

— Inserindo dados na Tabela de Placas através de CTE Recursiva com Junção Cruzada — 33 minutos e 40s de processamento —

 

— Limpando a Tabela de Placas —

Truncate Table Placas

Go

 

— Declarando as variáveis de controle —

Declare @ContadorSequencialTotalPlacasInseridas Int = 1,

@CodigoSequencialLetrasCombinadas SmallInt = 1

 

While @ContadorSequencialTotalPlacasInseridas <=(Select Max(CodigoSequencialLetrasCombinadas) From LetrasCombinadas)

Begin

 

— Realizando a Junção Cruzada entre as Tabelas LetrasCombinadas x CTENumeracao —

;With CTENumeracao

As

(Select 1 As Numero

Union All

Select Numero + 1 From CTENumeracao

Where Numero <=9998

)

Insert Into Placas (CodigoSequencialLetrasCombinadas, CodigoSequencialNumeroPlacas)

Select LC.CodigoSequencialLetrasCombinadas, N.Numero

From LetrasCombinadas LC Cross Join — Aqui acontece a mágia

CTENumeracao N — Cross Joi vai combinar todas as Letras com 9999 números —

Where CodigoSequencialLetrasCombinadas = @CodigoSequencialLetrasCombinadas

Option (MaxRecursion 0)

 

Set @CodigoSequencialLetrasCombinadas +=1

Set @ContadorSequencialTotalPlacasInseridas +=1

End

Go

 

— Cenário 3 –

 

— Inserindo dados na Tabela de Placas através de Junção Cruzada – 10 minutos e 27s de processamento —

 

— Limpando a Tabela de Placas —

Truncate Table Placas

Go

 

— Declarando as variáveis de controle —

Declare @ContadorSequencialTotalPlacasInseridas Int = 1,

@CodigoSequencialLetrasCombinadas SmallInt = 1

 

While @ContadorSequencialTotalPlacasInseridas <=(Select Max(CodigoSequencialLetrasCombinadas) From LetrasCombinadas)

Begin

 

— Realizando a Junção Cruzada entre as Tabelas LetrasCombinadas x Numeracao —

Insert Into Placas (CodigoSequencialLetrasCombinadas, CodigoSequencialNumeroPlacas)

Select LC.CodigoSequencialLetrasCombinadas, N.CodigoNumeracao

From LetrasCombinadas LC Cross Join — Aqui acontece a mágia

Numeracao N — Cross Joi vai combinar todas as Letras com 9999 números —

Where CodigoSequencialLetrasCombinadas = @CodigoSequencialLetrasCombinadas

 

Set @CodigoSequencialLetrasCombinadas +=1

Set @ContadorSequencialTotalPlacasInseridas +=1

End

Go

 

Show, show, e show, nossos três cenários de estudo e testes já estão apresentados, basta você escolher qual deseja brincar, executar, encontrar as melhorias e possíveis falhas que podem existir.

Pra finalizar nossa longa caminhada, apresento o Bloco de Código 4, o qual vai ilustrar como podemos consultar nossas tabelas e obter os dados já inseridos após o processamento de um dos cenários, e por fim o Bloco de Código 5 que apresenta o código utilizado no Bloco de Código 4 transformado em uma Stored Procedure denominada: P_PesquisarPlacas.

— Bloco de Código 4 —

— Apresentando as 100 primeiras Placas Geradas —

Select Top 100 Concat(LC.SequencialDeLetrasCombinadas,’-‘,

Convert(Char(4),Case

When P.CodigoSequencialNumeroPlacas BetWeen 1 And 9 Then Concat(‘000’,P.CodigoSequencialNumeroPlacas)

When P.CodigoSequencialNumeroPlacas BetWeen 10 And 99 Then Concat(’00’,P.CodigoSequencialNumeroPlacas)

When P.CodigoSequencialNumeroPlacas BetWeen 100 And 999 Then Concat(‘0’,P.CodigoSequencialNumeroPlacas)

When P.CodigoSequencialNumeroPlacas BetWeen 1000 And 9999 Then Convert(Char(4),P.CodigoSequencialNumeroPlacas)

End)) As ‘Placa’,

IsNull(F.FaixasDePlacasPorEstadoNomeDoEstado,’Sequência não atribuída…’) As ‘Nome do Estado’

From LetrasCombinadas LC Inner Join Placas P

On LC.CodigoSequencialLetrasCombinadas = P.CodigoSequencialLetrasCombinadas

Left Join FaixasDePlacasPorEstado F

On LC.SequencialDeLetrasCombinadas Between SubString(F.CodigoSequencialFaixasDeLetrasNumerosInicial,1,3)

And SubString(F.CodigoSequencialFaixasDeLetrasNumerosFinal,1,3)

Go

A Figura 1 apresentada abaixo, ilustra o possível resultado obtido após a execução do Bloco de Código 4:

Figura 1 – Relação das 100 primeiras placas de carros criadas e inseridas na tabela Placas.

— Bloco de Código 5 —

 

— Criando uma Stored Procedure para pesquisa de placas —

Create or Alter Procedure P_PesquisarPlacas @LetrasCombinadas Char(3), @SequenciaNumerica SmallInt = Null

As

Begin

 

Set NoCount On

 

Select Concat(LC.SequencialDeLetrasCombinadas,’-‘,

Convert(Char(4),Case

When P.CodigoSequencialNumeroPlacas BetWeen 1 And 9 Then Concat(‘000′,P.CodigoSequencialNumeroPlacas)

When P.CodigoSequencialNumeroPlacas BetWeen 10 And 99 Then Concat(’00’,P.CodigoSequencialNumeroPlacas)

When P.CodigoSequencialNumeroPlacas BetWeen 100 And 999 Then Concat(‘0’,P.CodigoSequencialNumeroPlacas)

When P.CodigoSequencialNumeroPlacas BetWeen 1000 And 9999 Then Convert(Char(4),P.CodigoSequencialNumeroPlacas)

End)) As ‘Placa’,

IsNull(F.FaixasDePlacasPorEstadoNomeDoEstado,’Sequência não atribuída…’) As ‘Nome do Estado’

From LetrasCombinadas LC Inner Join Placas P

On LC.CodigoSequencialLetrasCombinadas = P.CodigoSequencialLetrasCombinadas

Left Join FaixasDePlacasPorEstado F

On LC.SequencialDeLetrasCombinadas Between SubString(F.CodigoSequencialFaixasDeLetrasNumerosInicial,1,3)

And SubString(F.CodigoSequencialFaixasDeLetrasNumerosFinal,1,3)

Where LC.SequencialDeLetrasCombinadas = @LetrasCombinadas

And P.CodigoSequencialNumeroPlacas = @SequenciaNumerica

End

Após a Stored Procedure estar criada, basta realizar sua execução conforme o exemplo apresentado abaixo, passando a sequência de letras e números que você deseja consultar.

Importante: Destaco que algumas combinações de placas de carro ainda não estão sendo utilizadas em nosso território, dentre elas as que começam com as letras: W, X, Y e Z.

 

— Executando a Stored Procedure P_PesquisarPlacas —

Exec P_PesquisarPlacas ‘FBD’,3127

Go

Sensacional, chegamos ao final, missão cumprida e entregue, acredito que este foi um dos estudos mais prazerosos e de grande obtenção de conhecimento que eu realizei nos últimos meses.

Antes de encerrarmos, gostaria de contar com a sua participação neste post, respondendo a enquete abaixo:

 

REFERÊNCIAS

https://www.infoescola.com/matematica/analise-combinatoria/

https://mundoeducacao.bol.uol.com.br/matematica/analise-combinatoria.htm

https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql

https://www.mssqltips.com/sqlservertip/1042/using-derived-tables-to-simplify-the-sql-server-query-process/

https://docs.microsoft.com/pt-br/sql/t-sql/language-elements/select-local-variable-transact-sql

 

Posts Anteriores

 

CONCLUSÃO

Como de costume, tenho um imenso prazer em poder compartilhar um pouco das minhas experiências, estudos e conhecimentos que estou diariamente formando sobre este fantástico Sistema Gerenciador de Banco de Dados chamado Microsoft SQL Server.

Hoje não seria diferente, mais uma vez você pode notar o quanto ele é capaz de nos surpreender com sua inteligência, sabedoria, e principalmente sua capacidade de conseguir aplicar as mais variadas técnicas existentes da Matemática para solucionar algo que pode parecer tão complexo para o raciocínio humano.

Neste post, utilizei algumas das mais variadas técnicas para se trabalhar com combinações de dados, utilização de dados em memória através de um Select From (Values()), em conjunto com tabelas criadas fisicamente ou tabelas declaradas como expressões em um CTE.

Por fim, fizemos usado mais uma vez da Matemática aplicando a análise condicional em conjunto com seus elementos de permutação e arranjo de valores.

Através dos cenários apresentados, em conjunto com a gama de recursos que ele nos permite utilizar, conseguimos aplicar as mais variadas preposições de análises de dados que nos possibilita identificar qual cenário pode ser a melhor maneira de se tomar uma decisão.

Este é o fantástico Microsoft SQL Server, produto tão fascinante que a cada dia eu não consigo deixar de querer estudar e conhecer mais ainda.

 

Agradecimentos

Agradeço a você por sua atenção e visita ao meu blog. Fique à vontade para enviar suas críticas, sugestões, observações e comentários.

Nos encontramos no próximo post da sessão Dica do Mês a ser publicado em breve.

Um forte abraço, curta esta nova estação do ano que começou a alguns dias atrás, eu adoro o outono.

Até mais.

Short Scripts – Novembro 2018


Muito boa tarde, já estamos no mês de novembro, e este é mais um post da sessão Short Scripts.

Tudo bem? Já esta se preparando para as festividades de final de ano?

Eu particularmente ainda não, na verdade a grana esta curta então tenho que esperar um pouquinho….

Seguindo em frente, que alegria poder te encontrar em mais um post da sessão Short Scripts, uma das sessões mais recentes do meu blog que esta alçando a marca de 35 posts publicados trimestralmente.

Mantendo a tradição estou retornando com mais um conjunto de “pequenos” scripts catalogados e armazenados em minha biblioteca pessoal de códigos relacionados ao Microsoft SQL Server e sua fantástica linguagem de desenvolvimento Transact-SQL.

Como promessa é dívida e deve ser cumprida “ou melhor” compartilhada, estou compartilhando a minha feita a alguns meses no final do último post desta sessão, publicando mais um conjunto de scripts adicionados atualmente na minha biblioteca particular de códigos e exemplos.

O post de hoje

Normalmente compartilho os principais scripts armazenados nos últimos meses, no post de hoje vou fazer a mesma forma que o anterior, compartilhar scripts relacionados a um tema específico, sendo o tema de hoje a propriedade Identity(), desta maneira, você vai se deparar com alguns códigos que estejam envolvidos com este tema e que também se vinculem com outros, dentre os quais destaco:

  • Ativando e Desativando a propriedade Identity;
  • Capturando o último valor Identity;
  • Comando DBCC CheckIdenty();
  • Função Identity;
  • Ident_Current;
  • Realizando insert com a propriedade Identity;
  • Refazer numeração sequencial;
  • Scope_Identity;
  • Select Into;
  • Sequência Numérica;
  • Set Identity_Insert On / Off;
  • Tabela de sistema sys.indexes;
  • Tabela de sistema sys.objects;
  • Tabela de sistema sys.identity_columns;
  • Tabela de sistema sys.index_column; e
  • Variáveis de sistema @@Identity.

Chegou a hora, mãos nos teclados, a seguir apresento os códigos e exemplos selecionados para o Short Script – Novembro 2018 . Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.

Fique a vontade para compartilhar, comentar e melhorar cada um destes códigos.

Short Scripts

— Short Script 1  – Ativando e Desativando a propriedade Identity() —

SET IDENTITY_INSERT NomedaTabela On
Go

SET IDENTITY_INSERT NomeDaTabela Off
Go

— Short Script 2  – Comando DBCC CheckIdent(), redefinindo a sequência Identity() —
Declare @Identity Int

— Obtendo o último valor Identity() —
Set @Identity=(Select Ident_Current(‘NomedaTabela’))

— Aplicando e redefinindo a sequência Identity através do último valor obtido —
DBCC CheckIdent(‘NomedaTabela‘,Reseed,@Identity)
Go

— Short Script 3  – Identificando se a chave primária possui a propriedade Identity aplicada —
Select O.Object_Id,
O.Name,
Case IC.is_identity
When 0 Then ‘Identity desabilitado’
When 1 Then ‘Identity habilitado’
End As ‘Identity’
From sys.objects O Inner Join sys.identity_columns IC
On O.object_id = IC.object_id
Where IC.is_identity=1

— Short Script 4  – Obtendo o último valor Identity de cada tabela —
SELECT sys.tables.name AS [Table Name],
sys.identity_columns.name AS [Column Name],
sys.types.name as Type,
last_value AS [Last Value]
FROM sys.identity_columns INNER JOIN sys.tables
ON sys.identity_columns.object_id = sys.tables.object_id
Inner join sys.types
on sys.types.user_type_id = sys.identity_columns.user_type_id
ORDER BY last_value DESC

— Short Script 5  – Resentando o valor Identity atualmente em uso — 
DBCC CHECKIDENT (‘NomedaTabela’, RESEED, 0)
Go

— Short Script 6 – Identificando o valor Identity atualmente em uso —
DBCC CHECKIDENT (‘NomedaTabela’, NORESEED)
Go

— Short Script 7 – Utilizando a função Identity() em conjunto com o comando Insert —

— Exemplo 1 —
Create Table Valores
(Codigo Int)

Insert Into Valores Values(1)
Go 100

Select Identity(Int, 2,2) As Linha, Codigo Into Registros from Valores
Go

— Exemplo 2 —
Select identity(int, 1,1) As Seq, name from sys.sysdatabases
Go

Muito bem, missão mais que cumprida! Uma nova relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.


Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.

Acredito que você tenha observado que estes códigos são conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.

Links

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

https://pedrogalvaojunior.wordpress.com/2018/09/18/short-scripts-setembro-2018/

https://pedrogalvaojunior.wordpress.com/2018/05/10/short-scripts-maio-2018/

https://pedrogalvaojunior.wordpress.com/2018/02/19/short-scripts-fevereiro-2018-transaction-log/

https://pedrogalvaojunior.wordpress.com/2017/12/09/short-scripts-dezembro-2017/

Agradecimento

Obrigado mais uma vez por sua visita, fico honrado com sua ilustre presença ao meu blog, desejo e espero que você possa ter encontrado algo que lhe ajudou.

Volte sempre, nos encontraremos mais uma vez na sessão Short Scripts no post a ser publicado somente em 2019, mais especificamente no mês de fevereiro.

Um forte abraço, lhe desejo um ótimo final de ano.

Até mais.

Short Scripts – Setembro 2018


Buenos dias, bom dia, comunidade….

E ai como passaram os últimos meses?

Pergunto isso, devido ao post anterior desta sessão ter sido publicado no mês de maio, posso imaginar que neste intervalo de tempo nossas vidas apresentaram inúmeras situações, espero que todas possam ter sido dentro do possível boas, assim como a minha, e se caso venha a existir algo mais espinhoso, que você possa ter superado.

Seguindo em frente, que alegria poder te encontrar em mais um post da sessão Short Scripts, uma das sessões mais recentes do meu blog que esta alçando a marca de 34 posts publicados trimestralmente.

Mantendo a tradição estou retornando com mais um conjunto de “pequenos” scripts catalogados e armazenados em minha biblioteca pessoal de códigos relacionados ao Microsoft SQL Server e sua fantástica linguagem de desenvolvimento Transact-SQL.

Como promessa é dívida e deve ser cumprida “ou melhor” compartilhada, estou compartilhando a minha feita a alguns meses no final do último post desta sessão, publicando mais um conjunto de scripts adicionados atualmente na minha biblioteca particular de códigos e exemplos.

O post de hoje

Normalmente compartilho os principais scripts armazenados nos últimos meses, no post de hoje vou fazer um pouco diferente, recentemente tive a necessidade de realizar alguns atividades relacionadas as Split de dados e Collation, sendo assim, decide então compartilhar alguns códigos que estejam envolvidos com estes assuntos e que também se vinculem com outros, dentre os quais destaco:

  • Collate Column,
  • Collate,
  • Collation,
  • Database Collate,
  • Database Collation,
  • Instâncias Microsoft SQL Server,
  • Junção de Tabelas,
  • Page Split,
  • Server Collation,
  • Servidores Microsoft SQL Server,
  • String Split,
  • User Defined Function, e
  • Valores separados por vírgulas.

Chegou a hora, mãos nos teclados, a seguir apresento os códigos e exemplos selecionados para o Short Script – Setembro 2018 . Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.

Fique a vontade para compartilhar, comentar e melhorar cada um destes códigos.

Short Scripts

— Short Script 1  – Identificando Page Split – Método 1  —
SELECT cntr_value
FROM sys.sysperfinfo
WHERE counter_name =’Page Splits/sec’ AND
OBJECT_NAME LIKE’%Access methods%’
Go

— Short Script 2  – Identificando Page Split – Método 2  —
SELECT object_name,
counter_name,
instance_name,
cntr_value, cntr_type

FROM sys.dm_os_performance_counters
WHERE counter_name =’Page Splits/sec’ AND
OBJECT_NAME LIKE’%Access methods%’
Go

— Short Script 3  – Criando um User Defined Function para separação de Strings por vírgula  – Método 1 —
CREATE FUNCTION dbo.UFN_SEPARATES_COLUMNS(@TEXT1 varchar(8000), @COLUMN tinyint, @SEPARATOR char(1))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @POS_START int = 1
DECLARE @POS_END int = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)

WHILE (@COLUMN >1 AND @POS_END> 0)
BEGIN
SET @POS_START = @POS_END + 1
SET @POS_END = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
SET @COLUMN = @COLUMN – 1
END

IF @COLUMN > 1 SET @POS_START = LEN(@TEXT) + 1
IF @POS_END = 0 SET @POS_END = LEN(@TEXT) + 1

RETURN SUBSTRING (@TEXT, @POS_START, @POS_END – @POS_START)
END
Go

SELECT
dbo.UFN_SEPARATES_COLUMNS(@ISBN, 1, ‘-‘) AS PREFIX,
dbo.UFN_SEPARATES_COLUMNS(@ISBN, 2, ‘-‘) AS REGISTRATION_GROUP,
dbo.UFN_SEPARATES_COLUMNS(@ISBN, 3, ‘-‘) AS REGISTRANT,
dbo.UFN_SEPARATES_COLUMNS(@ISBN, 4, ‘-‘) AS PUBLICATION,
dbo.UFN_SEPARATES_COLUMNS(@ISBN, 5, ‘-‘) AS [CHECK]
Go

— Short Script 4  – Criando um User Defined Function para separação de Strings por vírgula – Método 2  —

Create FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1

INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end – @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)

END
RETURN
END

Select * from dbo.fnSplitString(‘Querying SQL Server’,”)
Go

— Short Script 5  – Informando o Collate de uma coluna na cláusula Where  —
S
elect campo
From tabela
Where campo collate Latin_General_CI_AS like ‘%á%’
Go

— Short Script 6  – Informando o Collate na declaração de uma coluna e na cláusula Where  —
Select Campo collate Latin_General_CI_AS
From tabela
Where Campo collate Latin_General_CI_AS like ‘%á%’
Go

— Short Script 7  – Alterando o Collate de um Banco de Dados  —
Alter Database MosaicoClient
Collate SQL_Latin1_General_CP1_CI_AI
Go

— Short Script 8  – Alterando o Collate de uma Coluna  —
Alter Table Users
Alter Column [Password] Varchar(12) Collate SQL_Latin1_General_CP1_CS_AS
Go

— Short Script 9  – Identificando o Server Collation  – Método 1 —
Select SERVERPROPERTY (‘collation’)
Go

— Short Script 10 – Identificando o Server Collation  – Método 2 —
Exec sp_helpsort
Go

— Short Script 11 – Identificando o Database Collation —
Select DatabasePropertyEx(‘MRP’,’collation’)
Go

— Short Script 12 – Identificando o Column Collation – Método 1 —
Exec sp_help ‘Produtos’
Go

— Short Script 13 – Identificando o Column Collation – Método 2 —
Select * from Sys.columns
Where Name = ‘Produtos’
Go

— Short Script 14 – Identificando o Column Collation – Método 3 —
Select * from Information_schema.columns
Where Table_Name = ‘Produtos’
Go

Muito bem, missão mais que cumprida! Uma nova relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.


Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.

Acredito que você tenha observado que estes códigos são conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.

Links

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

https://pedrogalvaojunior.wordpress.com/2018/05/10/short-scripts-maio-2018/

https://pedrogalvaojunior.wordpress.com/2018/02/19/short-scripts-fevereiro-2018-transaction-log/

https://pedrogalvaojunior.wordpress.com/2017/12/09/short-scripts-dezembro-2017/

https://pedrogalvaojunior.wordpress.com/2017/09/16/short-scripts-setembro-2017/

Agradecimento

Obrigado mais uma vez por sua visita, fico honrado com sua ilustre presença ao meu blog, desejo e espero que você possa ter encontrado algo que lhe ajudou.

Volte sempre, nos encontraremos mais uma vez na sessão Short Scripts no post do mês de novembro de 2018.

Abraços…

Dica do Mês – Microsoft SQL Server 2017 – SQL Graph Databases


Muito bom dia…… Salve amantes de banco de dados.

Tudo bem? Este é mais um post da sessão Dica do Mês, sessão dedicada a compartilhar mensalmente dicas, novidades, curiosidades e demais informações relacionadas ao Microsoft SQL Server, Banco de Dados e Tecnologias de Banco de Dados.

No post de hoje, quero compartilhar com você uma das maiores novidades implementadas na última versão do Microsoft SQL Server, neste caso, a versão 2017 lançada oficialmente em outubro de 2017 e muito destacada aqui no meu blog em diversos posts.

Destacando um pouco sobre o post, quando se referimos a banco de dados, normalmente pensamos em uma estrutura organizada basicamente em tabelas, colunas, chaves primárias, chaves estrangeiras e relacionamentos. Mas a partir do Microsoft SQL Server 2017 temos uma nova possibilidade de elaborar uma estrutura de banco de dados saindo um pouco deste tradicional cenário conhecimento como modelo relacional e sim partindo para o chamado modelo grafos ou banco de dados em grafos. Talvez em algum momento você já deve ter ouvido falar um pouco sobre esta forma de modelagem.

Pois bem, neste nova versão o time de engenheiros do SQL Server adicionaram ao conjunto de novas funcionalidades (features) o SQL Graph Databases ou simplesmente Banco de Dados em Grafos, isso mesmo banco de dados no formato de grafos, parece ser algo muito diferente do que estamos acostumados a fazer, na verdade não é bem assim, e você vai poder conhecer um pouco mais sobre este recurso, suas características e curiosidades no decorrer deste post.

Esta curioso em saber um pouco mais sobre esta nova feature? Eu estou, e não vejo a hora de poder dividir com você um pouco do vasto conteúdo relacionado com este recurso. Então, vamos em frente, vou tentar mitigar a sua curiosidade e ao mesmo também satisfazer os meus objetivos. Sendo assim, seja bem vindo ao post – Dica do Mês – Microsoft SQL Server 2017 – SQL Graph Databases.

Você vai se surpreender com este novo recurso e suas possibilidades relacionadas com modelagem de banco de dados.


Introdução

O SQL Graph Database é uma nova forma de se estruturar um banco de dados criado no Microsoft SQL Server 2017, tendo como base um estrutura formada por uma coleção de tabelas de nó (Node Table) e  tabelas de borda (Edge Table).

Basicamente um nó representa uma entidade — por exemplo, uma pessoa ou uma organização e uma borda representa uma relação entre os dois nós que ele conecta. As tabelas de nó ou borda podem ser criadas em qualquer esquema em um banco de dados, mas todas pertencem a uma estrutura de grafos representada de forma lógica.

Os bancos de dados na estrutura de grafos são úteis quando o aplicativo tem relacionamentos complexos de muitos para muitos e precisamos analisar as relações complexas.

Algumas das características importantes de um bancos de dados na estrutura de grafos:

  • Bordas (edge) ou relacionamentos(node) são entidades de primeira classe em um banco de dados de grafos e podem ter atributos ou propriedades associadas a eles;
  • Uma única edge table pode unir flexivelmente vários nós em um banco de dados de grafos;
  • Demonstrar a relação de padrões e consultas de navegação de vários saltos facilmente; e
  • Demonstrar o encerramento transitivo de dados e as consultas polimórficas facilmente.

A Figura 1 abaixo apresenta a estrutura básico do SQL Graph Databases e seus principais componentes:

Architecture of SQL Server 2017 Graph Database
Figura 1 – Estrutura básica do SQL Graph Databases.

Analisando a Figura 1 apresentada acima, podemos dizer que um banco de dados na estrutura de grafos é um tipo de banco de dados cujo conceito é baseado em nós e bordas. Este novo tipo de bancos de dados, denominada de grafos, baseiam-se na teoria dos grafos (um grafo é um diagrama de pontos e linhas conectados aos pontos), respeitando a seguinte estrutura:

  1. Os nós representam dados ou entidade e bordas representam conexões entre nós; e
  2. As bordas são propriedades que podem estar relacionadas a nós, essa capacidade nos permite mostrar interações mais complexas e profundas entre os nossos dados.

Elementos básicos

A seguir destaco os elementos básicos que compõem a estrutura do SQL Graph Databases:

Node Table
Representa uma entidade em um esquema de grafos. Sempre que criamos uma tabela de nós, juntamente com as colunas definidas pelo usuário, uma coluna implícita $node _id é criada, o que identifica exclusivamente um determinado nó no banco de dados.

Os valores na coluna $node _id são gerados automaticamente e são uma combinação de object_id dessa tabela de nós e um valor bigint gerado internamente. No entanto, quando a coluna $node _id é selecionada, um valor calculado na forma de uma cadeia de caracteres JSON é exibido.
Além disso, $Node _id é uma coluna pseudo, que mapeia para um nome interno com String hex nele. Quando selecionamos $node _id da tabela, o nome da coluna aparecerá como $node _id_ hex_string.

É recomendável que os usuários criem uma restrição ou índice exclusivo na coluna $node _id no momento da criação da tabela de nós, mas se um não for criado, um índice padrão exclusivo não clusterizado será criado automaticamente.

Edge Table
Como mencionado anteriormente, uma tabela de borda(Edge Table) representa uma relação em um grafos. As bordas são sempre direcionadas e conectam dois nós.

Uma tabela de borda permite que os usuários modelem relacionamentos muitos-para-muitos no grafos, esta mesma tabela pode ou não ter quaisquer atributos definidos pelo usuário.

$Edge _id
A primeira coluna na tabela de borda representa $Edge _id que identifica exclusivamente uma aresta fornecida no banco de dados. O valor da coluna edge_id é gerado com a combinação de object_id da tabela de borda e um valor bigint gerado internamente.

No entanto, quando selecionamos a coluna $Edge _id, ela é exibida como a seqüência de caracteres JSON que é calculada a partir do valor da coluna.

$from _id
Coluna que armazena o $node _id do nó, de onde a borda é originada. Semelhante ao $Edge _id este é também um pseduo e pode ser usado como $from _id no entanto, o nome da coluna inclui Strings hex nele.

$to _id
Armazena o $node _id do nó, no qual a borda termina. Comportamento desta coluna em também como por $Edge _id e $from coluna _id.

Funções

Existem algumas funções adicionadas a linguagem Transact-SQL, que visam ajudar os usuários a extrair informações das colunas geradas. Abaixo estão as funções:

OBJECT_ID_FROM_NODE_ID: Função que permite extrair o object_id de um node_id. Precisamos passar node_id para esta função e ele retornará o object_id, também podemos obter o nome do objeto do object_id.

GRAPH_ID_FROM_NODE_ID: Função que permite extrair o GRAPH_ID de um node_id. Precisamos passar node_id para esta função e ele retornará o object_id.

NODE_ID_FROM_PARTS: Através desta função podemos construir um node_id de um object_id e um graph_id.

OBJECT_ID_FROM_EDGE_ID: Função utilizada para extrair um object_id de um EDGE_ID.

GRAPH_ID_FROM_EDGE_ID: Função utilizada para identificar um GRAPH_ID de EDGE_ID.

EDGE_ID_FROM_PARTS: Função que nos permite identificar o id utilizada entre as conexões de EDGE_ID de object_id e identidade.

Tabelas de Sistemas e Metadados

Sys.Tables – Novas colunas foram adicionadas ao sys.tables para identificar se uma tabela é um nó ou uma borda, conforme apresenta a Tabela 1 abaixo:

Column Name Data Type Description
is_node bit 1 = this is a node table
is_edge bit 1 = this is an edge table

Tabela 1 – Novas colunas adicionadas a system table sys.tables.

Sys.Columns – Novas colunas foram adicionadas ao sys.tables para indicar o tipo da coluna em tabelas de nó e borda, permitindo o relacionamento entre as systems tables sys.columns e sys.tables. A Tabela 2 abaixo apresenta a relação de novas colunas adicionadas a sys.columns:

Column Name Data Type Description
graph_type int Internal column with a set of values.

The values are between 1-8 for graph columns and NULL for others:

1 – GRAPH_ID
2 – GRAPH_ID_COMPUTED
3 – GRAPH_FROM_ID
4 – GRAPH_FROM_OBJ_ID
5 – GRAPH_FROM_ID_COMPUTED
6 – GRAPH_TO_ID
7 – GRAPH_TO_OBJ_ID
8 – GRAPH_TO_ID_COMPUTED

graph_type_desc nvarchar(60) internal column with a set of values

Tabela 2 – Novas colunas adicionadas a system table sys.columns.

Nossa, quanta coisa nova foi adicionado ao Microsoft SQL Server a partir desta nova funcionalidades, como também, diversas mudanças internadas como de costume também foram realizadas no produto afim de possibilitar o uso destas e outras funcionalidades, algo comumente realizado a cada nova versão.

Vamos então conhecer de forma prática o SQL Graph Databases e entender como estes elementos podem ser utilizados através do cenário que estaremos implementando a partir de agora.

Implementando o SQL Graph Databases

Se estamos nos referindo ao um banco de dados de grafos, nada melhor do que representar a estrutura que será utilizada em nosso cenário de exemplo através de um Diagrama conforme ilustra a Figura 2 abaixo:

Figura 2 – Diagrama – Estrutura utilizada para o exemplo.

Os nós são SQL Server, Azure e Windows e segue (bordas) fornecem conexões entre nós. Este modelo de banco de dados não pode ser tratado como uma alternativa a um modelo de banco de dados relacional, mas confrontados com alguns problemas específicos, o modelo de banco de dados em grafos pode ser alternativo e efetivo.

Observações: Se você olhar para o diagrama de perto, talvez você pode projetar este modelo de dados em um banco de dados relacional por junções, mas imagine que se você tiver um monte de nós e bordas, em seguida, quantas junções você vai precisar? Outra consideração importante se relaciona na forma de como este projeto funcionaria? Por esta razão, ao lidar com alguns problemas de negócios, precisamos de um banco de dados representado na teoria de grafos.

Um cenário muito interessante para a adoção de um banco de dados em grafos a ser considerado são meios de comunicação social. Por exemplo, há um monte de ações sociais, cada ação social cria uma marca. Quando combinamos estas marcas, parece uma teia de aranha. O modelo de banco de dados em grafos é ideal para armazenar esse tipo de dados.

Criando o ambiente

Para nossa prática vamos trabalhar com o seguinte ambiente:

  • Database – SQLGraphDatabase;
  • Node Table – Products; e
  • Edge Table – Microsoft.

Vamos começar criando nosso banco de dados e a node table através do Bloco de Código 1 apresentado na sequência:

— Bloco de Código 1 – Criando Banco de Dados e Node Table —

— Criando o Banco de Dados —
Create Database SQLGraphDatabases
Go

— Acessando o Banco de Dados —
Use SQLGraphDatabases
Go

— Criando a Node Table Products —
Create Table Products
(ProductID TinyInt Primary Key,
ProductName Varchar(100)
) As Node
Go

Note que instrução Create Table especificamos ao final da mesma a palavra chave Node, desta forma, o Microsoft SQL Server vai entender que nossa tabela será um nó e posteriormente estará relacionada com no mínimo uma borda.

O próximo passo é realizar a inserção de dados na tabela Products, para isso vamos utilizar o Bloco de Código 2 apresentado a seguir:

— Bloco de Código 2 – Inserindo dados na Node Table Products —

— Inserindo dados na Node Table Products —
Insert Into Products
Values (1,’SQL Server’),
(2,’Azure’),
(3,’Windows’)
Go

— Consultando os dados —
Select ProductId, ProductName from Products
Go

Você poderá notar que após a execução do comando Select teremos o resultado das três linhas de registros lógicos adicionadas a node table Products apresentados em tela, para que seja possível apresentar toda estrutura da tabela incluindo o $Node_Id, vamos então utilizar um simples e conhecido Select * (técnica não recomendável, mas para este cenário será útil para apresentar de maneira rápida a estrutura da node table Products).

— Apresentando toda estrutura da node table Products e seus respectivos dados —

Select * From Products
Go

A Figura 3 abaixo ilustra o retorno dos dados obtidos após a execução do Select acima:

Figura 3 – Estrutura da node table Products, incluindo $Node_ID e dados.

Continuando a nossa caminhada, vamos agora criar nossa tabela borda (edge table) denominada Microsoft, sendo esta o elemento utilizado para estabelecer as ligações entre os dados da node table para com a edge table, desta forma utilizaremos o Bloco de Código 3 apresentado a seguir:

— Bloco de Código 3 – Criando a Edge Table Microsoft —

— Criando a Edge Table Microsoft —
Create Table Microsoft As Edge
Go

— Consultando a estrutura da Edge Table Microsoft —
Select * from Microsoft
Go

Pronto nossa tabela borda esta criada, nosso penúltimo passo será adicionar os dados oriundos da node table Products, estabelecem assim as relações entre ambos os elementos, para tal procedimentos utilizaremos o Bloco de Código 4 apresentado a seguir:

— Bloco de Código 4 – Inserindo os dados na Edge Table Microsoft —

— Azure com SQL Server —
Insert Into Microsoft ($from_id , $to_id )
Values ((Select $node_id from Products where ProductId=2),
(Select $node_id from Products where ProductId=1))
Go

— Windows com SQL Server —
Insert Into Microsoft ($from_id ,$to_id )
Values ((Select $node_id from Products where ProductId=3),
(Select $node_id from Products where ProductId=1))
Go

— Windows com Azure —
Insert Into Microsoft ($from_id ,$to_id )
Values ((Select $node_id from Products where ProductId=3),
(Select $node_id from Products where ProductId=2))
Go

— Consultando os dados inseridos na Edge Table Microsoft —
Select * from Microsoft
Go

Muito bom, acabamos de estabelecer as ligações entre nossa node table e a edge table, note que para realizar este vinculo entre os dados oriundas da node table Products para a edge table Microsoft, utilizamos os valores da coluna ProductID existente na tabela de Products, como elemente chave a ser inserido nas colunas $from_id e $to_id no momento da criação da edge table Microsoft.

Figura 4 a seguir ilustra os dados inseridos na edge table Microsoft e apresentados no formato de string JSON:

Figura 4 – Dados inseridos na edge Table Microsoft.

Agora falta muito pouco e chegamos ao último passo que consiste justamente em identificar as conexões estabelecidas com base no Bloco de Código 4 processado anteriormente, onde através da nova função Match() em conjunto com seus operadores ‘-‘ ou ‘->’ podemos definir o caminho para encontrar os dados respeitando a estrutura de nós e bordas. Para tal procedimento executaremos o Bloco de Código 5 apresentado abaixo:

— Bloco de Código 5 – Identificando as conexões entre os dados —

— Utilizando a função Match(), encontrando as conexões do ProductName = Azure —
Select Concat(Products.ProductName,’ –> ‘, ProductsDetails.ProductName) As Connections
From Products, Microsoft, Products ProductsDetails
Where Match(Products-(Microsoft)->ProductsDetails)
And Products.ProductName = ‘Azure’
Go

— Utilizando a função Match(), encontrando as conexões do ProductName = Windows —
Select Concat(Products.ProductName,’ –> ‘, ProductsDetails.ProductName) As Connections
From Products, Microsoft, Products ProductsDetails
Where Match(Products-(Microsoft)->ProductsDetails)
And Products.ProductName = ‘Windows’
Go

Após a execução do Bloco de Código 5 apresentado anteriormente o Management Studio deverá retornar um resultado similar ao apresentado pela Figura 5 abaixo:

Figura 5 – Conexões de dados estabelecidas com base nas colunas $from_id  e $to_id.

Incrível, chegamos ao final de mais um post da sessão Dica do Mês, tenho a certeza que você gostou do conteúdo apresentado hoje neste post.

Vale ressaltar que alguns pontos não foram abordados neste post, dentre eles como utilizar um índice clusterizado ou não clusterizado, como também, a maneira que uma Node Table e Edge Table são apresentadas na estrutura de um banco de dados através do Management Studio.

No que diz respeito ao novo modelo de banco de dados, tenha a certeza que a minha missão foi cumprida, principalmente no entendimento de como esta uma nova forma de representação baseada em grafos poderá nos ajudar a estabelecer uma nova técnica de relacionamento entre nossas tabelas e seus respectivos dados, indo muito além dos tradicionais conceitos dentre eles: chaves primárias e estrangeiras, caminhando para uma nova proposta com base no conceito de relacionamento lógico de dados.

Antes de encerrarmos, gostaria de contar com a sua participação neste post, respondendo a enquete abaixo:


Referências

https://docs.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-sample

https://docs.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-architecture

https://docs.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-overview

https://www.red-gate.com/simple-talk/sql/sql-development/sql-server-graph-databases-part-1-introduction/

https://www.sqlshack.com/implement-graph-database-sql-server-2017/

https://www.mssqltips.com/sqlservertip/4883/sql-server-2017-graph-database-example/

 

Posts Anteriores

https://pedrogalvaojunior.wordpress.com/2018/01/24/dicadomes-sqlservertoolsuiteintroduction/

https://pedrogalvaojunior.wordpress.com/2017/11/01/dicadomessql2017novascolunasinternas/

https://pedrogalvaojunior.wordpress.com/2017/08/26/dica-do-mes-simulando-a-insercao-de-uma-massa-de-dados-aleatoria/

https://pedrogalvaojunior.wordpress.com/2017/05/23/conhecendo-e-aplicando-o-uso-de-atualizacao-de-estatisticas-incrementais/

Conclusão

Neste post, conhecemos um pouco sobre este novo recursos SQL Graph Databases implementado a partir da versão 2017 do Microsoft SQL Server.

O SQL Server Graph é um recurso fantástico que nos permite implementar dentro da mesma estrutura de banco de dados existente no Microsoft SQL Server dois modelos de relacionamento de dados totalmente diferentes.  Através desta arquitetura híbrida temos a capacidade de utilizar recursos do mecanismo do SQL Server com um banco de dados na estrutura de grafos de maneira muito similar para não se dizer parecida com os recursos aplicados no modelo relacional, com base, na linguagem Transact-SQL nos dando todo suporte a consultas para este novo formato.

O banco de dados baseados na estrutura de grafos (SQL Graph Databases) apresentam como toda nova tecnologia algumas limitações técnicas neste momento, que tendem nas próximas versões do produto evoluírem ou até mesmo deixar de existir, algo que neste momento não podem ser consideradas limitações que impossibilitem a sua adoção ou uso em novos projetos de bancos de dados, como também, não se tornem  limitações a outros recursos excepcionais no existentes no Microsoft SQL Server 2017, mesmo assim podemos reconhecer que este novo modelo de banco de dados, pode ser considerada uma tecnologia convincente e promissora.

Agradecimentos

Agradeço a você por sua atenção e visita ao meu blog. Fique a vontade para enviar suas críticas, sugestões, observações e comentários.

Um forte abraço, nos encontramos no próximo post a ser publicado no mês de abril.

Viva o Microsoft SQL Server 2017 e suas novas funcionalidades…

Um forte abraço, sucesso e até o próximo post.

Script Challenge – 13 – A resposta….


Salve pessoal, bom dia.

Tudo bem?  Seja mais uma vez muito bem vindo ao meu blog, mais especificamente ao post que apresenta a resposta para o Script Challenge – 2017, publicado em outubro de 2017 destacando o retorno da sessão Script Challenge (Script Desafiador ou Desafio do Script) como queiram traduzir.

Espero que você já tenha ouvido falar desta sessão ou acessado alguns dos posts publicados na mesma, caso ainda não tenha feito, fique tranquilo você vai encontrar no final deste post uma pequena relação contendo os últimos desafios lançados e seus respostas.

Vamos então falar um pouco mais sobre o último desafio, estou me referindo ao Script Challenge 13, sendo assim, seja bem vindo a mais um post da sessão Script Challenge.


Script Challenge 13

Falando do desafio de número 13, o mesmo foi publicado no mês de outubro de 2017, período de data que apresenta uma das comemorações mais importantes que ocorrem anualmente em quase todos os países do mundo, mas que especialmente no Estados Unidos da América.

E ai já matou a charada? Eu acredito que sim! Mas para te ajudar mais um pouco vou apresentar a Figura 1 que contem todo código Transact-SQL utilizado neste desafio, contendo trechos ou partes de código ocultas, procedimento que realizei no post que contempla o lançamento deste desafio como forma de aumentar o nível de dificuldade:

Figura 1 – Código Transact-SQL apresentado no Script Challenge 13.

Bom chegou a hora de revelar o que exatamente este pequeno bloco de código esta fazendo, chegou o momento de revelar e desvendar este desafio, a seguir apresento a resposta para o Script Challenge 13 e o trecho de código disponível para você utilizar em seus ambientes de trabalho ou estudos.

A resposta

Tanto no post de lançamento do desafio, bem como, neste post de apresenta a resposta para o mesmo, eu deixei algumas pequenas dicas para tentar ajudar a identificar a resposta, dentre as quais a relação do script com uma das datas comemorativas mais tradicionais dos Estados Unidos, neste caso o Halloween(conhecido tradicionalmente como dia das bruxas).

Mesmo assim você pode estar se perguntando, o que Script Challenge 13 tem haver com dia das bruxas, ué tudo haver, pois quando falamos de bruxas, temos também em mente a relação com magia, fantasias, medo, terror e propriamente a fantasmas, isso mesmos fantasmas, algo que também pode acontecer em nossas tabelas com o passar do tempo conforme vamos realizando as manipulações de dados, torna-se possível se deparar com a ocorrência de possíveis dados fantasmas.

Então a resposta para o Script Challenge 13 é justamente a possibilidade que o script apresenta em identificar uma possível ocorrência de dados fantasmas em nossas tabelas e bancos de dados.

Isso mesmo, esta é a resposta, e o script original que apresenta esta funcionalidade apresentado abaixo:

— Script Challenge 13 – A resposta – Identificando a ocorrência de dados fantasmas —

SELECT db_name(database_id),
                object_name(object_id),
                ghost_record_count,
                version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N’GhostDB’),
                                                                            OBJECT_ID(N’GhostTable’),
                                                                            NULL,
                                                                            NULL ,
                                                                            ‘DETAILED’)
GO

Que coisa de louco isso Galvão! Sim realmente parece ser coisa de outro mundo, mas podem acontecer tranquilamente a qualquer momento, o complicado é imaginar, que nossos bancos de dados, podem ser vítimas deste tipo de situação.

Mas isso não é o fim do mundo, muito ao contrário, para este cenário temos uma grande vantagem. Você pode estar se perguntando qual, a resposta é simples, não precisamos chamar os Casas Fantasmas, nós mesmos podemos resolver facilmente isso. Foi pensando justamente nas possibilidades da ocorrência deste tipo de situação, que além da compartilhar a resposta para este desafio, vou deixar também um cenário de simulação de como é possível ocorrência a existência de dados fantasmas, como também a possibilidade de excluir estes “dados”, a seguir:

— Simulando a ocorrência de dados fantasmas —

— Criando o Banco de Dados – GhostDB —
Create Database GhostDB
Go

— Acessando o Banco de Dados —
Use GhostDB
Go

— Criando a Tabela GhostTable —
Create Table GhostTable
(GhostRecord Int)
Go

— Criando um índice clusterizado —
Create Clustered Index Ind_GhostTable_GhostRecord On GhostTable(GhostRecord)
Go

— Inserindo Dados na Tabela GhostTable —
Insert Into GhostTable
Select 100
Go

— Obtendo informações sobre as estatísticas de alocação de dados —
Select object_id,
index_id,
index_depth,
index_level
From sys.dm_db_index_physical_stats(db_id(),
object_id(‘GhostTable’),
object_id(‘Ind_GhostTable_GhostRecord’),
null,
null)
Go

— Obtendo informações sobre o Índice IND_GhostTable_GhostRecord —
Select id, name, root, first
from sys.sysindexes
where id=565577053 — Aqui você vai colocar o ID identificado do índice apresentado na sua máquina —
Go

— Identificando a página de dados que contem os dados inseridos na GhostTable —
SELECT first_page,
(convert(varchar(2), (convert(int, substring(first_page, 6, 1)) * power(2, 8)) +
(convert(int, substring(first_page, 5, 1)))) + ‘:’ + convert(varchar(11),
(convert(int, substring(first_page, 4, 1)) * power(2, 24)) +
(convert(int, substring(first_page, 3, 1)) * power(2, 16)) +
(convert(int, substring(first_page, 2, 1)) * power(2, 8)) +
(convert(int, substring(first_page, 1, 1))))) As Page
FROM SYS.SYSTEM_INTERNALS_ALLOCATION_UNITS
Where first_page = 0x180100000100 — Valor obtido no bloco de código anterior através da coluna root —
Go

— Habilitando a Trace Flag 3604 para apresentar informações sobre as páginas de dados —
DBCC TRACEON (3604)
GO

— Consultando informações sobre as páginas de dados relacionadas o índice Ind_GhostTable_GhostRecord —
DBCC PAGE(GhostDB,1,280,1)
Go

Após obter o resultado do DBCC Page procure pela coluna m_ghostRecCnt, neste momento ela deve esta apresentando o valor m_ghostRecCnt = 0.

— Excluíndo os registros em GhostTable —
Delete from GhostTable
Where GhostRecord=100
Go

— Consultando informações sobre as páginas de dados relacionadas o índice Ind_GhostTable_GhostRecord —
DBCC PAGE(GhostDB,1,280,3)
Go

Agora verifique novamente a coluna m_ghostRecCnt que neste momento deverá apresentar o valor igual á m_ghostRecCnt = 1, este é o indicador da ocorrência de um dado fantasma em nossa tabela.

— Confirmando a existência de um registro fantasmas —
SELECT db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N’GhostDB’), OBJECT_ID(N’GhostTable’), NULL, NULL , ‘DETAILED’)
GO

— Simulando a eliminação de dados fantasmas —
Alter Table GhostTable
Rebuild — Utilize este tipo de procedimento em uma tabela com poucos registros —
Go

— Confirmando a existência de um registros fantasmas —
SELECT db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N’GhostDB’),
OBJECT_ID(N’GhostTable’),
NULL,
NULL ,
‘DETAILED’)
GO

— Liberando espaço alocado anteriormente em disco pelos registros fantasmas —
Exec sp_clean_db_free_space @dbname=N’GhostDB’
Go

Meu deus, conseguimos, chegamos ao final, esta é a resposta para o Script Challenge 13, sinceramente falando achei que não iria conseguir compartilhar este conteúdo com vocês.

Espero que tenham gostado desta da volta desta sessão, como também, a nova maneira que pretendo apresentar os desafios e seus respostas.


Sua Participação

No post de lançamento deste desafio, contei com a participação através de uma enquete contendo algumas opções de respostas que poderiam estar relacionadas com o Script Challenge 13. A seguir apresento o resultado desta enquete:

A opção mais votada com 66,67% dos votos é justamente a resposta correta para este desafio, o qual retorna ao usuário informações relacionadas a identificação de páginas de dados com fragmentação.

Referências

Agradecimentos

Obrigado por sua visita, espero que o retorno desta sessão e o conteúdo aqui apresentado como um possível “desafio” possam ser úteis e ao mesmo tempo prover conhecimento, aprendizado ou mostrar recursos e problemas existentes no Microsoft SQL Server que as vezes parecem não ter uma resposta.

Um forte abraço nos encontramos em breve nas demais sessões e especialmente em junho de 2018 em mais um post da sessão Script Challenge.

Até a próxima…

#16 – Para que serve


Fala galera, boa noite.

Último dia do mês de setembro e como costumo dizer o tempo esta passando a cada dia de uma maneira mais rápida que não conseguimos nos deparar com tudo o que acontece ao nosso redor.

São exatamente 22:07 hrs da noite de um sábado que já esta chegando ao fim e com ele o final também de mais um mês de muita luta e trabalho neste ano de 2017, eita ano complicado.

Mas se você esta lendo este post é porque de alguma maneira acabou chegando até aqui, muito obrigado por sua visita, sendo este o post de número 16 da sessão Para que serve, já estava passando da hora de publicar mais um conteúdo exclusivo para esta sessão.

O conteúdo de hoje é um assunto que eu pouco trabalhei em toda minha carreira de DBA, olha que já fazem 17 anos que venho me dedicando diariamente a esta função, mas o recurso que será destacado hoje não é assim “tão velhinho” como eu (kkkkk).

Hoje vou destacar um pouco sobre índices, ai você pode se perguntar, poxa vida você pouco trabalhou com índices, como assim? Pois é, a resposta tem uma pequena parte de verdade, o tipo de índice ao qual eu me refiro pois adicionado ao Microsoft SQL Server a partir da versão 2012, agora acredito que ficou fácil saber de qual índice estou me referindo, se você ainda não se lembrou ou não se atentou para a dica, estou me referindo ao ColumnStore Index. Agora, tenho a certeza que você se lembrou desta nova forma de armazenamento de dados criada pelo time de engenheiros do Microsoft SQL Server a partir da versão 2012 e que ao longo das próximas versões sofreu diversas atualizações e melhorias.

Se a sua resposta é não ou sinceramente você não conhece esta “nova maneira” de trabalhar com índice, não fique preocupado, como de costume vou fazer uma breve introdução sobre este recurso, como também, vamos fazer alguns comparativos em relação a espaço em disco ocupado e custo de processamento em relação aos tradicionais índices Clustered e NonClustered, sendo estes conhecidos como índices Row Store.

Então chegou a hora de conhecer um pouco mais sobre este recurso, desta forma, seja bem vindo ao #16 – Para que serve – Algumas características e comportamentos do ColumnStore Index.

Introdução

Segundo a documentação oficial do Microsoft SQL Server,  índice columnstore é o padrão para armazenar e consultar grandes tabelas de fatos de repositório de dados. 

Um columnstore index é uma tecnologia para armazenamento, recuperação e gerenciamento de dados usando um formato de dados colunar, chamado columnstore.

Ele usa armazenamento de dados baseado em coluna e processamento de consultas para alcançar um desempenho de consulta até 10 vezes melhor no data warehouse em relação ao armazenamento tradicional orientado por linha e até 10 vezes mais compactação de dados em relação ao tamanho dos dados descompactados.

A partir do SQL Server 2016, os índices columnstore permitem a análise operacional, a capacidade de executar análises de alto desempenho em tempo real em uma carga de trabalho transacional. 

ColumnStore Vs RowStore

Um columnstore são dados logicamente organizados como uma tabela com linhas e colunas e fisicamente armazenados em um formato de dados com reconhecimento de coluna.

Um rowstore são dados logicamente organizados como uma tabela com linhas e colunas e fisicamente armazenados em um formato de dados com reconhecimento de linha. No SQL Server, rowstore refere-se à tabela em que o formato de armazenamento de dados subjacente é um heap, um índice clusterizado ou uma tabela com otimização de memória.

Característica 1: Por padrão até a versão 2012 todo e qualquer índice do tipo Clustered e NonClustered ao ser criado era associado ao mesmo uma estrutura de armazenamento de dados utilizando a tecnologia de RowStore, neste caso, ao invés de conter somente a coluna relacionada ao índice o mesmo possuía a linha por completa o que acarretava um aumento considerável de espaço em disco, como também, um custo de processamento maior para o SQL Server quando o dado solicitado era pesquisado.

Seguindo em frente, já sabemos um pouco sobre esta nova maneira de se armazenar dados contidos em um índice, característica muito importante e que pode nos trazer grandes benefícios.

Quais podem ser os outros benefícios ou características nativas existentes no ColumnStore Index que podem nos fazer adotar este tipo de tecnologia? Esta e muitas outras perguntas que eu mesmo sempre fiz em relação á este tipo de índice serão respondidas a seguir, através do ambiente que estaremos utilizando neste post.

Ambiente

Para que possamos entender ainda mais sobre o ColumnStore Index, nada melhor do que colocar em prática este recurso, para tal vamos começar a montar nosso ambiente de teste, composto por três tabelas similares denominadas:

  • MassaDeDadosCompressaoPorRow;
  • MassaDeDadosCompressaoPorPage; e
  • MassaDeDadosCompressaoColumnStore.

Estes três tabelas serão formadas por duas simples colunas denominadas:

  • Record; e
  • Number.

Onde a coluna Record será utilizada como identificar das linhas de registros que estão sendo manipuladas, sendo seu valor preenchido de forma automática através da propriedade Identity().

A coluna Number corresponde ao número que será inserido de forma aleatório neste coluna, esta será a coluna que iremos utilizar para a construção dos respectivos índices em cada tabela.

Vamos então através do Bloco de Código 1 realizar a criação de cada tabela, suas colunas e seus respectivos índices. Vale ressaltar que além de índices nas tabelas MassaDeDadosCompressaoPorRow e MassaDeDadosCompressaoPorPage estamos também utilizando compressão de dados aplicada a linha e compressão de dados aplicada a página de dados respectivamente. 

A seguir apresento o Bloco de Código 1:

— Bloco de Código 1 —

Use TempDB
Go

Create Table dbo.MassaDeDadosCompressaoPorRow
(Record Bigint Identity(1,1),
 Number BigInt
 Index IND_MassaDeDadosCompressaoPorRow Clustered (Number))
WITH (DATA_COMPRESSION = ROW)
Go

Create Table dbo.MassaDeDadosCompressaoPorPage
(Record Bigint Identity(1,1),
 Number BigInt
 Index IND_MassaDeDadosCompressaoPorPage Clustered (Number))
WITH (DATA_COMPRESSION = PAGE)
Go

Create Table dbo.MassaDeDadosCompressaoPorColumnStore
(Record Bigint Identity(1,1),
 Number BigInt,

 INDEX IND_MassaDeDadosCompressaoPorColumnStore Clustered  ColumnStore)
Go

Por enquanto nada de muito inovador ou diferente do que você pode estar acostumado a fazer, mas uma segunda característica do ColumnStore Index pode ser identificada após a criação das tabelas.

Característica 2: Ao realizar a criação de um índice ColumnStore, o Storage Engine mecanismo utilizado pelo Microsoft SQL Server para armazenamento físico de dados, realiza a alocação de um espaço mínimo inicial para armazenar a estrutura base da coluna relacionada ao índice, ou seja, mesmo não possuindo não dado armazenado no ColumnStore um pequeno espaço em disco é ocupado por sua estrutura e respectiva coluna. Para comprovar esta característica utilizamos o Bloco de Código 2 apresentado abaixo:

— Bloco de Código 2 —

Select s.Name As SchemaName, t.Name As TableName, p.rows As RowCounts,
            Cast(Round((Sum(a.used_pages) / 128.00), 2) As Numeric(36,2)) As Used_MB,
             Cast(Round((Sum(a.total_pages) – SUM(a.used_pages)) / 128.00, 2) As Numeric(36, 2)) As Unused_MB,
            Cast(Round((Sum(a.total_pages) / 128.00), 2) As Numeric(36, 2)) As Total_MB
From sys.tables t Inner Join sys.indexes i
                                 On t.OBJECT_ID = i.object_id
                                Inner Join sys.partitions p
                                 On i.object_id = p.OBJECT_ID And i.index_id = p.index_id
                                Inner Join sys.allocation_units a
                                On p.partition_id = a.container_id
                                 Inner Join sys.schemas s
                                 On t.schema_id = s.schema_id
Where t.object_id >1
Group By t.Name, s.Name, p.Rows
Order By s.Name, t.Name
Go 

Agora que você executou o Bloco de Código 2, deve ter obtido como resultado ao similar ao apresentado na Figura 1 a seguir:


Figura 1 – Espaço ocupado pelo ColumnStore Index mesmo são a existência de linhas de registro.

Legal, mas ainda temos algumas coisas para analisar em relação ao ColumnStore Index, nosso próximo passo será realizar o armazenamento de uma massa de dados em cada tabela, aonde estará identificando o tempo de processamento decorrido para inserir um milhão de linhas de registro, valor hoje considerado pouco para se trabalhar com qualquer banco de dados e principalmente para estruturas ColumnStore, mas o suficiente para nossas análises e comparações.

Vamos então trabalhar com o Bloco de Código 3 a seguir:

— Bloco de Código 3 —

— Inserindo 1.000.000 na Tabela MassaDeDadosCompressaoPorRow –|

Declare @Contador Int = 1

While @Contador <=1000000
Begin
Insert Into dbo.MassaDeDadosCompressaoPorRow (Number)
Values (Rand()*1000000000000)

Set @Contador +=1
End
Go

— Inserindo 1.000.000 na Tabela MassaDeDadosCompressaoPorPage —

Declare @Contador Int = 1

While @Contador <=1000000
Begin
Insert Into dbo.MassaDeDadosCompressaoPorPage (Number)
Values (Rand()*1000000000000)

Set @Contador +=1
End
Go

— Inserindo 1.000.000 na Tabela MassaDeDadosCompressaoPorColumnStore —

Declare @Contador Int = 1

While @Contador <=1000000
Begin
Insert Into dbo.MassaDeDadosCompressaoPorColumnStore (Number)
Values (Rand()*1000000000000)

Set @Contador +=1
End
Go

Neste momento nossas tabelas já estão totalmente populadas com 1.000.000 de linhas de registros armazenadas em cada uma delas, vamos então identificar mais duas características presentes no ColumnStore Index, começando pela número 3.

Característica 3: ColumnStore Index apresentam em alguns cenários de armazenamento de dados dependendo do tipo de dados utilizado por ele uma ligeira perda de performance em comparação com o mesmo tipo de dados utilizado em índices que utilizam tecnologia RowStore.

Para comprovar e ajudar a entender esta diferença de comportamento apresento a seguir a Tabela 1 que exibe os dados coletados durante os três processos de inserir realizado pelo Bloco de Código 3 declarado anteriormente.

Tabela Operação Tempo Decorrido
MassaDeDadosCompressaoPorRow Insert 00:00:22
MassaDeDadosCompressaoPorPage Insert 00:00:22
MassaDeDadosCompressaoPorColumnStore Insert 00:00:28

Tabela 1 – Comparativo de tempo decorrido para inserção de 1.000.000 de linhas de registro em cada tabela.

Nossa próxima característica esta relacionada ao espaço físico ocupado em disco para cada índice de acordo com a tecnologia de armazenamento utilizada e a forma de compressão aplicada.

Característica 4: Tabelas que possuem índice do tipo ColumnStore ocupam um espaço físico em disco infinitamente menor que tabelas que possuem índices RowStore, o que representa que o espaço ocupado em disco por índices que utilizam tecnologia ColumnStore podem apresentar uma economia de espaço em disco entre 70 e 90%, algo que devemos considerar em muito quando vamos trabalhar com um volume consideravelmente grande de dados.

A Tabela 2 apresentada abaixo ilustra esta diferença de espaço em disco ocupada por cada tabela:

Dados coletados pelo SP_SpaceUsed
Tabela Linhas Espaço Reservado  Dados Índices Não Utilizado
dbo.MassaDeDadosCompressaoPorRow 1000000 18592 KB 18448 KB 104 KB 40 KB
dbo.MassaDeDadosCompressaoPorPage 1000000 17312 KB 17192 KB 96 KB 24 KB
dbo.MassaDeDadosCompressaoPorColumnStore 1000000 28904 KB 28696 KB 144 KB 64 KB

Tabela 2 – Dados coletados pela System Stored Procedure SP_SpaceUsed.

Observação: Se analisarmos os dados da Tabela 2 podemos notar que a compressão por página de dados para este cenário de 1.000.000 de linhas de registro foi um pouco superior em relação a economia de espaço em disco se comparada com a compressão por linha de dados, essa é uma característica interessante quando trabalhamos com compressão de dados fazendo uma análise de minimal space entre Row Compression e Page Compression.

Uma outra possibilidade para notar esta diferença considerável de espaço alocado, reservado e ocupado fisicamente em disco poderia ser obtido através de uma nova execução do Bloco de Código 2 apresentado anteriormente, para tal a Tabela 3 apresenta o resultado desta nova execução:

Dados coletados através do Bloco de Código 2
Tabela Linhas Espaço Ocupado em MBs Espaço Não Utilizado em MBs Total MBs
MassaDeDadosCompressaoPorRow 1000000 18.12 0.04 18.16
MassaDeDadosCompressaoPorPage 1000000 16.88 0.02 16.91
MassaDeDadosCompressaoPorColumnStore 1000000 0.02 0.05 0.07

Tabela 3 – Dados coletados através da execução do Bloco de Código 2.

Observação: No que diz respeito a minimal space quando trabalhamos com o ColumnStore Index em comparação com Row Compression ou Page Compression a diferença é extremamente assustadora, aonde o ColumnStore Index aplica uma técnica de armazenamento de dados muito interessante que possibilita esta ganho de espaço e consequentemente ganho de performance. Esta técnica é conhecida como Row Group.

Estamos quase no final, vamos agora identificar mais uma importante característica presente em um ColumnStore Index, relacionada a performance algo que este tipo de tecnologia apresenta como principal vantagem e benefícios em relação a índices RowStore ainda mais quando trabalhamos com grandes volumes de dados em ambientes relacionais.

Nossa análise será iniciada através da execução da Bloco de Código 4 declarado abaixo, observe que estaremos habilitando os contadores de estatísticas de processamento relacionados a Tempo e Input/Output denominados:

Através do uso destes contadores o Management Studio ira nos retornar na guia de mensagens um conjunto de dados relacionados ao tempo de processamento, quantidade de leituras lógicas e físicas demandadas por cada transação que estaremos processando, com base, nestes e outros dados poderemos identificar quando será o diferença do ColumnStore Index em comparação com os outros índices existentes em nossas tabelas.

Outro elemento importante que estaremos utilizando, consiste na quantidade de linhas pesquisadas em cada tabela, você poderá notar que para cada Select declarado no Bloco de Código 4 estaremos filtrando e limitando nosso result set na faixa de valores existentes entre as linhas de registro 525.000 e 789.000.

Vamos então executar o Bloco de Código 4, recomendo que faça a execução de cada select de forma separada, anote os dados retornados pelos contadores Time e IO:

— Bloco de Código 4 —

Set Statistics Time On
Set Statistics IO On
Go

Select * from dbo.MassaDeDadosCompressaoPorRow
Where Record Between 525000 And 789000
Go

Select * from dbo.MassaDeDadosCompressaoPorPage
Where Record Between 525000 And 789000
Go

Select * from dbo.MassaDeDadosCompressaoPorColumnStore
Where Record Between 525000 And 789000
Go

Set Statistics Time Off
Set Statistics IO Off
Go

Ufa, estamos quase lá, nosso Bloco de Código 4 foi apresentado, tenho a certeza que você executou cada Select de forma individual, anotando os respectivos valores de tempo de processamento e input/output retornados pelo SQL Server, muito bem, agora vamos analisar a Tabela 4 apresentada abaixo e identificar o comportamento apresentado pelo ColumnStore Index.

Tabela Operação Tempo Decorrido CPU Time Elapsed Time Scan Count Logical Reads Physical Reads
MassaDeDadosCompressaoPorRow Select 00:00:02 110 ms 769 ms 1 2316 0
MassaDeDadosCompressaoPorPage Select 00:00:02 203 ms 830 ms 1 2158 0
MassaDeDadosCompressaoPorColumnStore Select 00:00:01 125 ms 911 ms 2 3599 0

Tabela 4 – Conjunto de dados coletados e apresentados pelos contadores TIME e IO durante a execução dos selects declarados no Bloco de Código 4.

Característica 5: Índice do tipo ColumnStore sempre serão mais rápidos que índice RowStore mesmo apresentando alguns dados que valores superiores, dentre eles: Scan Count e Logical Reads.

Analisando os dados apresentados anteriormente na Tabela 4, fica mais fácil observar alguns comportamentos presentes em índices ColumnStore, nos quais a quantidade de leituras lógicas pode estar mais presente neste tipo de índice devido justamente a sua forma de armazenamento de dados, além disso, o número de contagem de varreduras conhecidos como Scan Count, outro fator que também esta relacionado ao armazenamento do dados no índice na forma de coluna ao invés de linha como no RowStore.

Mas o elemento mais importante esta vinculado ao tempo decorrido na busca dos dados solicitados, tanto a tabela MassaDeDadosCompressaoPorRow como também a MassaDeDadosCompressaoPorPage demandaram 00:00:02 segundos para identificar, filtrar e apresentar 264.001 mil linhas de registro.

Nesta mesma comparação, os dados pesquisados na tabela MassaDeDadosCompressaoPorRow foram retornados em tela em 00:00:01 segundo, ou seja, uma ligeira diferença no tempo de processamento, o que pode parecer muito insignificante para o atual volume de dados que estamos trabalhando, mas imagine este ganho de processamento quando estivermos manipulando bilhões, trilhões ou quatrilhões de linhas de dados, mesmo que o elapsed time do processamento tenha sido um pouco maior que os demais. 

Desta forma, os dados apresentados na Tabela 4 em conjunto com os demais dados presentes em cada análise realizada neste post, nos traz a seguinte certeza, que índices ColumnStore podem e devem ser analisados, o que nos permite também dizer que o uso deste tipo de tecnologia poderá ser de grande avaliar e benefícios no que se relaciona a armazenamento e processamento de dados.

Com isso chegamos ao final deste post, olha foi surpreende e desafiador….


Referências

https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/row-compression-implementation

https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/page-compression-implementation

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property

https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-spaceused-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-time-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-io-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-tables-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-partitions-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-allocation-units-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/schemas-catalog-views-sys-schemas

Links

Caso você ainda não tenha acessado os posts anteriores desta sessão, fique tranquilo é fácil e rápido, basta selecionar um dos links apresentados a seguir:

https://pedrogalvaojunior.wordpress.com/2017/06/28/15-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2017/04/30/14-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2017/03/25/13-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2017/01/23/12-para-que-serve/

Conclusão

Identificar a melhor forma de armazenar nossos dados em um Sistema Gerenciador de Banco de Dados não é um tarefa das mais fáceis e comum de se realizada, escolher qual tecnologia de armazenamento visando economia de espaço e performance muito menos.

A partir da versão 2012 do Microsoft SQL Server esta tarefa começou a ficar um pouco mais interessante e amigável com a adoção da tecnologia de armazenamento de dados existentes em um índice no formato de coluna ao invés de linha, tecnologia conhecida e denominada como ColumnStore Index.

O uso do ColumnStore Index deve ser avaliado, sua principal característica relaciona-se ao capacidade de processamento de um grande volume de dados com baixo custo de tempo, mesmo em ambientes relacionais, o que nos permite dizer que utilizar um índice ColumnStore poderá lhe trazer benefícios tanto no espaço ocupado em disco, bem como, no tempo de processamento na pesquisa de dados.

Agradecimentos

Antes de finalizar, são 00:46hrs da madrugada, já estamos no dia 01/10, ou seja, um novo mês esta começando neste domínio.

Chegou a hora do descanso, se preparar para um novo dia que daqui a pouco estará raiando, espero que você possa fazer o mesmo, aproveitar o seu dia ainda mais um domingo, tentar viver um pouco sem se preocupar com os problemas.

Mais uma vez obrigado por sua ilustre visita, sinto-me honrado com sua presença, espero que este conteúdo possa lhe ajudar e ser útil em suas atividades profissionais e acadêmicas.

Um forte abraço, até o próximo post da sessão Para que serve…..

Valeu.

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.

#05 – Para que serve


Hello everybody, Good Afternoon, Buenas Tardes e Buon pomeriggio!!!!

Domingão chuvoso aqui em São Roque, olha esta chuvinha já esta dando o que falar e causando alguns estranhos na região, poxa vida eu gosto de chuva mas acho que já deu na hora de parar(kkkkk), desse jeito vamos ficar todos embolorados(kkkkkk)….

Comentários e brincadeirasa parte, aproveitando este tempinho para ficar em casa se preparando para mais uma semana de muito trabalho no FIT e na FATEC, falando de FATEC já estamos chegando ao final do semestre.

Pois bem, estava pensando e decidi compartilhar com vocês na sessão Para que serve, mais dois comandos DBCCs (Database Command Console) não documentadas que recentimente acabei me deparando em uma necessidade na empresa e por incrível que pareça uma das QoD (Question of Day) no portal SQLServerCentral.com, alias eu sou suspeito a falar deste portal, sou fã de carterinha desta plataforma que emana conhecimento especializado e dedicado ao Microsoft SQL Server.

Seguindo em frente, estou me referindo a DBCC Freeze_IODBCC Thaw_IO! Por acaso você já teve a necessidade de utilizar ou já ouvi falar destes commandos?

Ficou surpreso em saber da existência destes recursos, eu também, mas tenho a certeza que você vai ficar mais supreso quando fazer uso deles, algo que realmente pode ajudar em muito a entender o comportamento do SQL Server durante o procedimento de leitura e escrita de uma transação.

Bom vamos conhecer um pouco mais sobre estas duas DBCCs não documentadas, tendo como base um tradicional ambiente de testes que normalmente utilizo para este tipo de necessidade, e como de costume, vamos vais uma vez resaltar que este tipo de procedimento deve ser realizado sempre em ambientes ou cenários de teste e desenvolvimento, pois são comandos que podem alterar o comportamento do seu servidor ou instância SQL Server, proporcionando algum tipo de perda de informação ou impactos nos seus dados. Mesmo assim acredito que vale a pena conhece-los.


Começa aqui o #05 – Para que serve, boa viagem meu amigo.

Basicamente estas duas DBCCs não documentadas estão presentes na relação de comandos não oficiais e não reconhecidos pela documentação Microsoft á algum tempo, nas pesquisas que realizei para obter mais exemplos e informações encontrei posts publicados em alguns blogs americas, russos e europeus datados de meados do ano de 2011, sendo assim deduzido que ambas as funcionalidades podem ser utilizadas de uma maneira mais “segura” nas versões 2008 R2, 2012 e 2014.

Para nosso ambiente de testes vou utilizar o Microsoft SQL Server 2014 Express SP1, também realizei testes no Microsoft SQL Server 2012 Express SP3, não observei nenhum tipo de mudança de comportamento ou processamento em relação a cada versão, tanto na sua forma de execução como também na maneira de processamento o resultado obtive foi o mesmo, sendo assim, fique a vontade para escolher a versão que você deseja utilizar. Então vamos conhecer um pouco sobre cada comando DBCC, começando pela DBCC Freeze_IO.

DBCC Freeze_IO

Como seu próprio nome segure, esta dbcc tem a função de congelar de forma temporária do processo de leitura e escrita realizada para um banco de dados, quando eu me referi a congelar estou fazendo referência a possibilidade de suspender todo processo de IO(Input – Output) para gravação de dados no banco de dados que você encontra conectado.

Quando o DBCC Freeze_IO é executado todas as atividades realizadas pelo SQL Server que envolvam processos de IO que estejam vinculadas a operação de escrita serão suspensas, não estou dizendo que estas atividades vão receber uma instrução de encerramento ou finalização, nada disso todas serão congeladas até que o comando DBCC Thaw_IO seja executado.
Uma vez que o IO é congelado, você pode tratar o banco de dados como um banco de dados somente leitura. Qualquer atividade de escrita não retornará nenhum tipo de informação, pois este banco de dados terá naquele exato momento o comportamento de um banco somente leitura. 
Você pode estar se pergunta, cara isso é muito perigo? Sim a resposta é sim, ainda mais se você fizer uso deste tipo de implementação em um ambiente que sofre uma carga enorma de leitura e escritas em pouco espaço de tempo.
DBCC Thaw_IO
Como destacado anteriormente este comando DBCC tem a finalidade de descongelar ou remover o status de suspensão nos processos de leitura e escrita marcados pela DBCC Freeze_IO. Quando executado o DBCC Thaw_IO envia para o Database Engine uma instrução que permite reverter o estado do banco de dados de somente leitura para leitura e escrita, desta forma, o SQL Server entende que a partir daquele momento o banco de dados envolvimento na execução da DBCC Freeze_IO não necessita mais ser impedido de receber novos dados, como também, esta livre para retornar qualquer tipo de informação solicitado pelo usuário e suas aplicações.
Ufa, parece ser bastante assustador fazer uso destes dois comandos, mas não é bem assim, e para mostrar que nem tudo o que parece realmente é tão perigoso, vamos fazer uma pequena prática para ilustrar o comportamento do SQL Server durante a execução de cada DBCC, para isso iremos utilizar uma pequena tabela chamada Estudantes, chegou então a hora de colocar a mão na massa ou melhor no código (kkkkk)….

Colocando a mão no código #05 – Para que serve – DBCC Freeze_IO e DBCC Thaw_IO

Para nosso ambiente de testes, vamos criar um novo banco de dados chamado DBFreeze e como destacado anteriormente será criada uma tabela chamada Estudantes que receberá alguns linhas de registros, conforme apresenta o bloco de Código 1 a seguir:
— Código 1 —

— Criando o Banco de Dados DBFreeze —

Create Database DBFreeze

Go

 

— Acessando o Banco de Dados —

Use DBFreeze

Go

 

— Criando a Tabela Estudantes —

Create Table Estudantes

(Id Int Primary Key Identity(1,1),

Nome Varchar(20) Not Null,

Classificacao TinyInt Not Null,

Curso Varchar(20) Not Null)

Go

 

— Inserindo os dados —

Insert Into Estudantes (Nome, Classificacao, Curso)

Values (‘Kim’, 99, ‘Inglês’),

(‘Thomas’, 95, ‘Inglês’),

(‘Jonh’, 92, ‘Inglês’),

(‘Mag’, 97, ‘Espanhol’),

(‘Sussy’, 90, ‘Espanhol’),

(‘Boby’, 91,‘Português’),

(‘Darth’, 89, ‘Português’)

Go 100000

 

Ótimo, nosso ambiente este criado, agora podemos fazer uma simples simulação do congelamento do banco de dados DBFreeze, para isso vamos utilizar o bloco de código 2, note que iremos executar um simples select com algumas funções de ranking na tabela Estudantes, conforme apresento abaixo:

 

— Código 2 —

Begin Transaction

Select Id, Nome, Classificacao, Curso,

ROW_NUMBER() Over (Order By Curso) As ‘Row Number’,

Rank() Over (Order By Curso) As ‘Rank’,

Dense_Rank() Over (Order By Curso) As ‘Dense Rank’,

NTile(4) Over (Order By Curso) As ‘NTile’

From Estudantes

Go

Observe que para garantir e evitar qualquer tipo de impacto em outras transações, realizei a abertura de uma nova transação para que o comando select e suas respectivas funções de ranking fossem executadas, neste momento esta transação esta sendo executado, vamos então forçar o congelamento do banco de dados, para isso abra uma nova query em seu management studio e execute o bloco de código 3:

— Código 3 —

DBCC Freeze_IO(DBFreeze)
Go

A partir deste momento nosso banco de dados DBFreeze acaba de receber a instrução de Freeze IO, onde o mesmo vai ser obrigado a interromper qualquer processo de atividades que envolvam leitura e escrita de dados, se você decidir voltar a sessão anterior, será possível observar que a mesma continua em execução mas os dados não são retornados em tela, para ter certeza de qual é o atual status desta sessão, podemos fazer uso da system stored procedure sp_lock para obter a lista de recursos e objetos atualmente em lock em nosso SQL Server.

Vamos voltar nosso banco de dados para o estado original, possibilitando que todas as leituras e escritam possam ser executadas normalmente retornando os dados solicitados, para isso utilizaremos o bloco código 4 apresentado a seguir. Para sua execução recomendo utilizar a mesma query criada para execução do código 3:

 

— Código 4 —

DBCC Thaw_IO(9)

Go

Após alguns segundos o Database Engine entende que o nosso banco de dados DBFreeze deve ter o estado de somente leitura alterado para leitura e gravação, sendo assim, o processamento da nossa primeira query será executado e encerrado normalmente, onde teremos todos os nossos dados apresentados conforme a estrutura do comando select executada no bloco de código 2, ilustrado anteriormente.

Observações:

  1. Gostaria de salientar que este tipo de funcionalidade não será muito aplicável ou utilizável em suas atividades diárias, mas poderá ser bastante útil quando você desejar de alguma forma simular uma possível “imagem ou snapshot” do seu banco de dados em um espaço de tempo ou situação; e
  2. O uso do DBCC Freeze_IO, DBCC Thaw_IO e qualquer outro comando DBCC não reconhecido ou documentado pela Microsoft e seus times de Engenheiros e desenvolvedores deve ser utilizado com muita cautela e sempre em ambientes de testes e desenvolvimento. Em algumas situações a adoção de estratégias de backup de bancos de dados ou até mesmo de um snapshot de seu ambiente virtualizado devem ser adotados como forma de garantir a recuperação dos seus recursos.

Então chegamos ao final de mais um post, estamos vivos e salvos e principalmente nosso banco de dados sobreviveu.


É isso ai galera, muito legal este post da sessão Para que serve!

Espero que você tenha gostado, que as informações compartilhadas aqui possam lhe ajudar a se tornar cada vez um profisisonal de banco de dados mais reconhecido e valorizado, algo que atualmente as empresas estão deixando muito de reconhecer o verdadeiro papel de um DBA que muitas vezes passa despercebido, mas é de grande e extremo valor para qual instituição, empresa ou corporação.

Caso deseje acessar os posts anteriores desta sessão, utilize os links postados abaixo:

Mais uma vez obrigado por sua visita, tenha uma ótima semana, nos encontramos em breve.

Até mais.

Short Scripts – Agosto 2015


Boa tarde, Comunidade!!!!

Tudo em paz? Mas que loucura este tempo aqui em São Roque e Sorocaba, hoje ás 6:30hrs da manhã estava 8º graus, agora ás 13hrs sensação térmica de 20º graus e subindo……

Nos últimos meses acabei deixando de publicar alguns posts relacionadas a minha sessão Short Scripts devido a correria da vida profissional e acadêmica, mas hoje vou pagar este dívida, compartilhando com vocês alguns dos meus novos short scripts.

Muitos tem me perguntado o porque acabou guardando tantos scripts ou códigos de exemplo, a resposta é bem simples e direta, sempre existirá alguém ou algo no mundo que poderá necessitar deste recurso e isso é que me mantem nesta jornada em cultivar minha biblioteca de scripts a cada dia mais atualização e completa(tarefa muito, mas muito complicada de se realizar).

Os Short Scripts apresentados hoje, estão relacionandos com os seguintes assuntos:

  • Block Process;
  • CTE;
  • Cláusula Output;
  • Comando Select;
  • Comando SET;
  • Concatenação de Valores;
  • Extended Events;
  • Índices;
  • Junção de Tabelas
  • Fatorial;
  • Funções; e
  • Tipos de Dados.

É isso galera, a seguir você vai poder encontrar os blocos de código que representam os Short Scripts, fique a vontade para copiar, compartilhar, sugerir melhorias e fazer suas críticas também.

 

— Short Script 1 –  Uitlizando cláusula Output em Delete com Inner Join + Select —

DECLARE @t TABLE

(nDex INT IDENTITY(1,1),

valu VARCHAR(9),

keey UNIQUEIDENTIFIER)

INSERT @t

VALUES (‘a’,NEWID()) , (‘b’,NEWID()),

(‘c’,NEWID()) , (‘d’,NEWID()),

(‘e’,NEWID()) , (‘f’,NEWID()),

(‘g’,NEWID()) , (‘h’,NEWID()),

(‘i’,NEWID()) , (‘j’,NEWID()),

(‘k’,NEWID())

DELETE t

OUTPUT DELETED.*

FROM @t AS t INNER JOIN (SELECT TOP 9 nDex FROM @t ORDER BY NEWID()) AS b

ON b.ndex = t.nDex

GO

 

 

— Short Script 2 – Uitilizando – CTE para separar palavras —

DECLARE @s VARCHAR(8000), @d VARCHAR(10)

SET @s = ‘separar por espaço em branco’

SET @d = ‘ ‘

;WITH split(i,j) AS

(

SELECT i = 1, j = CHARINDEX(@d, @s + @d)

UNION ALL

SELECT i = j + 1, j = CHARINDEX(@d, @s + @d, j + 1) FROM split

WHERE CHARINDEX(@d, @s + @d, j + 1) <> 0

)

SELECT SUBSTRING(@s,i,ji)

FROM split

 

— Short Script 3 – Simulando – Cenários de utilização de índices —

USE TempDB;

— Criando a Tabela Funcionário —

CREATE TABLE Funcionario

(ID int primary key,

NomeFunc varchar(200),

DataNasc date,

DataADM date);

Go

— Consulta 1 – Fazendo uso do Operador Clustered Index Scan —

SELECT * from Funcionario

Where DataADM between Convert(date, ‘1/1/2012’, 103) and Convert(date, ’31/1/2012′, 103)

Go

— Criando um novo índice chamado Ind_NC_Funcionario_DataADM —

CREATE Nonclustered Index Ind_NC_Funcionario_DataADM On Funcionario (DataADM);

Go

— Consulta 2 – Fazendo uso do Operador Clustered Index Scan —

SELECT * from Funcionario

Where DataADM between Convert(date, ‘1/1/2012’, 103) and Convert(date, ’31/1/2012′, 103)

Go

— Consulta 3 – Forçando o uso do índice IND_NC_Funcionario_DataADM, gerando Index Seek, Key Lookup e Nested Loops —

SELECT * from Funcionario with (index=Ind_NC_Funcionario_DataADM)

Where DataADM between Convert(date, ‘1/1/2012’, 103) and Convert(date, ’31/1/2012′, 103)

Go

— Consulta 4 – Utilizando realmente o índice IND_NC_Funcionario_DataADM —

SELECT ID, DataADM

From Funcionario

Where DataADM between Convert(date, ‘1/1/2012’, 103) and Convert(date, ’31/1/2012′, 103)

Go

 

— Short Script 4 – Função – Concatenar valores em ordem decrescente —

CREATE FUNCTION dbo.ConcatenaValores (@C1 int, @C2 int, @C3 int, @C4 int, @C5 int)

returns int as

begin

declare @Concatenado varchar(5);

set @Concatenado= space(0);

SELECT @Concatenado+= Cast(N as char(1))

from (values (@C1), (@C2), (@C3), (@C4), (@C5)) as Numeros(N)

order by N desc;

return Cast(@Concatenado as int);

end;

go

Select Valor= dbo.ConcatenaValores(1, 2, 3, 4, 5);

 

— Short Script 5 – Criando tipo de dados NotNull mas Null sobrepondo Not Null —

CREATE TYPE NotNullType FROM VARCHAR(10) NOT NULL;

GO

— table create

CREATE TABLE Test(TestId INT, NullTest NotNullType NULL);

GO

— insert

INSERT INTO Test(TestId) VALUES(1);

SELECT NullTest FROM Test;

 

— Short Script 6 – Comparando comportamento entre os comandos SET e Select —

DECLARE @a INT= 0;

DECLARE @b INT= 0;

 

CREATE TABLE #tmpPrice ( Value INT );

SET @a = ( SELECT Value FROM #tmpPrice);

SELECT @b = Value FROM #tmpPrice;

SELECT @a AS, @b AS b

 

— Short Script 7 – Função para Calcular Fatorial com CTE encapsulada —

CREATE FUNCTION fatorial

(

@n INT

)

RETURNS INT

AS

BEGIN

DECLARE @val INT;

WITH fat(f, n) AS

(

SELECT CAST (1 as bigint) as f, 0 as n

UNION ALL

SELECT CAST (1 as bigint) as f, 1 as n

UNION ALL

SELECT f * (n + 1), n +1

FROM fat

WHERE n < 20 AND n <> 0

)

SELECT @val = f

FROM fat

WHERE n = @n

RETURN @val

END

GO

— Testando a função

SELECT dbo.fatorial(3);

SELECT dbo.fatorial(4);

SELECT dbo.fatorial(7);

 

— Short Script 8 –  CTE – Calculando fatorial —

WITH fat(f, n) AS

(

SELECT CAST (1 as bigint) as f, 0 as n — fat de 0 é 1

UNION ALL

SELECT CAST (1 as bigint) as f, 1 as n — fat de 1 é 1

UNION ALL

SELECT f * (n + 1), n +1

FROM fat

WHERE n < 20 AND n <> 0

— 20 é o limite neste caso, pois o fatorial de 21

— não cabe em um tipo bigint. O <> 0 é para cortar a recursão

— do primeiro âncora, senão repetiria tudo, faça o teste.

)

SELECT f

FROM fat

WHERE n = 12

 

— Short Script 9 – Capturar Blocked Process com Extended Events —

CREATE EVENT SESSION [Blocked] ON SERVER

ADD EVENT sqlserver.blocked_process_report

ADD TARGET package0.event_file

(SET filename=N’C:\xel\blocked.xel’)

GO

ALTER EVENT SESSION [Blocked]

ON SERVER

STATE = start;

GO

 

–Agora precisamos ler os arquivos .XEL gerados pela sessão e extrair os dados do XML para identificarmos as causas dos blocked process:

select theNodes.event_data.value(‘(//blocked-process/process)[1]/@spid’,‘int’) as blocking_process,

theNodes.event_data.value(‘(//blocked-process/process/inputbuf)[1]’,‘varchar(max)’) as blocking_text,

theNodes.event_data.value(‘(//blocked-process/process)[1]/@clientapp’,‘varchar(100)’) as blocking_app,

theNodes.event_data.value(‘(//blocked-process/process)[1]/@loginname’,‘varchar(50)’) as blocking_login,

theNodes.event_data.value(‘(//blocked-process/process)[1]/@isolationlevel’,‘varchar(50)’) as blocking_isolation,

theNodes.event_data.value(‘(//blocked-process/process)[1]/@hostname’,‘varchar(50)’) as blocking_host,

theNodes.event_data.value(‘(//blocking-process/process)[1]/@spid’,‘int’) as blocked_process,

theNodes.event_data.value(‘(//blocking-process/process/inputbuf)[1]’,‘varchar(max)’) as blocked_text,

theNodes.event_data.value(‘(//blocking-process/process)[1]/@clientapp’,‘varchar(100)’) as blocked_app,

theNodes.event_data.value(‘(//blocking-process/process)[1]/@loginname’,‘varchar(50)’) as blocked_login,

theNodes.event_data.value(‘(//blocked-process/process)[1]/@isolationlevel’,‘varchar(50)’) as blocked_isolation,

theNodes.event_data.value(‘(//blocking-process/process)[1]/@hostname’,‘varchar(50)’) as blocked_host

from

(select convert(xml,event_data) event_data

from

sys.fn_xe_file_target_read_file(‘c:\xel\blocked*.xel’, NULL, NULL, NULL)) theData

cross apply theData.event_data.nodes(‘//event’) theNodes(event_data)

 

Chegamos ao fim de mais um Short Scripts, espero que você tenha gostado destes códigos!!!

Nos encontramos em breve.

Até a próxima…..

Short Script – Junho – 2015


Bom dia, Comunidade! Hello Everybody!!!

Estamos começando mais um mês e como diz alguns dos cronistas esportivos durante suas transmissões “O tempo passa….”, realmente passa e temos que procurar aproveitar.

Como já destaquei anteriormente procure diariamente estudar e conhecer cada vez mais sobre o mundo de Banco de Dados, Carreira de DBA, SQL Server e outros elementos que se relacionam com a minha função de Administrador de Banco de Dados, que exerço a muito tempo. Na verdade faço isso por que gosto e principalmente para me manter atualizado neste concorrido mercado de trabalho, seja ele profissional ou acadêmico, neste sentido em muitos momentos algumas informações, dicas, truques e até mesmo códigos de exemplo acabam fazendo parte da minha “Biblioteca de Scripts” que venho nos últimos anos compartilhando com todos vocês.

Seguindo esta escrita e mantendo a tradição, como de costume todo inicio ou final de mês estou compartilhando com vocês novos Shorts Scripts ou Materiais de Apoio, e hoje não será diferente.

Neste Short Script, você vai encontrar pequenos blocos de código ou exemplos relacionados aos seguintes itens:

  • Criação de Jobs e Steps por linha de comando utilizando a opção @subsytem;
  • Criação de função para manipulação de String;
  • Criação de uma User Function Scalar para trabalhar com a Tabela Price;
  • Simulando um Select “*” que não retorna todas as colunas;
  • Trabalhando com XML em conjunto com SP_XML_PrepareDocument;
  • Utilizando a função Count_Big em conjunto com Funções de Agregação;
  • Utilizando o comando Intersect com valores Char e Int; e
  • Utilizando Sparse Columns e Sparse Columns Sets.

Fique a vontade para copiar e compartilhar os Short Scripts apresentados abaixo:

 

— Short Script 1 – Criando Job para substituir o uso do XP_CMDShell –

Create PROCEDURE dbo.usp_ExecCmdShellProcess

AS

BEGIN

DECLARE @job NVARCHAR(100), @BulkCMD Varchar(1000)

SET @job = ‘xp_cmdshell replacement’+Convert(Varchar(10),GetDate()) ;

 

SET @BulkCMD = ‘BULK INSERT ListFiles FROM ‘+”’C:\TEMP\LISTFILES.TXT” ‘+

‘WITH (FIELDTERMINATOR = ”;”, ROWTERMINATOR = ”’+ CHAR(10) +”’, CODEPAGE = ”ACP”)’

 

EXEC msdb..sp_add_job @job_name = @job,

@description = ‘Automated job to execute command shell script’,

@owner_login_name = ‘pedro’, @delete_level = 1 ;

 

EXEC msdb..sp_add_jobstep @job_name = @job, @step_id = 1,

@step_name = ‘Command Shell Execution’, @subsystem = ‘CMDEXEC’,

@command =’dir /b > C:\TEMP\LISTFILES.TXT’, @on_success_action = 1;

 

EXEC msdb..sp_add_jobserver @job_name = @job ;

 

EXEC msdb..sp_start_job @job_name = @job ;

 

Exec(@Bulkcmd)

 

END ;

GO

 

— Short Script 2 – Criando User Function para cortar Strings em partes —

CREATE FUNCTION fncCortaString(@txt VARCHAR(50), @txt_ini VARCHAR(50), @txt_fim VARCHAR(50))

RETURNS VARCHAR(50)

BEGIN

RETURN

(

SUBSTRING(@txt, CHARINDEX(@txt_ini, @txt) + LEN(@txt_ini), (CHARINDEX(@txt_fim, @txt) – CHARINDEX(@txt_ini, @txt)) – LEN(@txt_ini))

)

END

 

Select dbo.fncCortaString(‘SQL Server’, ‘SQL ‘, ‘ver’)

 

— Short Script 3 – Utilizando a função Count_Big para criar índice único em view com função de agregação —

Create Table MySampleTable

(Id1 Int,

Id2 Int,

SomeData Varchar(100))

 

Create View SampleView

With SchemaBinding

As

Select COUNT(*) TableCount,

Id2

From dbo.MySampleTable

Group By Id2

 

— Erro ao criar —

Create Unique Clustered Index [IX_ViewSample]

On [dbo].[SampleView]

(Id2 Asc)

Go

 

— Alterando o tipo de contagem de dados —

Create View SampleView

With SchemaBinding

As

Select COUNT_BIG(*) TableCount,

Id2

From dbo.MySampleTable

Group By Id2

 

— Criando o Índice —

Create Unique Clustered Index [IX_ViewSample]

On [dbo].[SampleView]

(Id2 Asc)

Go

 

— Short Script 4 – Utilizando o comando Intersect com valores Char e Int —

Create Table #A (x Char(2));

 

Insert Into #A Values (‘1’);

Insert Into #A Values (‘6’);

Insert Into #A Values (‘2’);

Insert Into #A Values (‘3’);

Insert Into #A Values (‘5’);

Insert Into #A Values (‘5’);

Insert Into #A Values (‘6’);

Insert Into #A Values (‘9’);

Go

 

Create Table #B (M BigInt);

 

Insert Into #B Values(5);

Insert Into #B Values(5);

Insert Into #B Values(6);

Insert Into #B Values(7);

Insert Into #B Values(7);

Go

 

SELECT x AS ‘Select #1’ FROM #A

INTERSECT SELECT M FROM #B;

 

— (Select #2)

SELECT DISTINCT(x) AS ‘Select #2’

FROM #A

LEFT OUTER JOIN #B

ON #A.x = #B.M

 

— (Select #3)

SELECT DISTINCT(x) AS ‘Select #3’

FROM #A

LEFT OUTER JOIN #B

ON #A.x = #B.M

 

— (Select #4)

SELECT DISTINCT(x) AS ‘Select #4’

FROM #A

INNER JOIN #B

ON #A.x = #B.M

 

— (Select #5)

SELECT x AS ‘Select #5’

FROM #A

INNER JOIN #B

ON #A.x = #B.M

 

— Short Script 5 – Criando User Function com base na Tabela Price —

CREATE FUNCTION CalcPrest (@C money, @i numeric(9,5), @n int)

returns table as return

SELECT Cast(@C *

(Power((1 + (@i / 100)), @n) * (@i / 100)) /

(Power((1 + (@i / 100)), @n) -1)

as money) as Prestação;

go

 

— Short Script 6 – Select “*” não retornando todas as colunas de uma tabela —

— Exemplo 1 —

DROP TABLE #temp;

 

CREATE TABLE #temp (

RowID INT IDENTITY PRIMARY KEY CLUSTERED,

Name VARCHAR(25),

Column1 INT SPARSE,

Column2 INT SPARSE,

Column3 INT SPARSE,

Column4 INT SPARSE,

ColumnSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);

 

INSERT INTO #temp (Name, Column1) VALUES (‘Row1’, 1);

INSERT INTO #temp (Name, Column1, Column2) VALUES (‘Row2’, 2, 2);

INSERT INTO #temp (Name, Column1, Column2, Column3) VALUES (‘Row3’, 3, 3, 3);

INSERT INTO #temp (Name, Column1, Column2, Column3, Column4) VALUES (‘Row4’, 4, 4, 4, 4);

INSERT INTO #temp (Name, Column1, Column3) VALUES (‘Row5’, 5, 5);

INSERT INTO #temp (Name, Column3, Column4) VALUES (‘Row6’, 6, 6);

 

SELECT * FROM #temp;

— Exemplo 2 —

DROP TABLE #temp;

 

CREATE TABLE #temp (

RowID INT IDENTITY PRIMARY KEY CLUSTERED,

Name VARCHAR(25),

Column1 INT SPARSE,

Column2 INT SPARSE,

Column3 INT SPARSE,

Column4 INT SPARSE);

 

INSERT INTO #temp (Name, Column1) VALUES (‘Row1’, 1);

INSERT INTO #temp (Name, Column1, Column2) VALUES (‘Row2’, 2, 2);

INSERT INTO #temp (Name, Column1, Column2, Column3) VALUES (‘Row3’, 3, 3, 3);

INSERT INTO #temp (Name, Column1, Column2, Column3, Column4) VALUES (‘Row4’, 4, 4, 4, 4);

INSERT INTO #temp (Name, Column1, Column3) VALUES (‘Row5’, 5, 5);

INSERT INTO #temp (Name, Column3, Column4) VALUES (‘Row6’, 6, 6);

 

SELECT * FROM #temp;

 

— Short Script 7 – Trabalhando com XML em conjunto com SP_XML_PrepareDocument –

Declare @Table As Table (SINo Int, Name Varchar(500), Salary Money)

Declare @XMLIntLog As XML

Declare @IDoc Int

 

Set @XMLIntLog = Null

Insert Into @Table Values(1,’SES’,10000)

Insert Into @Table Values(2,’SRS’,40000)

Insert Into @Table Values(3,’SS’,50000)

Set @XMLIntLog = (Select SINo, Name, Salary from @Table Tab For XML Auto, Root(‘Root’), elements)

 

Exec sp_xml_preparedocument @IDoc Output, @XMLIntLog

 

Select SINo, Name, Salary from OpenXML(@iDoc, ‘/Root/Tab’,7)

With(SINo Int, Name Varchar(500), Salary Money)

 

Exec sp_xml_removedocument @IDoc

 

— Short Script 8 – Utilizando Sparse Columns e Sparse Columns Sets —

CREATE TABLE dbo.Table1 (

RowID INTEGER IDENTITY PRIMARY KEY CLUSTERED,

DateTimeStamp DATETIME DEFAULT GETDATE(),

Col1 INTEGER SPARSE,

Col2 INTEGER SPARSE,

Col3 INTEGER SPARSE,

TblColumnSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);

 

INSERT INTO dbo.Table1 (Col1) VALUES (1), (2);

INSERT INTO dbo.Table1 (Col2) VALUES (3), (4);

INSERT INTO dbo.Table1 (Col3) VALUES (5), (6);

INSERT INTO dbo.Table1 (TblColumnSet) VALUES (‘<Col1>1</Col1><Col2>2</Col2><Col3>3</Col3>’);

 

SELECT RowID, DateTimeStamp, Col1, Col2, Col3, TblColumnSet

FROM dbo.Table1;

 

Agradeço a sua visita, espero que este material posso te ajudar, qualquer dúvida, sugestão ou crítica post o seu comentário.

Até mais.

Short Script – Maio – 2015


Bom dia, pessoal, mais uma semana começando.

Mantendo a tradição e escrita, estou começando mais um mês no meu blog, compartilhamento com todos vocês a minha relação de Scripts que atualmente esta crescendo muito graças ao SQLServerCentral.com.

Venho diariamente estudando e respondendo as questões diárias que são publicadas no SQLServerCentral.com, pelos principais nomes e especialistas em SQL Server do Mundo, estou seguindo nesta caminhada desde o final do mês de fevereiro. Posso dizer que esta sendo uma oportunidade fantástica de relembrar, conhecer, aprender e também descobrir muitos segredos, novidades e principalmente informações sobre o Microsoft SQL Server em todas as suas versões.

Aproveito a oportunidade para indicar e recomendar sua visita ao SQLServerCentral.com, principalmente a sessão de Question of the Day: http://www.sqlservercentral.com/Questions

Pois bem, na relação de Short Scripts que estou disponibilizando hoje, boa parte destes códigos são resultado das questões que eu respondi no SQLServerCentral.com e identifiquei como questões importantes ou relevantes para meus estudos, bem como, para repassar para meus alunos.

Nesta relação, você terá a oportunidade de conhecer e aprender um pouco mais sobre:

·         Análise e Lógica Condicional com operadores Not, And e Or;
·         Colunas Computadas;
·         Complexidade Lógica;
·         CTE Recursiva;
·         Declaração de variáveis do Tipo XML em conjunto com Select;
·         Função Coalesce em conjunto com Left Join e valores nulos; e
·         Funções Char(), NChar(), Unicode() e ASCII();
·         Identificando ausência de índices em tabelas;
·         Índices Clusterizados e Índices Chaves;
·         Índices com colunas duplicadas;
·         Operador Intersect;
·         Taxas de Fragmentação de Tabelas antes e depois do Rebuild;
·         Transações aninhadas;
·         Utilitário BCP para criação de arquivos XML.

 

Legal, a seguir você poderá encontrar os Short Scripts separados por seus respectivos conteúdos:

  • Análise, Precedência e Lógica Condicional com operadores Not, And e Or    VALUES ( 1, ‘Baseball Hat’ ),           ( 3, ‘Snowboard’ ),           ( 5, ‘Shows’ )SELECT ID   WHERE Product = ‘Bicycle’ OR Product = ‘Snowboard’ AND ID = 4

 

  1.    FROM @Logic
  2.            ( 4, ‘Goggles’ ),
  3.          ( 2, ‘Bicycle’ ),
  4. INSERT INTO @Logic
  5. DECLARE @Logic TABLE ( ID INT, Product VARCHAR(50) )

 

  • Colunas Computadas   (   , col1Computed AS LEFT(col1, 4)   , col2Computed AS LEFT(col2, 4)   , col3Computed AS LEFT(col3, 4)   , col4Computed AS LEFT(col4, 4) 

 

  1.    );
  2.    , col4 CHAR(8) NOT NULL
  3.    , col3 CHAR(8) NULL
  4.    , col2 VARCHAR(8) NOT NULL
  5.      col1 VARCHAR(8) NULL
  6. CREATE TABLE t
  7. Complexidade Lógica

DECLARE @AA VARCHAR(12) = ‘AAA’

,@XXX           VARCHAR(12) = ‘AAA’

,@YYY           VARCHAR(12) = ‘BBB’

,@ZZZ           VARCHAR(12) = ‘CCC’

,@AAXXX         VARCHAR(12) = ‘XXAAA’

,@AAYYY         VARCHAR(12) = ‘XXBBB’

,@AAZZZ         VARCHAR(12) = ‘XXCCC’

,@XXXCount       INT = 1

,@YYYCount       INT = 1

,@ZZZCount       INT = 1

,@AAXXXCount     INT = 1

,@AAYYYCount     INT = 1

,@AAZZZCount     INT = 1

,@Result       INT;

 

— Much to your chagrin, you encounter the following logic in a SQL SP.

— You may assume that each local variable is DECLAREd and @AA,

— @XXX, @YYY, @ZZZ, @AAXXX, @AAYYY, @AAZZZ each have a value assigned,

— with only @AA required to be NOT NULL. Each @xxxCount variable

— is also assigned an integer value.

SET @Result = 0;

 

IF @AA = @XXX

BEGIN

IF @XXXCount > @AAXXXCount SET @Result = 2

END

ELSE IF @AA = @YYY

BEGIN

IF @YYYCount > @AAYYYCount SET @Result = 2

END

ELSE IF @AA = @ZZZ

BEGIN

IF @ZZZCount > @AAZZZCount SET @Result = 2

END

ELSE IF @AA = @AAXXX

BEGIN

IF @XXXCount = @AAXXXCount SET @Result = 3

END

ELSE IF @AA = @AAYYY

BEGIN

IF @YYYCount = @AAYYYCount SET @Result = 3

END

ELSE IF @AA = @AAZZZ

BEGIN

IF @ZZZCount = @AAZZZCount SET @Result = 3

END

 

— Identify the equivalent logic from the options shown:

— Option 1:

SET @Result = 0;

 

IF @AA = @XXX AND @XXXCount > @AAXXXCount SET @Result = 2

ELSE IF @AA = @YYY AND @YYYCount > @AAYYYCount SET @Result = 2

ELSE IF @AA = @ZZZ AND @ZZZCount > @AAZZZCount SET @Result = 2

ELSE IF @AA = @AAXXX AND @XXXCount = @AAXXXCount SET @Result = 3

ELSE IF @AA = @AAYYY AND @YYYCount = @AAYYYCount SET @Result = 3

ELSE IF @AA = @AAZZZ AND @ZZZCount = @AAZZZCount SET @Result = 3

 

SELECT [Option 1:]=@Result

SELECT @Result = NULL;

 

 

— Option 2:

IF @AA = @XXX AND @XXXCount > @AAXXXCount SET @Result = 2

ELSE IF @AA = @YYY AND @YYYCount > @AAYYYCount SET @Result = 2

ELSE IF @AA = @ZZZ AND @ZZZCount > @AAZZZCount SET @Result = 2

ELSE IF @AA = @AAXXX AND @XXXCount = @AAXXXCount SET @Result = 3

ELSE IF @AA = @AAYYY AND @YYYCount = @AAYYYCount SET @Result = 3

ELSE IF @AA = @AAZZZ AND @ZZZCount = @AAZZZCount SET @Result = 3

ELSE SET @Result = 0;

 

SELECT [Option 2:]=@Result

SELECT @Result = NULL;

 

— Option 3:

SELECT @Result = CASE @AA

WHEN @XXX THEN CASE WHEN @XXXCount > @AAXXXCount THEN 2 ELSE 0 END

WHEN @YYY THEN CASE WHEN @YYYCount > @AAYYYCount THEN 2 ELSE 0 END

WHEN @ZZZ THEN CASE WHEN @ZZZCount > @AAZZZCount THEN 2 ELSE 0 END

WHEN @AAXXX THEN CASE WHEN @XXXCount = @AAXXXCount THEN 3 ELSE 0 END

WHEN @AAYYY THEN CASE WHEN @YYYCount = @AAYYYCount THEN 3 ELSE 0 END

WHEN @AAZZZ THEN CASE WHEN @ZZZCount = @AAZZZCount THEN 3 ELSE 0 END

END;

 

SELECT [Option 3:]=@Result          — Not identical

SELECT @Result = NULL;

 

— Option 4:

SELECT @Result = CASE @AA

WHEN @XXX THEN CASE WHEN @XXXCount > @AAXXXCount THEN 2 ELSE 0 END

WHEN @YYY THEN CASE WHEN @YYYCount > @AAYYYCount THEN 2 ELSE 0 END

WHEN @ZZZ THEN CASE WHEN @ZZZCount > @AAZZZCount THEN 2 ELSE 0 END

WHEN @AAXXX THEN CASE WHEN @XXXCount = @AAXXXCount THEN 3 ELSE 0 END

WHEN @AAYYY THEN CASE WHEN @YYYCount = @AAYYYCount THEN 3 ELSE 0 END

WHEN @AAZZZ THEN CASE WHEN @ZZZCount = @AAZZZCount THEN 3 ELSE 0 END

ELSE 0 END;

 

SELECT [Option 4:]=@Result

SELECT @Result = NULL;

 

— Option 5:

SELECT @Result = CASE @AA

WHEN @XXX THEN CASE WHEN @XXXCount > @AAXXXCount THEN 2 END

WHEN @YYY THEN CASE WHEN @YYYCount > @AAYYYCount THEN 2 END

WHEN @ZZZ THEN CASE WHEN @ZZZCount > @AAZZZCount THEN 2 END

WHEN @AAXXX THEN CASE WHEN @XXXCount = @AAXXXCount THEN 3 END

WHEN @AAYYY THEN CASE WHEN @YYYCount = @AAYYYCount THEN 3 END

WHEN @AAZZZ THEN CASE WHEN @ZZZCount = @AAZZZCount THEN 3 END

ELSE 0 END;

 

SELECT [Option 5:]=@Result   — Not identical

 

  • CTE Recursiva set @DataFim = ‘20150430’;with CTE_Datas as   select @DataIni as Data, @DataFim as DataFim   union all   select DATEADD(DAY, 1, Data), DataFim   where Data < DataFim 

 

  1. select Data from CTE_Datas
  2. )
  3.    from CTE_Datas
  4. (
  5. set @DataIni = ‘20150401’;
  6. declare @DataIni date, DataFim date;

 

  • Declaração de variáveis do Tipo XML em conjunto com Select

 

DECLARE @Xml XML = N’

<products>

<product id=”1″ name=”Adjustable Race” />

<product id=”879″ name=”All-Purpose Bike Stand” />

<product id=”712″ name=”AWC Logo Cap” />

<product id=”19910″ name=”Cozonac” />

</products>’;

 

SELECT

xt.xc.value(‘@id’, ‘INT’) AS ProductID,

xt.xc.value(‘@name’,’NVARCHAR(50)’) AS Name

FROM

@Xml.nodes(‘/products/product’) AS xt(xc);

 

  • Função Coalesce em conjunto com Left Join e valores nulos(a int null, c int null)(x int null, z int null)

 

  1. y int null,
  2. Create table #xyz
  3. b int null,
  4. Create table #abc

 

 

Insert Into #abc values(null, null, 3), (1,null,3),(1,null,null),(null,2,null)

 

Insert Into #xyz values(null,2,3),(null,null,1),(null,null,2)

 

— Q1

SELECT

*

FROM

#abc a

LEFT JOIN #xyz x

ON a.a = x.x

AND a.b = x.y;

 

— Q2

SELECT

*

FROM

#abc a

LEFT JOIN #xyz x

ON COALESCE(a.a, 9) = COALESCE(x.x, 9)

AND COALESCE(a.b, 9) = COALESCE(x.y, 9);

 

 

  • Funções Char(), NChar(), Unicode() e ASCII()SELECT ASCII(36)   SELECT NCHAR(10084)  

 

    1. SELECT UNICODE(10084)
    2. SELECT CHAR(36)
  • Identificando ausência de índices em tabelas             user_scans, ID.equality_columns, FROM sys.dm_db_missing_index_group_stats GS LEFT OUTER JOIN sys.dm_db_missing_index_groups IG                                                                                                                                                                                                                                LEFT OUTER JOIN sys.dm_db_missing_index_details ID Where ID.database_id = DB_ID(‘CycleCount’) 

 

    1. ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC
    2.                                                                                                                                                                                                                                On (ID.index_handle = IG.index_handle)
    3.                                                                                    On (IG.index_group_handle = GS.group_handle)
    4.              ID.inequality_columns, ID.included_columns, ID.statement
    5. SELECT avg_total_user_cost, avg_user_impact, user_seeks,
  • Índices Clusterizados e Índices Chaves

 

SELECT t.name, i.name, i.type_desc, c.name

FROM sys.tables t

INNER JOIN sys.indexes i

INNER JOIN sys.index_columns ic

INNER JOIN sys.columns c

ON ic.object_id = c.object_id AND ic.column_id = c.column_id

ON i.object_id = ic.object_id AND i.index_id = ic.index_id

ON t.object_id = i.object_id AND i.index_id = 1;

GO

 

  • Índices com colunas duplicadas   (select distinct object_name(i.object_id) tablename,i.name indexname,                                       from sys.index_columns ic1 inner join                                                              ic1.column_id=c.column_id                                           ic1.object_id=i.object_id and                                      order by index_column_id FOR XML PATH(”)),1,2,”)               where object_id=i.object_id and index_id=i.index_id) as columnlist        sys.index_columns ic on i.object_id=ic.object_id and            sys.objects o on i.object_id=o.object_id    (select distinct object_name(i.object_id) tablename,i.name indexname,                                       from sys.index_columns ic1 inner join                                                              ic1.column_id=c.column_id                                           ic1.object_id=i.object_id and                                     order by index_column_id FOR XML PATH(”)),1,2,”)               where object_id=i.object_id and index_id=i.index_id) as columnlist        sys.index_columns ic on i.object_id=ic.object_id and            sys.objects o on i.object_id=o.object_id        substring(t2.columnlist,1,len(t1.columnlist))=t1.columnlist and          (t1.columnlist=t2.columnlist and t1.indexname<>t2.indexname))

 

  1. Go
  2.        (t1.columnlist<>t2.columnlist or
  3. where o.is_ms_shipped=0) t2 on t1.tablename=t2.tablename and
  4.                                    i.index_id=ic.index_id inner join
  5.        from sys.indexes i inner join
  6.                from sys.index_columns ic
  7.                                            ic1.index_id=i.index_id
  8.                                      where ic1.index_id = ic.index_id and
  9.                                            sys.columns c on ic1.object_id=c.object_id and
  10.              (select distinct stuff((select ‘, ‘ + c.name
  11.      where o.is_ms_shipped=0) t1 inner join
  12.                                    i.index_id=ic.index_id inner join
  13.        from sys.indexes i inner join
  14.                from sys.index_columns ic
  15.                                            ic1.index_id=i.index_id
  16.                                      where ic1.index_id = ic.index_id and
  17.                                            sys.columns c on ic1.object_id=c.object_id and
  18.              (select distinct stuff((select ‘, ‘ + c.name
  19. select t1.tablename,t1.indexname,t1.columnlist,t2.indexname,t2.columnlist from

 

  • Operador Intersect

 

CREATE TABLE A ( x INT )

 

INSERT INTO A

VALUES ( 18 ),

( 20 ),

( 4 ),

( 1 ),

( 2 )

 

CREATE TABLE B ( Y INT )

 

INSERT INTO B

VALUES ( 1 ),

( 20 ),

( 2 ),

( 3 )

 

SELECT X AS ‘Intersecting’

FROM A

INTERSECT

SELECT Y

FROM B

 

  • Taxas de Fragmentação de Tabelas antes e depois do Rebuild

 

Select

object_name(ddips.object_id) [Table_name],

convert(decimal(5,2),isnull(ddips.avg_fragmentation_in_percent,0)) [Ext_frag],

ddips.page_count [Pages],

ddips.compressed_page_count

From sys.dm_db_index_physical_stats(db_id(), object_id(‘produtos’), null, null, ‘detailed’) ddips

  • Transações aninhadas

 

CREATE Table TranTable(col varchar(3))

GO

 

BEGIN TRAN A

INSERT INTO TranTable (col) Values (‘abc’)

BEGIN TRAN B

INSERT INTO TranTable (col) Values (‘def’)

SAVE TRAN B

BEGIN TRAN C

INSERT INTO TranTable (col) Values (‘ghi’)

COMMIT TRAN C

ROLLBACK TRAN B

INSERT INTO TranTable (col) Values (‘xyz’)

COMMIT TRAN A

GO

 

Select col from TranTable

GO

 

Drop TABLE TranTable

GO

 

  • Utilitário BCP para criação de arquivos XML

 

— Exemplo 1 —

 

DECLARE @FileName varchar(50),

@bcpCommand varchar(2000)

 

SET @FileName = ‘C:\Users\test.xml’

SET @bcpCommand = ‘bcp “SELECT

”CustomerID” AS ”Parameter/name”, CustomerID AS ”Parameter/value”,

”ContactName” AS ”Parameter/name”, ContactName AS ”Parameter/value”,

”CompanyName” AS ”Parameter/name”, CompanyName AS ”Parameter/value”

FROM Sales.Customers

FOR XML PATH(”T2Method”), ROOT(”Parking”), TYPE, ELEMENTS” queryout “‘

 

SET @bcpCommand = @bcpCommand + @FileName + ‘” -T -c -x’

 

print(@bcpCommand)

 

EXEC master..xp_cmdshell @bcpCommand

 

 

 

— Exemplo 2 —

Declare @Contador Int,

@FileName varchar(50),

@bcpCommand varchar(2000)

 

Set @Contador = 1

 

While @Contador <=(Select Count(IDdaSuaTabela) from SuaTabela)

Begin

 

SET @bcpCommand = ‘bcp “SELECT ”CustomerID” AS ”Parameter/name”, CustomerID AS ”Parameter/value”,

”ContactName” AS ”Parameter/name”,

ContactName AS ”Parameter/value”,

”CompanyName” AS ”Parameter/name”,

CompanyName AS ”Parameter/value”

FROM Sales.Customers

Where IDdaSuaTabela = @Contador

FOR XML PATH(”T2Method”), ROOT(”Parking”), TYPE, ELEMENTS” queryout “‘

 

SET @FileName = ‘C:\Users\test-arquivo-‘+Convert(Varchar(10),@Contador)+’.xml’

SET @bcpCommand = @bcpCommand + @FileName + ‘” -T -c -x’

 

EXEC master..xp_cmdshell @bcpCommand

 

Set @Contador += 1

End

Mais uma vez obrigado por sua visita, fique à vontade para compartilhar este material com seus contatos. Dúvidas, comentários, sugestões e críticas, envie seu comentário ou se preferir um e-mail.

Até mais.