#25 – Para que serve


Bom dia, bom dia, bom dia, hoje estou tão feliz (kkkkk)….

Isso parece música, parece não, na verdade é…, aquela musiquinha cantadas todos os dias na entrega da escola, quem nunca cantou esta musiquinha.

Seja bem-vindo a mais um post da sessão Para que Serve, sendo este o de número 25, em mais um dia de muitas atividades, afazeres e compromissos profissionais, domésticos (isso eu não posso comentar aqui….kkkkk) e acadêmicos.

No post de hoje, vou compartilhar com você que esta acessando meu blog, um dos mais tradicionais recursos existentes no Microsoft SQL Server introduzido desde as versões iniciais do produto, mantendo-se sempre atualizada, úteis e principalmente apresentando um pouco perigoso ao ser utilizada quando não se conhece.

Da mesma maneira que realizei no post anterior desta sessão, serei mais direto no assunto, sem qualquer tipo de mistério, suspense ou criar qualquer clima de expectativa sobre este tema, na verdade estou me referindo ao uso das Table Hints (dicas de tabelas), com certeza você já deve ter ouvido falar ou já utilizou pelo menos uma delas.

Ainda não? Não se lembra? Fique tranquilo(a), vou tentar te ajudar a conhecer pouco destas pequenas mas grandiosas funcionalidades existentes no Microsoft SQL Server.

Sendo assim, chegou a hora de conhecer um pouco mais sobre o post de número 25 da sessão Para que serve. Mas uma vez, bem vindo ao #25 – Para que serve – Table Hint – Serializable.

Espero que você esteja animado para conhecer um pouco mais sobre esta funcionalidade, caso já conheça, continue lendo este post, sempre podemos aprender algo novo….


Introdução

As dicas de tabela tem um papel muito importante a partir do momento que decidimos fazer uso delas, alterar o comportamento padrão do otimizador de consulta durante a instrução DML (linguagem de manipulação de dados) ao especificar um método de bloqueio, um ou mais índices, uma operação de processamento de consulta, como uma verificação de tabela ou busca de índice, ou outras opções. As dicas da tabela são especificadas na cláusula FROM da instrução DML e afetam apenas a tabela ou exibição referenciada nessa cláusula.

Mesmo sabendo que o otimizador de consulta do SQL Server normalmente seleciona o melhor plano de execução para uma consulta, muitos desenvolvedores e administradores de banco de dados tomam a decisão de fazer uso deste recurso em diversos cenários de forma indiscriminada.

Estas dicas ou table hints, podem ao longo de sua utilização impactar de forma direta na maneira que o otimizador de consultas existente no Microsoft SQL Server trabalha desde o momento da identificação da análise da query e criação do seu plano de execução, mas principalmente no momento da própria execução.

Vale ressaltar que as mesmas serão ignoradas se a tabela não for acessada pelo plano de consulta, isso pode ser provocado porque o query optimizer opta por não acessar a tabela ou porque uma exibição indexada é acessada, mas este cenário também pode ser contornado, quem sabe em outro momento.

Vamos em frente, sabendo que estas funcionalidades precisam ser um pouco mais conhecidas e melhor entendidas, tomei a decisão de elaborar este post, tentando detalhar um pouco mais sobre a table hint Serializable.

Desta forma, vou apresentar uma breve descrição sobre esta dica de tabela, logo na sequência utilizarei um simples cenário prático para demonstrar como podemos fazer da mesma.

Como diria Chapolin Colorado “Siga-me os bons…..”

Table Hint – SERIALIZABLE

A dica de tabela SERIALIZABLE está na outra extremidade do espectro da dica de tabela NoLock (eu costume dizer bem diretamente que ela faz o papel inverso ao NoLock).

Se comparada ao NoLock, a Serializable, apresenta uma consistência extremamente alta, não permite leituras sujas, leituras não repetíveis e leituras fantasmas de dados, o que fortalece ainda mais a aplicação dos pilares ACID: (Atomicidade, Consistência, Isolamento e Durabilidade).

Quando você usa a dica de tabela SERIALIZABLE (também conhecida como HOLDLOCK), ela garante que nenhuma outra transação pode modificar ou ler dados não confirmados na transação atual. Em alguns casos, podemos dizer que ela se torna semelhante ao HOLDLOCK, porém aplica aos locks restrições maiores ao longo da transação.

Em outras palavras, as transações devem aguardar que outras transações sejam concluídas antes de concluir seu trabalho. Isso limita drasticamente a simultaneidade de banco de dados e coloca um prêmio na consistência do banco de dados. Outro ponto importante quando se referimos a table hint Serializable, se relaciona e existência de um nível de isolamento similar a seu comportamento conhecido Isolation Level Serializable.

Para muitos profissionais de SQL Server, ela é uma variação mais completa da table hint Repeatable Read, bloqueando qualquer modificação de dados nas colunas que são consultadas até que sejam concluídas, independente da operação ser um Update ou Insert, esse comportamento fornece uma alta consistência, mas ao custo de uma baixa concorrência.

Agora que conhecemos um pouco mais sobre esta table hint, vamos avançar, conhecer o seu funcionamento de forma prática, para tal, utilizaremos como base o Bloco de Código 1, que realizará a criação do seguinte ambiente:

·         Databases: DatabaseTableHints;

·         Table: SerializableTable; e

·         Transações: TST e TSTII.

— Bloco de Código 1 —

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

— Acessando o Banco de Dados – DatabaseTableHints
Use DatabaseTableHints
Go

 — Criando a Tabela – SerializableTable —
Create Table SerializableTable
(Codigo Int Identity(1,1) Not Null Primary Key Clustered,
Valores Varchar(30))
Go

Até aqui nada de especial, nosso ambiente já está criado e pronto para receber alguns dados, vamos utilizar o Bloco de Código 2, responsável em realizar o insert e consulta dos dados, conforme apresento a seguir:

— Bloco de Código 2 —
— Inserindo uma pequena porção de dados —
Insert Into  SerializableTable
Values (‘Pedro’), (‘Antonio’), (‘Galvão’), (‘Junior’),
(‘MVP’), (‘MCC’), (‘MSTC’), (‘MIE’), (‘SQL Server’),
(‘Banco de Dados’),(‘Table Hint SerializableTable’)
Go

— Consultando os dados —
Select Codigo, Valores From SerializableTable
Order By Valores Desc
Go

A executarmos o comando Select declarado acima, deveremos ter um result set semelhante ao apresentado na Figura 1 a seguir:

Figura  1 – Processamento do comando Select apresentando os dados inseridos na Table SerializableTable.

Avançando mais um pouco, neste momento temos uma porção de dados armazenados em nossa table SerializabelTable, o que nos permite realizar uma pequena simulação de seu comportamento e entender como a ela realiza o bloqueio em nossa query.

Vamos então realizar a execução do Bloco de Código 3, sendo este o ponto chave que vai nortear nosso entendimento, observe que estamos abrindo um bloco transacional denominado TST (conforme já destacado anteriormente).

— Bloco de Código 3 —
— Iniciando um novo Bloco de Transações denominada TST —
Begin Transaction TST

Observe que estou fazendo uso do comando Begin Transaction abrindo nosso bloco de transação denominado TST, o qual consiste na execução do comando Select em conjunto com table hint Serializable.

Neste instante o Microsoft SQL Server em conjunto com alguns de seus mais importantes componentes: Database Engine e Storage Engine, estabelecem uma nova transação que deverá ser processada dentro da sessão que estamos trabalhando mais que a mesma não recebeu até o presente momento nenhuma instrução Commit ou Rollback.

Select * From SerializableTable With (Serializable)  — Especificando a Table Hint Serializable —
Go
— Forçando um Delay de 10 segundos para gerar bloqueio no nível de leitura compartilhadas  —
WaitFor Delay ’00:00:10′
Go
Importante: O uso do comando WaitFor, vai nos ajudar a ter tempo para abrir uma nova query e executar os próximos passos que deveremos realizar, note que abaixo já estou declarando o comando Commit para confirmar a execução da nossa transação, que na verdade consiste na busca dos linhas de dados existentes na tabela SerializableTable e apresentadas em tela.
Commit Transaction TST
Go
A Figura 2 apresentada abaixo, ilustra o processamento do Bloco de Código 3, o que nos permite identificar a ocorrência do Delay de 10 segundos:
Figura 2 – Processamento do Bloco de Código 3.
Muito bem, agora é a hora da verdade, em paralelo a execução do Bloco de Código 3, vamos executar vamos executar o Bloco de Código 4, o qual vai nos exigir abrir uma nova query dentro da ferramenta que você se encontra acessando o Microsoft SQL Server no momento, eu como de costume estou utilizando o bom e velho Management Studio.

— Bloco de Código 4 —
— Abrir nova query e executar o Select abaixo, após 10 segundos os dados serão apresentados —
Begin Transaction TSTII
Select ‘Aguardando…’ As ‘Passo 1…’
Go

Select GetDate() As ‘Passo 2 – Update Realizado…’
Update SerializableTable
Set Valores = ‘SerializableTable’
Where Codigo = 11
Go
Select GetDate() As ‘Passo 3 – Apresentar dados…’
Go
Select Codigo, Valores From SerializableTable
Where Codigo = 11
Commit Transaction TSTII
Go
Select GetDate() As ‘Transações confirmadas…’
Go
Posso dizer que a execução do Bloco de Código 4, aparentemente foi super tranquilo e simples, mas tenho a certeza que após sua execução você conseguiu entender o funcionamento da table hint Serializable.
A Figura 3 a seguir, apresenta o resultados obtidos através do processamento deste bloco de código:
Figura 3 – Realização do bloco de transação TSTII em paralelo ao TST.
Você pode observar que a partir do momento que nossa primeira transação TST estava sendo processada, a segunda transação TSTII teve seu processamento iniciado, mas ficou aguardando a confirmação de conclusão da TST.
Isso não é fantástico, com certeza é, realmente o uso das table hints em situações ou necessidades específicas pode ser de grande avalia em nossas atividades, mas calma, muita calma neste hora, pois não devemos utilizar nenhum recurso ou funcionalidade sem conhecer melhor seu comportamento, este foi justamente o objetivo principal do post de hoje.

Com isso, e sem mais delongas, chegamos ao final. Foi muito legal poder mostrar um pouco mais sobre as table hints existentes no Microsoft SQL Server, claro de que um pouco de trabalho este post, mas já estou acostume, mesmo assim sempre vale a pena poder compartilhar um o conhecimento e experiências adquiridas ao longo dos anos de trabalho como DBA e Professor.

Espero que você tenha gostado, eu posso dizer que sim, mas sua opinião é muito importante.


Referências

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017

https://sqlperformance.com/2014/04/t-sql-queries/the-serializable-isolation-level

https://www.techrepublic.com/article/using-advanced-table-hints-in-sql-server/

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

https://pedrogalvaojunior.wordpress.com/2019/02/23/24-para-que-serve/

http://www.verycomputer.com/156_c5e201f3b1bce12f_1.htm

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/2019/02/23/24-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/12/21/23-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/08/22/22-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/05/28/21-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/04/12/20-para-que-serve/

 

Conclusão

Mais uma vez podemos observar como o Microsoft SQL Server apresenta um conjunto grandioso de funcionalidades, utilizadas de maneiras diferentes e específicas para cada cenário.

Neste post podemos conhecer, aprender e visualizar como as table hint Serializabel pode ser utilizada, seus comportamento, além disso, entender como uma consulta usa o nível de isolamento SERIALIZABLE ou a table hint HOLDLOCK.

Demonstrei como o Microsfot SQL Server pode aplicar bloqueios compartilhados até que uma transação seja concluída, papel fundamente para qualquer SGBD de alto nível, ainda mais o SQL Server, que possui a capacidade de compreender e permitir que bloqueios ocorram e possam ser feitos durante o processamento de diversas querys ou transações.

Este é o fantástico Microsoft SQL Server, que desde suas primeiras versões nos apresenta inúmeras possibilidades de se aprender, possibilitando usar o passado como fonte de inspiração para construção de um futuro melhor, por isso que a cada dia eu me apaixono ainda mais por este produto…

Vai SQL Server, Vai SQL Server….

Agradecimentos

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 a ser publicado no mês agosto de 2019.

Um grande abraço e ótimo final de semana.

Valeu.

Material de Apoio – Abril 2019


Olá, muito bom dia….

Tudo bem? Antes de começar este post, quero lhe perguntar: E ai já se pesou? Anotou na balança ou no seu banco de dados da tabela de pesos o quanto você esta pesando antes da páscoa! KKKK, é minha gente, estamos no mês de abril, a páscoa esta chegando e com ela o coelhinho trazendo muitos docinhos…..

Bom, antes de darmos aquela engordadinha com tantos chocolatinhos, balinhas e docinhos desta doce época do ano, estou de volta cumprindo a minha missão de  colaborar e compartilhar com a comunidade técnica mais um post da sessão Material de Apoio dedicado exclusivamente ao meu blog.

Espero que você esteja gostando do conteúdo aqui disponibilizado, como também, possa me ajudar a torná-lo ainda melhor no decorrer do tempo com a sua participação.

O post de hoje

Seja bem-vindo a mais um post da sessão Material de Apoio, sendo o segundo post da sessão em 2019 e de número 162 no total da mesma.

Para aqueles que já acompanham o meu blog a um certo tempo, os posts dedicados a sessão Material de Apoio, possuem o objetivo de compartilhar o conhecimento de recursos, funcionalidades e procedimentos que podemos realizar no Microsoft SQL Server.

Hoje não será diferente, estou trazendo alguns dos mais recentes scripts catalogados nos últimos meses, que atualmente estão compondo a minha galeria de códigos formada ao longo dos anos de trabalho como DBA e atualmente como Professor de Banco de Dados.

Neste post você vai encontrar arquivos relacionados com os seguintes temas:

  • Acessos;
  • Any Constraints;
  • Arquivo XLS;
  • Arquivo MDB;
  • Cálculo de dias úteis;
  • Casas Decimais;
  • Comando Backup Database;
  • Comando Backup Database With Move Option;
  • Comando Declare;
  • Comando Deny;
  • Comando Grant;
  • Comando Insert;
  • Comando OpenDataSource;
  • Comando OpenRowSet;
  • Comando Restore Database;
  • Comando Revoke;
  • Comando Select;
  • Comando Update;
  • Constraints;
  • Create Database;
  • Create Logins;
  • Create Table;
  • Create Users;
  • Declaração de Variáveis;
  • Delete Cascade;
  • Diretiva Set;
  • Drop Table;
  • Exportação de Dados;
  • Função CharIndex;
  • Função DataLength;
  • Função Floor;
  • Função Left;
  • Função Len;
  • Função Right;
  • Gerenciamento de acesso para Banco de Dados e Tabelas;
  • Importação de Dados;
  • Logins;
  • Microsoft Access;
  • Microsoft Excel;
  • Permissões;
  • Schemas;
  • Stored Procedure;
  • Stored Procedure SP_Attach_DB;
  • Stored Procedure SP_Detach_DB;
  • Tipo de Dados Decimal;
  • Tipo de Dados Int;
  • Tipo de Dados Money;
  • Update Cascade; e
  • Users.

Espero que este conteúdo possa lhe ajudar em seus atividades profissionais e acadêmicas. Por questões de compatibilidade com a plataforma WordPress.com, todos os arquivos estão renomeados com a extensão .docx ao final do seu respectivo nome, sendo assim, após o download torna-se necessário remover esta extensão, mantendo somente a extensão padrão .sql.

Material de Apoio

A seguir apresento a relação de arquivos  selecionados:

1 – Material de Apoio – Abril – 2019 – Comando – Alter Table + Any Constrainst.sql

2 – Material de Apoio – Abril – 2019 – Comando – Alter Table With NoCheck.sql

3 – Material de Apoio – Abril – 2019 – Identificando a quantidade de Casas Decimais.sql

4 – Material de Apoio – Abril – 2019 – Calculando Dias Úteis.sql

5 – Material de Apoio – Abril – 2019 – Realizando o Attach e Detach Database.sql

6 – Material de Apoio – Abril – 2019 – Gerenciando – Acesso para Banco de Dados.sql

7 – Material de Apoio – Abril – 2019 – Gerenciando – Acesso para Tables.sql

8 – Material de Apoio – Abril – 2019 – Stored Procedure P_ImportarBanco.sql

9 – Material de Apoio – Abril – 2019 – OpenDataSource lendo dados em um arquivo XLS.sql

10 – Material de Apoio – Abril – 2019 – Utilizando comando OpenRowSet para importar dados do Microsoft Access.sql

Fique a vontade para copiar, editar, compartilhar e distribuir estes arquivos com seus contatos, aproveite se possível deixe seu comentário, críticas, sugestões e observações.

Nota: Todos os arquivos disponibilizados foram obtidos ou criados com autorização de seus autores, sendo estes, passíveis de direitos autorais.

Links

Caso você queira acessar os posts anteriores da sessão, não perca tempo utilize os links listados abaixo:

https://pedrogalvaojunior.wordpress.com/2018/12/18/material-de-apoio-dezembro-2018/

https://pedrogalvaojunior.wordpress.com/2018/10/30/material-de-apoio-outubro-2018/

https://pedrogalvaojunior.wordpress.com/2018/08/14/material-de-apoio-agosto-2018/

https://pedrogalvaojunior.wordpress.com/2018/06/19/material-de-apoio-junho-2018/

Agradecimento

Quero agradecer imensamente a sua visita, sinto-me honrado e orgulhoso de contar com a sua presença.

Não deixe de acessar os outros posts das demais sessões, o próximo post desta sessão será publicado no mês de junho, até lá, continue aproveitando cada momento da sua vida, desfrutando com muita sabedoria os momentos de galeria e também os desafios que são colocados ao seu redor.

Um forte abraço, muita saúde, sucesso e nos encontramos em breve.

Valeu.

SQL Server Management Studio 18.0 Release Candidate 1 liberado


Através do anuncio realizado ontem em seu blog dedicado ao Microsoft SQL Server, o Senior Program Manager – Dinakar Nethi, realizou o anúncio oficial da disponibilidade da primeira versão release candidate do próximo Microsoft SQL Server Management Studio 2018.

O Senior Program Manager: “À medida que nos aproximamos da disponibilidade geral do SQL Server Management Studio (SSMS) 18, decidimos ter uma versão rápida da compilação Release Candidate (RC).”

Melhorias do SQL Server Management Studio

  • Habilitando a conectividade de ponto de extremidade XMLA para DataSets do Power BI: os endpoints XMLA fornecem acesso ao mecanismo do Analysis Services no serviço do Power BI. Isso permite que ferramentas como o SSMS e o SQL Profiler se conectem a conjuntos de fonte do Power BI para monitoramento, gerenciamento e depuração, etc. 
  • SQL Server Management Objects (SMO)
    – Adicionado suporte de exclusão em cascata para “Edge Constraints” no SMO e no SSMS.
    – Adicionado suporte para permissões de classificação de dados “leitura-gravação”.
  • Audit Files
    – Atualizada a lista de ações de auditoria conhecidas para FEATURE RESTRICTION ADD/CHANGE GROUP/DROP.

 

Correções de bugs NO SSMS

  • Corrigido um problema que estava impedindo a autenticação de MFA quando os IDs de usuário pertenciam a vários locatários.
  • Corrigido um problema em que o relatório do painel perf estava relatando as esperas PAGELATCH e PAGEIOLATCH que não podiam ser encontradas em sub-relatórios.
  • Corrigido um problema que resultou em uma exceção de “índice fora do intervalo” ao clicar na grade.
  • Corrigido um problema em que a cor do plano de fundo do resultado da grade estava sendo ignorada. 

Importante

  1. Como de costume, se você estiver utilizando uma versão anterior do SSMS 18.0 Preview, será necessário desinstalá-la primeiro.
  2. O SSMS 18,0 RC1 pode ser instalado lado a lado com o SSMS 17. x para fins de teste.
  3. Como lembrete, o uso do software pre-GA em ambientes de produção não é suportado.

Você pode realizar o download acessando o link: download SSMS 18.0 RC1.

Para maiores informações sobre a relação completa de correções e melhorias, acesse a Release Notes.


Fontes e Direitos  Autorais: Dinakar Nethi – Senior Program Manager, Azure Data – 28/03/2019.
Leia na integra: https://cloudblogs.microsoft.com/sqlserver/2019/03/28/sql-server-management-studio-18-0-release-candidate-1-is-now-available/

Microsoft libera nova preview do SQL Server 2019 denominada 2.4


Ontem a Microsoft através do seu time MSSQLTiger, disponibilizou a nova versão preview da próxima versão do Microsoft SQL Server 2019.

Este novo preview foi batizado de SQL Server 2019 2.4 (quinta versão), vale ressaltar que nos últimos meses a Microsoft vem trabalhando fortemente para liberação de novas versões preliminares.

Novidades

Um dos destaques desta nova versão preliminar faz referência a integração do SQL Server com o Apache Spark™ e o HDFS com o SQL Server, criando assim  uma nova plataforma de dados unificada, que possibilita um grande salto no produto relacionado com os novos padrões de dados e processamento em larga escala.

Segundo informações fornecidas pela Microsoft, o SQL Server 2019 oferece mais segurança, disponibilidade e desempenho para todas as cargas de dados, além de trazer novas ferramentas de conformidade (relacionadas a GDPR e LGPD), melhor desempenho em hardware moderno e alta disponibilidade em Windows, Linux e contêineres.

A Figura 1 abaixo ilustra a página inicial do website da Microsoft dedicado ao Microsoft SQL Server 2019:

Figura 1 – Introdução ao Microsoft SQL Server 2019.

Melhorias

Esta versão inclui melhorias de versões CTP anteriores para corrigir bugs, melhorar a segurança e otimizar o desempenho.

Além disso, os seguintes recursos são adicionados ou aprimorados para SQL Server 2019 pré-visualização CTP 2.4:

    • Cluster de grande volume de dados
      • Orientação na GPU suporte para a execução de profunda aprendizagem com TensorFlow no Spark.
      • Atualização em tempo real para Spark 2.4.
    • Mecanismo de banco de dados
      • Novo evento estendido.query_post_execution_plan_profile
      • Nova DMF retorna o equivalente o último plano de execução real conhecido para a maioria das consultas.sys.dm_exec_query_plan_stats
      • Criptografia de dados transparente (TDE) varredura – suspender e continuar.
    • SQL Server Analysis Services
      • Relacionamentos muitos-para-muitos em modelos tabulares.
      • Configurações de propriedade para a gerenciamento de recursos.

Download

Dentre as opções disponíveis, você poderá optar tanto para rodar em ambientes físicos como Windows e Linux ou virtualizados através de máquinas virtuais ou containers, conforme Figura 2 abaixo ilustra:

Figura 2 – Plataformas para download disponíveis para versão 2.3 do Microsoft SQL Server 2019.

O Microsoft Server 2019 Preview 2.4 para Windows está disponível em arquivos ISO, arquivo .cab para os seguintes idiomas:

  • Inglês;
  • Alemão;
  • Japonês;
  • Espanhol;
  • Coreano;
  • Russo;
  • Italiano;
  • Francês;
  • Chinês (simplificado);
  • Chinês (tradicional); e
  • Português (Brasil)‎.

Saiba mais sobre o SQL Server 2019

Caso você queria saber mais sobre esta nova versão do Microsoft SQL Server e seus principais recursos, selecione um dos links abaixo:

https://info.microsoft.com/ww-landing-SQLDB-Microsoft-SQL-Server-WhitePaper.html

http://download.microsoft.com/download/8/B/6/8B643729-6224-4ECC-8C50-3292B8156F0E/SQL_Server_2019_Transform-Data_into_Insights_Infographic_EN_US.pdf

http://download.microsoft.com/download/D/2/5/D2519504-0ACD-4CD7-9C34-AB85D5824F34/SQL_Server_2019_Top_10_Reasons_to_Choose_Infographic_EN_US.pdf

https://info.microsoft.com/ww-landing-intro-sql-server-2019.html

Fontes e Direitos Autorais: Microsoft.com – SQL Server BlogSQL Server Team – 27/03/2019.
Leia na integra acessando: https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-ver15?view=sql-server-ver15#ctp-24

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 – Março 2019


Olá boa tarde, estamos no mês de março, primeiro trimestre de 2019 esta quase se encerrando.

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 36 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

Como de costume selecionei os principais scripts armazenados recentemente na minha biblioteca de códigos, que apresentam os seguintes assuntos:

  • Cálculo de Idade;
  • Cláusula Values;
  • Cláusula Where;
  • Comando Create Procedure;
  • Comando Declare;
  • Comando Exec;
  • Comando Group By;
  • Comando Insert;
  • Comando Order By;
  • Comando Pivot;
  • Comando Select;
  • Common Table Expression;
  • Diretiva Set;
  • DMV sys.dm_os_ring_buffers;
  • DMV sys.dm_os_virtual_address_dump;
  • Filegroup;
  • Função Concat();
  • Função DateDiff();
  • Função de Agregação SUM;
  • Função Month();
  • Função Year();
  • Gerenciamento de Memória;
  • Grupo de Arquivos;
  • Inner Joins;
  • Left Joins;
  • Operador Lógico Condicional Case;
  • Operador Lógico Condicional While;
  • Stored Procedure;
  • Variáveis;
  • Variável do Tipo Tabela;
  • Visão;
  • Visão de Sistema sys.all_objects;
  • Visão de Sistema sys.filegroups;
  • Visão de Sistema sys.indexes;
  • Visão de Sistema sys.syslanguages;
  • Visão de Sistema SYSCACHEOBJECTS; e
  • Visão de Sistemas sys.messages.

Chegou a hora, mãos nos teclados, a seguir apresento os códigos e exemplos selecionados para o Short Script – Março 2019. 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 à vontade para compartilhar, comentar e melhorar cada um destes códigos.

Short Scripts

— Short Script 1  – Alterando a sequência numérica com base em uma condição —

— Criando a Tabela Valores —
Create Table Valores
(Contador Int Primary Key Identity(1,1),
CodigoCaracter Char(4),
SequenciaDeControle Int)
Go

 

— Inserindo os registros com CódigoCaracter 0001 —
Insert Into Valores Values (‘0001’,1),(‘0001’,2),(‘0001’,3),(‘0001’,4)
Go

— Inserindo os registros com CódigoCaracter 0002 —
Insert Into Valores Values (‘0002’,5),(‘0002’,6),(‘0002’,7),(‘0002’,8)
Go

— Inserindo os registros com CódigoCaracter 0003 —
Insert Into Valores Values (‘0003’,9),(‘0003’,10)
Go

 

— Consultando os registros inseridos —
Select Contador, CodigoCaracter, SequenciaDeControle from Valores
Go

 

— Declarando um bloco de execução para alterar os registros de acordo com uma condição —
Declare @ContadorRegistros Int,
@ContadorSequenciaDeControle Int,
@ValorColunaCodigoCaracter Char(4)

Set @ContadorRegistros=1
Set @ContadorSequenciaDeControle=1

While @ContadorRegistros <= (Select Count(CodigoCaracter) from Valores)
Begin

Set @ValorColunaCodigoCaracter=(Select CodigoCaracter From Valores Where Contador = @ContadorRegistros)

If (@ValorColunaCodigoCaracter = (Select CodigoCaracter From Valores Where Contador = @ContadorRegistros) And @ContadorRegistros > 1)
Set @ContadorSequenciaDeControle=@ContadorSequenciaDeControle + 1
Else
Set @ContadorSequenciaDeControle=0

Update Valores
Set SequenciaDeControle = @ContadorSequenciaDeControle
Where Contador = @ContadorRegistros

Set @ContadorRegistros += 1

End

 

— Consultando os registros após a alteração —
Select Contador, CodigoCaracter, SequenciaDeControle from Valores
Go

 

— Short Script 2 – Utilizando Extended Events – Ring Buffer através da sessão system_health para obter detalhes sobre conexões abertas —

Declare @LanguageID int

Select @LanguageID = lcid
From sys.syslanguages
Where name = @@Language

WITH RingBufferXML
As (SELECT CAST(record as xml) AS RecordXML
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type= ‘RING_BUFFER_CONNECTIVITY’),
RingBufferConnectivity
As (SELECT x.y.value(‘(/Record/@id)[1]’, ‘int’) AS [RecordID],
x.y.value(‘(/Record/ConnectivityTraceRecord/RecordType)[1]’, ‘varchar(max)’) AS RecordType,
x.y.value(‘(/Record/ConnectivityTraceRecord/RecordTime)[1]’, ‘datetime’) AS RecordTime,
x.y.value(‘(/Record/ConnectivityTraceRecord/SniConsumerError)[1]’, ‘int’) AS Error,
x.y.value(‘(/Record/ConnectivityTraceRecord/State)[1]’, ‘int’) AS State,
x.y.value(‘(/Record/ConnectivityTraceRecord/Spid)[1]’, ‘int’) AS SPID,
x.y.value(‘(/Record/ConnectivityTraceRecord/RemoteHost)[1]’, ‘varchar(max)’) AS RemoteHost,
x.y.value(‘(/Record/ConnectivityTraceRecord/RemotePort)[1]’, ‘varchar(max)’) AS RemotePort,
x.y.value(‘(/Record/ConnectivityTraceRecord/LocalHost)[1]’, ‘varchar(max)’) AS LocalHost,
x.y.value(‘(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/TotalTime)[1]’, ‘int’) AS TotalTime,
x.y.value(‘(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/EnqueueTime)[1]’, ‘int’) AS EnqueueTime,
x.y.value(‘(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/NetWritesTime)[1]’, ‘int’) AS NetWritesTime,
x.y.value(‘(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/NetReadsTime)[1]’, ‘int’) AS NetReadsTime,
x.y.value(‘(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Ssl/TotalTime)[1]’, ‘int’) AS SslTotalTime,
x.y.value(‘(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Sspi/TotalTime)[1]’, ‘int’) AS SspiTotalTime,
x.y.value(‘(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/TriggerAndResGovTime)[1]’, ‘int’) AS TriggerAndResGovTime
FROM RingBufferXML
CROSS APPLY RecordXML.nodes(‘//Record’) AS x(y))

SELECT RBC.*, m.text
FROM RingBufferConnectivity RBC
LEFT JOIN sys.messages M ON
RBC.Error = M.message_id AND M.language_id = @LanguageID
WHERE RBC.RecordType IN (‘Error’, ‘LoginTimers’)
ORDER BY RBC.RecordTime DESC
Go

 

— Short Script 3 – Stored Procedure – Realizando o versionamento de código fonte  —

CREATE PROCEDURE dbo.GetOne
AS
SELECT 1
GO

CREATE PROCEDURE dbo.GetOne;2 —Versionamento, recurso antigo
AS
SELECT 2
GO

 

Exec dbo.GetOne;2 — Executando a procedure versão 2
go

 

 

— Short Script 4 – Gerenciamento de Memória  — Como encontrar quem está usandoo espaço de endereço virtual no seu SQL Server —

 

SELECT convert(varchar,getdate(),120) as [Timestamp], max(region_size_in_bytes)/1024 [Total max contiguous block size in KB]

from sys.dm_os_virtual_address_dump

where region_state = 0x00010000 — MEM_FREE
Go

 

Select SUM(virtual_memory_reserved_kb)/1024 as virtual_memory_reserved_mb

from sys.dm_os_memory_clerks

Where type not like ‘%bufferpool%’
Go

With VASummary(Size,Reserved,Free) AS
(SELECT
Size = VaDump.Size,
Reserved =  SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
SELECT  CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size, region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address

UNION

SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address  = 0x0
)
AS VaDump
GROUP BY Size)
SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail Mem, KB] ,CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0
Go

 

SELECT SUM(PAGESUSED)*8/1024 ‘MB of MemToLeave memory consumed by procedures’ FROM MASTER.DBO.SYSCACHEOBJECTS

WHERE PAGESUSED >1
Go

 

— Short Script 5 – Criando um Pivot em conjunto com Visão com Sumarização —

Create Table Fornos
(Id Int Primary Key,
Defeito Varchar(20) Not Null,
Forno Varchar(20) Not Null,
Equipe Varchar(20) Not Null)
Go

 

Insert Into Fornos
Values (1,’DEFEITO A’,’FORNO 3′,’AZUL’),
(2,’DEFEITO A’,’FORNO 2′,’VERDE’),
(3,’DEFEITO B’,’FORNO 1′,’AZUL’),
(4,’DEFEITO A’,’FORNO 1′,’PRETO’),
(5,’DEFEITO B’,’FORNO 2′,’VERDE’),
(6,’DEFEITO B’,’FORNO 2′,’AZUL’),
(7,’DEFEITO A’,’FORNO 1′,’PRETO’),
(8,’DEFEITO A’,’FORNO 2′,’AZUL’)
Go

Select * From Fornos
Go

 

— Gerando o Pivot —
Select * from
(Select CONCAT(Defeito,’ – ‘, Equipe) As DefeitosPorEquipe,
CONCAT(Defeito,’ – ‘, Equipe) As ‘Defeitos Agrupados Por Equipes’,
Equipe,
Forno As ‘Fornos’
From Fornos) As F
Pivot (Count(DefeitosPorEquipe) For Equipe In ([Azul],[Preto],[Verde])) as Pvt
Go

 

— Adicionando os Totais —
Select * from
(
Select CONCAT(Defeito,’ – ‘, Equipe) As DefeitosPorEquipe,
CONCAT(Defeito,’ – ‘, Equipe) As ‘Defeitos Agrupados Por Equipes’,
Equipe,
Forno As ‘Fornos’
From Fornos
) As F
Pivot (Count(DefeitosPorEquipe) For Equipe In ([Azul],[Preto],[Verde])) as Pvt

Union All

Select ‘Totais….’, ‘—->’,
Sum(Azul) As SomaAzul,
Sum(Preto) As SomaPreto,
Sum(Verde) As SomaVerde
From
(
Select CONCAT(Defeito,’ – ‘, Equipe) As DefeitosPorEquipe,
Equipe
From Fornos
) As F
Pivot (Count(DefeitosPorEquipe) For Equipe In ([Azul],[Preto],[Verde])) as Pvt
Go

 

— Short Script 6 – Identificando o filegroup vinculado para cada tabela de usuário —

SELECT o.[name],
o.[type],
i.[name],
i.[index_id],
f.[name]
FROM sys.indexes i INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id
AND o.type = ‘U’ — User Created Tables
GO

 

— Short Script 7 – Realizando o cálculo da idade —

— Exemplo 1 —
Declare @Hoje date

Set @Hoje= cast (current_timestamp as date)

 

SELECT CPF, DATA_NASC,
case when month(@Hoje) > month(DATA_NASC)
then datediff (year, DATA_NASC, @Hoje)
when month(@Hoje) = month(DATA_NASC) and day(@Hoje) >= day(DATA_NASC)
then datediff (year, DATA_NASC, @Hoje)
else datediff (year, DATA_NASC, @Hoje) -1
end as Idade
from tb_idade_dez
Go

 

— Exemplo 2 —
Select CPF, DATA_NASC, (datediff (month, DATA_NASC, @Hoje) / 12) as Idade

from tb_idade_dez
Go

 

— Exemplo 3 —
Select CPF, DATA_NASC, datediff(day,DATA_NASC,getdate())/365.15 as idade

from TB_IDADE_JANEIRO
Go

 

— Exemplo 4 —
Declare @DATAS table (DataNasc date)

INSERT into @DATAS values
(‘19880101’), (‘19880111’), (‘19880112’), (‘19880113’),
(‘19880213’), (‘19880313’), (‘19880413’), (‘19880513’),
(‘19880613’), (‘19880713’), (‘19880813’), (‘19880913’),
(‘19881013’), (‘19881113’), (‘19881213’)

 

Declare @Hoje date
Set @Hoje= ‘20190112’

 

SELECT DataNasc, @Hoje as Hoje,
datediff(day, DataNasc, @Hoje)/365.15 as idade,
case when month(@Hoje) > month(DataNasc) then datediff (year, DataNasc, @Hoje)
when month(@Hoje) = month(DataNasc) and day(@Hoje) >= day(DataNasc) then datediff (year, DataNasc, @Hoje)
else
datediff (year, DataNasc, @Hoje) -1
end as Anos
from @DATAS
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/11/20/short-scripts-novembro-2018/

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/

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 no mês de junho.

Um forte abraço, saúde e paz.

Até mais.

Novo Microsoft SQL Server Migration Assistant v8.1 disponível


A Microsoft disponibilizou na última terça – feira dia 05/03 o Microsoft SQL Server Migration Assistant v8.1, ferramenta gratuita que permite a migração de dados existentes em outras soluções de bancos de dados, como:

De uma forma bastante simples e prática é possível consumir os dados armazenados nas soluções listadas acima, enviando os mesmos diretamente para o Microsoft SQL Server e Microsoft Azure SQL.

O Microsoft SQL Server Migration Assistant v8.0 é compatível com o sistemas operacionais:

  • Windows 7;
  • Windows 8;
  • Windows 8.1;
  • Windows 10;
  • Windows Server 2008 R2;
  • Windows Server 2012;
  • Windows Server 2012 R2; e
  • Windows Server 2016.

Esta nova versão do Microsoft SQL Server Migration Assistant  inclui o suporte para:

– MySQL 4.1 e posteriores para todas as edições do SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, Azure SQL DB e Azure SQL Database Managed Instance;

–  Access 97 e posteriores para todas as edições do SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, Azure SQL DB e Azure SQL Database Managed Instance;

– SAP Adaptive Server Enterprise (ex-Sybase) 11.9 e posteriores para todas as edições do SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, Azure SQL DB e Azure SQL Database Managed Instance;

–  Oracle Database 9.0 e posteriores para todas as edições do SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, Azure SQL DB, Azure SQL Data Warehouse e Azure SQL Database Managed Instance; e

– IBM DB2 9.0 e 10.0 no z/OS e das versões 9.7 e 10.1 no Linux/Unix/Windows para o SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017,SQL Server 2019, Azure SQL DB e Azure SQL Database Managed Instance.

Microsoft SQL Server Migration Assistant v8.1

O download pode ser realizado de acordo com sua fonte de dados de origem, selecione uma das opções existentes abaixo:

Recursos adicionais

Fontes e Direitos Autorais: Microsoft.com – Download Center – https://blogs.msdn.microsoft.com/datamigration/2019/03/05/release-sql-server-migration-assistant-ssma-v8-1/

Microsoft libera nova preview do SQL Server 2019 denominada 2.3


Em grande evento dedicado ao Microsoft SQL Server que esta sendo realizado na Inglaterra o SQL Bits, a Microsoft informou a  liberação de mais uma versão preview da próxima versão SQL Server 2019.

Este novo preview foi batizado de SQL Server 2019 2.3 (quarta versão), vale ressaltar que nos últimos meses a Microsoft vem trabalhando fortemente para liberação de novas versões preliminares.

Novidades

Um dos destaques desta nova versão preliminar faz referência a integração do SQL Server com o Apache Spark™ e o HDFS com o SQL Server, criando assim  uma nova plataforma de dados unificada, que possibilita um grande salto no produto relacionado com os novos padrões de dados e processamento em larga escala. 

Segundo informações fornecidas pela Microsoft, o SQL Server 2019 oferece mais segurança, disponibilidade e desempenho para todas as cargas de dados, além de trazer novas ferramentas de conformidade (relacionadas a GDPR e LGPD), melhor desempenho em hardware moderno e alta disponibilidade em Windows, Linux e contêineres.

A Figura 1 abaixo ilustra a página inicial do website da Microsoft dedicado ao Microsoft SQL Server 2019:

Figura 1 – Introdução ao Microsoft SQL Server 2019.

Melhorias

  • Recuperação de banco de dados acelerada para fornecer melhor aproveitamento do tempo de recuperação e possibilidades de reversão instantânea para transações de longa duração;
  • Aprimoramentos de desempenho em recompilações de plano de consulta, gerenciamento de log de transações e repositório de consultas; e
  • Melhorias no recurso SQL Graph, permitindo exclusão em cascata para bordas e nós.

Download

Dentre as opções disponíveis, você poderá optar tanto para rodar em ambientes físicos como Windows e Linux ou virtualizados através de máquinas virtuais ou containers, conforme Figura 2 abaixo ilustra:

Figura 2 – Plataformas para download disponíveis para versão 2.3 do Microsoft SQL Server 2019.

O Microsoft Server 2019 Preview 2.3 para Windows está disponível em arquivos ISO, arquivo .cab para os seguintes idiomas:

  • ‎Inglês;
  • Alemão;
  • Japonês;
  • Espanhol;
  • Coreano;
  • Russo;
  • Italiano;
  • Francês;
  • Chinês (simplificado);
  • Chinês (tradicional); e
  • Português (Brasil)‎.

Saiba mais sobre o SQL Server 2019

Caso você queria saber mais sobre esta nova versão do Microsoft SQL Server e seus principais recursos, selecione um dos links abaixo:

https://info.microsoft.com/ww-landing-SQLDB-Microsoft-SQL-Server-WhitePaper.html

http://download.microsoft.com/download/8/B/6/8B643729-6224-4ECC-8C50-3292B8156F0E/SQL_Server_2019_Transform-Data_into_Insights_Infographic_EN_US.pdf

http://download.microsoft.com/download/D/2/5/D2519504-0ACD-4CD7-9C34-AB85D5824F34/SQL_Server_2019_Top_10_Reasons_to_Choose_Infographic_EN_US.pdf

https://info.microsoft.com/ww-landing-intro-sql-server-2019.html

Fontes e Direitos Autorais: Microsoft.com – SQL Server BlogSQL Server Team – 07/03/2019.
Leia na integra acessando: https://cloudblogs.microsoft.com/sqlserver/2019/03/01/sql-server-2019-community-technology-preview-2-3-is-now-available/

Visual Studio 2019 Release Candidate (RC) já está disponível


Em seu blog oficial dedicado ao Visual Studio, a Microsoft realizou o anuncio informando sobre a liberação da primeira versão RC – Release Candidate do novo Microsoft Visual Studio 2019, o qual será oficialmente apresentando para o mundo no dia 02 de abril.

Release Candidate

Segundo, John Montgomery – Diretor de gestão do programa. “Com esta versão, estamos introduzindo dois produtos “canais“: o canal de lançamento e o canal de visualização.

A partir de hoje, o Visual Studio 2019 RC está disponível no canal de lançamento (visualstudio.com/downloads) e o Visual Studio 2019 Preview 4 está disponível no canal de visualização (visualstudio.com/preview). Ambas as versões podem ser instaladas e usadas lado a lado e, agora, ambos os canais contêm os mesmos bits.

A partir de 2 de abril, a compilação do canal de lançamento (RC) pode ser atualizada para a nossa versão disponível em geral (GA), que estará pronta para uso na produção. O canal de visualização continuará a oferecer uma visão antecipada dos recursos programados, assim como você se acostumou a no Visual Studio 2017.

Visual Studio 2019 canais do produto.

Estamos incrivelmente gratos a todos os usuários que têm vindo a experimentar o Visual Studio 2019 desde a primeira visualização e têm vindo a dar-nos feedback a cada passo do caminho.”

Dentre as edições existentes, a Microsoft desta a Community que manteve presente e passou por diversas melhorias neste nova versão: “Para o Visual Studio Community 2019, estamos fazendo as referências, Application insights (solicitações e exceções), e teste (status de teste e testado por) CodeLens recursos disponíveis, que só estavam disponíveis no Visual Studio Professional e Enterprise Anteriormente.


Isso significa que qualquer usuário do Visual Studio 2019 pode agora obter insights específicos e informações sobre o código, diretamente no editor de código.”

Quer saber mais sobre o Visual Studio 2019? Confira as notas de lançamento disponíveis para esta primeira release candidate e seus detalhes.

Caso deseje realizar o download acesse: https://visualstudio.microsoft.com/downloads/

Fontes e Direitos Autorais: Microsoft – Visual Studio – John Montgomery  – 28/02/2019.
Leia na integra acessando: https://devblogs.microsoft.com/visualstudio/visual-studio-2019-release-candidate-rc-now-available/

#24 – Para que serve


Olá, bom dia, que bom te ver por aqui, neste sabadão, estamos entrando na semana que antecede a maior festa do Brasil.

Seja bem-vindo a mais um post da sessão Para que Serve, sendo este o de número 24, mais um dia de muito trabalhado, repleto de atividades e compromissos.

No post de hoje, vou compartilhar com você que esta acessando meu blog, um dos mais tradicionais recursos existentes no Microsoft SQL Server introduzido desde as versões iniciais do produto, mantendo-se sempre atualizada, úteis e principalmente apresentando um pouco perigoso ao ser utilizada quando não se conhece.

De uma forma bem diferente e mais direta, não vou realizar suspense ou criar qualquer clima de expectativa sobre este tema, na verdade estou me referindo ao uso das Table Hints (dicas de tabelas), com certeza você já deve ter ouvido falar ou já utilizou pelo menos uma delas, adianto que neste post não vou falar da mais conhecida a NoLock e sim de outras duas que também são importantes.

Sendo assim, chegou a hora de conhecer um pouco mais sobre o post de número 24 da sessão Para que serve. Mas uma vez, bem vindo ao #24 – Para que serve – Table Hint – UpdLock e TabLock.

Espero que você esteja animado para conhecer um pouco mais sobre estas funcionalidades, caso já conheça, continue lendo este post, sempre podemos aprender algo novo….


Introdução

As dicas de tabela tem um papel muito importante a partir do momento que decidimos fazer uso delas, alterar o comportamento padrão do otimizador de consulta durante a instrução DML (linguagem de manipulação de dados) ao especificar um método de bloqueio, um ou mais índices, uma operação de processamento de consulta, como uma verificação de tabela ou busca de índice, ou outras opções. As dicas da tabela são especificadas na cláusula FROM da instrução DML e afetam apenas a tabela ou exibição referenciada nessa cláusula.

Mesmo sabendo que o otimizador de consulta do SQL Server normalmente seleciona o melhor plano de execução para uma consulta, muitos desenvolvedores e administradores de banco de dados tomam a decisão de fazer uso deste recurso em diversos cenários de forma indiscriminada.

As dicas de tabelas podem ao longo de sua utilização impactar de forma direta na maneira que o otimizador de consultas existente no Microsoft SQL Server trabalha desde o momento da identificação da análise da query e criação do seu plano de execução, mas principalmente no momento da própria execução.

Vale ressaltar que as table hints serão ignoradas se a tabela não for acessada pelo plano de consulta, isso pode ser provocado porque o query optimizer opta por não acessar a tabela ou porque uma exibição indexada é acessada, mas este cenário também pode ser contornado, quem sabe em outro momento.

Vamos em frente, sabendo que estas funcionalidades precisam ser um pouco mais conhecidas e melhor entendidas, tomei a decisão de elaborar este post, destacando as duas dicas de tabela que atualmente acabei me deparando em cenários de consultoria, me refiro a:

·         UpdLock; e

·         Tablock.

Desta forma, vou apresentar uma breve descrição destas duas dicas de tabelas, e posteriormente utilizar um simples cenário prático para demonstrar como podemos fazer uso de ambas, começando pela UpdLock.

Table Hint – UpdLock

Sendo está uma das mais antigas dicas de tabela existente no Microsoft SQL Server, reconhecida como uma table hint exclusiva para seu uso no comando update, o que na verdade não se aplicada, mesmo sendo este o cenário mais comum de uso podemos em determinados momentos aplicar a instruções Select e Insert.

Seu papel é especificar que bloqueios de atualização serão usados e mantidos até que a transação seja concluída. UpdLock utiliza bloqueios de atualização apenas em operações de leitura no nível de linha ou de página.

Caso o UpdLock venha a combinado com TabLock, ou se um bloqueio em nível de tabela for usado por outro motivo, um bloqueio exclusivo reconhecido pela sigla (x) será usado.

Importante destacar que sempre que um UpdLock é especificado, as dicas em nível de isolamento ReadCommitted e ReadCommittedLock são ignoradas, sendo assim, podemos em alguns cenários se deparar com a ocorrência de dados fantasmas.

Agora que conhecemos um pouco mais sobre esta table hint, vamos avançar, conhecer seu funcionamento de forma prática, para tal, utilizaremos como base o Bloco de Código 1, que realizará a criação do seguinte ambiente:

·         Databases: TesteDatabaseTableHintUpdLock;

·         Tables: UpdLockTable; e

·         Transações: TUPD e TUPDII.

— Bloco de Código 1 —
— Criando o Banco de Dados – TesteDatabaseTableHintUpdLock —
Create Database TesteDatabaseTableHintUpdLock
Go

— Acessando o Banco de Dados – TesteDatabaseTableHintUpdLock —
Use TesteDatabaseTableHintUpdLock
Go

 — Criando a Tabela – UpdLockTable —
Create Table UpdLockTable
(Codigo Int Identity(1,1) Not Null Primary Key Clustered,
Valores Varchar(20))
Go

 

Até aqui nada de especial, nosso ambiente já está criado e pronto para receber alguns dados, para tal, utilizaremos o Bloco de Código 2, responsável em realizar o insert e consulta dos dados, conforme apresento a seguir:

 

— Bloco de Código 2 —
— Inserindo uma pequena porção de dados —
Insert Into UpdLockTable
Values (‘Pedro’), (‘Antonio’), (‘Galvão’), (‘Junior’),
(‘MVP’), (‘MCC’), (‘MSTC’), (‘MIE’), (‘SQL Server’),
(‘Banco de Dados’),(‘Table Hint UpdLock’)
Go
— Consultando os dados —
Select Codigo, Valores From UpdLockTable
Order By Valores Desc
Go

A partir deste momento temos uma porção de dados armazenados em nossa table UpdLockTable, o que nos permite realizar uma pequena simulação de seu comportamento e entender como a ela realiza o bloqueio em nossa query.

Vamos então realizar a execução do Bloco de Código 3, sendo este o ponto chave que vai nortear nosso entendimento, observe que estamos abrindo um bloco transacional denominado TUPD (conforme já destacado anteriormente).

— Bloco de Código 3 —
— Iniciando um novo Bloco de Transações denominada TUPD —
Begin Transaction TUPD

 

— Realizando o Update —

Update UpdLockTable
Set Valores = ‘BD’
Where Codigo = 10
Go

Neste instante o Microsoft SQL Server em conjunto com alguns de seus mais importantes componentes: Database Engine e Storage Engine, estabelecem uma nova transação que deverá ser processada dentro da sessão que estamos trabalhando mais que a mesma não recebeu até o presente momento nenhuma instrução Commit ou Rollback.

No próximo passo a ser realizado aparentemente pode parecer bastante simples, mas após sua execução você vai entender que nem sempre é assim que as coisas podem ser interpretadas.

 

Seguindo em frente, vamos executar o Bloco de Código 4, o qual vai nos exigir abrir uma nova query dentro da ferramenta que você se encontra acessando o Microsoft SQL Server no momento, eu como de costume estou utilizando o bom e velho Management Studio.

— Bloco de Código 4 —

Select Codigo, Valores From UpdLockTable
Order By Valores Desc
Go

Acredito que você deva ter feito a execução deste simples bloco de código e até o presente momento o Microsoft SQL Server ainda não te retornou nenhum dado. Não é verdade?


A Figura 1 pode te ajudar a comprovar e responder este meu questionamento:

Figura 1 – Execução do bloco de código 4 sendo realizada e nenhum dado retornado em tela.

Viu, nem tudo que parece ser fácil realmente é! Mas o que pode ter acontecido?

 

Na verdade, o SQL Server está trabalhando da forma correta, realizamos o processamento de um comando Update dentro de um bloco de transações que ainda não foi encerrado e internamente o bloqueio da tabela foi aplicada de forma geral o que impede que quaisquer outras transações que venha a acessar a tabela como um todo consiga.

 

Mas se você observou em nosso Bloco de Código 3 não fizemos uso da table hint UpdLock, é justamente este o ponto chave, a partir do momento em que fazemos uso desta dica de tabela teremos a capacidade de acessar nossos dados mesmo que estes se encontrem dentro de outra transação ou bloqueio.

 

Para confirmar esta mudança de comportamento, vamos executar agora o Bloco de Código 5 e logo na sequência abrir uma nova query e observar o resultado apresentado na Figura 2:

 

— Bloco de Código 5 —

RollBack Transaction TUPD — Encerrando a transação anterior

Go

 

 

— Realizar novamente o Update agora com UpdLock e Abrir nova Sessão —
Begin Transaction TUPDII

 

Update UpdLockTable With (UpdLock)
Set Valores = ‘Forçando UpdLock’
Where Codigo = 11
Go

Figura 2 – Select realizado em outra query mesmo com a transação TUPDII ainda não encerrada.

Show, não é verdade, neste momento o Microsoft SQL Server acaba de retornar nossos dados manipulados na tabela UpdLock a qual se encontra pertencente a um bloqueio de atualizações de dados que pode estar sendo aplicado no nível de linha ou página de dados (mas este comportamento e estudo vou deixar para outro momento).

 

Com isso, conhecemos a table hint UpdLock, ainda não terminamos nossa caminhada, podemos evoluir mais um pouco nesta jornada, conhecendo agora a dica de tabela TabLock na sequência.

 

Table Hint – TabLock

Também uma das mais tradicionais dicas de tabela, e durante minha pesquisa e entendimento sobre esta table hint, cheguei a conclusão que a ela é muito misteriosa, devido ao seu comportamento.

Aonde a mesma tem a função de especificar que o bloqueio adquirido seja aplicado no nível de tabela, o tipo de bloqueio que é adquirido depende da instrução que está sendo executada.

Por exemplo:

1.      Uma instrução Select pode adquirir um bloqueio compartilhado. Ao especificar TABLOCK, o bloqueio compartilhado é aplicado à tabela inteira, e não no nível de linha ou página. Se HoldLock também for especificado, o bloqueio de tabela será mantido até o final da transação; e

2.      Ou então quando usado com o provedor de conjuntos de linhas em massa através do uso de uma instrução OPENROWSET para importar dados em uma tabela, TabLock permite que vários clientes carreguem dados simultaneamente na tabela de destino com o registro em log e o bloqueio otimizados.

Estamos preparados para seguir em frente, conhecemos um pouco mais sobre esta table hint, chegou a hora de conhecer o seu funcionamento de forma prática, para tal, utilizaremos como base o Bloco de Código 6, que realizará a criação do seguinte ambiente:

·         Databases: TesteDatabaseTableHintTabLock;

·         Tables: TabLockTable; e

·         Transações: TTBL e TTBLII.

— Bloco de Código 6 —
— Criando o Banco de Dados – TesteDatabaseTableHintTabLock —
Create Database TesteDatabaseTableHintTabLock
Go

— Acessando o Banco de Dados – TesteDatabaseTableHintTabLock —
Use TesteDatabaseTableHintTabLock
Go

 

— Criando a Tabela – TabLockTable —
Create Table TabLockTable
(Codigo Int Identity(1,1) Not Null Primary Key Clustered,
Valores Varchar(20))
Go

 

Até aqui nada de especial, nosso ambiente foi criado, preparado para receber alguns dados, para tal, utilizaremos o Bloco de Código 7, responsável em realizar o insert e consulta dos dados, conforme apresento a seguir:

 

— Bloco de Código 7 —

— Inserindo uma pequena porção de dados —
Insert Into  UpdLockTable
Values (‘Pedro’), (‘Antonio’), (‘Galvão’), (‘Junior’),
(‘MVP’), (‘MCC’), (‘MSTC’), (‘MIE’), (‘SQL Server’),
(‘Banco de Dados’),(‘Table Hint TabLock’)
Go

 

— Consultando os dados —
Select Codigo, Valores From UpdLockTable
Order By Valores Desc

Go

 

Nossa porção de dados encontra-se armazenados em nossa table TabLockTable, o que nos permite realizar uma pequena simulação de seu comportamento e entender como ela realiza o bloqueio em nossa query.

 

Vamos então realizar a execução do Bloco de Código 8, sendo este o ponto chave que vai nortear nosso entendimento, observe que estamos abrindo um bloco transacional denominado TTBL (conforme já destacado anteriormente).

 

— Bloco de Código 8 —

— Iniciando um novo Bloco de Transações denominada TTBL —

Begin Transaction TTBL

 

 

— Utilizando o TabLock —
Begin Transaction TTBL

 

Declare @Contador TinyInt = 1

 

Select ‘Início…’

 

While @Contador < 255
BeginUpdate TabLockTable
Set Valores = @Contador
Where Codigo = 10

 

Select Valores From TabLockTable
Where Codigo = 10

 

Set @Contador += 1
End

 

Select ‘Fim…’

Go

 

Observação: Você deve ter notado um pouco de lentidão na execução de bloco de código, bem como, o uso de alguns comandos e técnicas que podem ser melhoradas ou até mesmo substituídas, ressalto que o mesmo não possui o objetivo de ser executado no menor tempo possível ou até mesmo ser entendimento como uma técnica para inserção de dados. Este bloco de código foi criado e elaborado somente para esta prática, sem qualquer finalidade de uso em cenários reais.

 

Dando continuidade, enquanto nosso Bloco de Código 8 é processado, vamos abrir uma nossa query e executar o Bloco de Código 9 e observar se algo de errado ou diferente acontece:

 

— Bloco de Código 9 —
— Inserindo uma nova linha de registró lógico na TableTableLock durante o processamento do bloco de código 8 —

Insert Into TabLockTable Values (‘Teste TabLock’)
Go

 

Tenho a certeza que você imaginava que o SQL Server iria demorar para processar nossa query ou simplesmente não iria retornar nada em tela, da mesma forma que a UpdLock.

Não!

 

Ele mais uma vez está trabalhando de forma certa, da mesma maneira que fizemos anteriormente não utilizamos de forma proposital a table hint TabLock em nosso bloco de código, algo que vamos realizar agora utilizando os blocos de código 10 e 11, declarados a seguir:

 

— Bloco de Código 10 —

Rollback Transaction TTBL — Encerrando a transação anterior —

Go

 

 

— Adicionar TabLock —
Begin Transaction TTBLII

 

Declare @Contador TinyInt = 1

 

Select ‘Início…’

While @Contador < 255
BeginUpdate TabLockTable With (TabLock)
Set Valores = @Contador
Where Codigo = 10

 

Select Valores From TabLockTable
Where Codigo = 10

Set @Contador += 1
End

 

Select ‘Fim…’
Go

 

— Bloco de Código 11 —

— Abrir nova query e realizar Insert vai ocorrer bloqueio —
Insert Into TabLockTable Values (‘Teste TabLock II’)
Go

 

Para nossa surpresa, o Bloco de Código 10 já deve ter sido processado, mas o 11 ainda se encontra em processamento, a Figura 3 apresentada abaixo, comprova este comportamento:

Figura 3 – Bloco de código 11 ainda em execução, aguardando algum retorno do bloco de código 10, mesmo após sua execução.

Você pode estar questionamento a si mesmo, o porquê deste comportamento por parte do nosso Microsoft SQL Server. Não precisa ficar preocupado, por padrão o Database Engine em conjunto com o Storage Engine estão aplicando em tempo de execução um bloqueio em nossa tabela TabLockTable, ou seja, a mesma ficou bloqueada pela operação de Update que nos disparamos no bloco de código 10 em conjunto table hint TabLock.

 

Para que possamos concluir a execução do Bloco de Código 11, precisamos encerrar nossa transação aberta anteriormente denominada TTBLII, para tal vamos executar o Bloco de Código 12 dentro da mesma query utilizada para executar o Bloco de Código 10:

 

— Bloco de Código 12 —

— Confirmando e encerrando a transação TTBLII dentro da mesma sessão do bloco de código 10 —

Commit Transaction TTBLII
Go

 

E como um passe de mágica, o Bloco de Código 11 foi encerrado simultaneamente após o processamento do Bloco de Código 12.

Vou deixar para que você mesmo comprove este resultado.

 

Não é algo realmente surpreendente, como a mesma funcionalidade utilizada de formas similares ou parecidas possa ser comportar de maneiras tão diferentes.

 

Eu sou suspeito a dizer, mas não podemos dizer que isso é um passe de mágicas ou truque, ao contrário, existe um conjunto de propriedades relacionadas ao controle de transacional e bloqueios que os Sistemas Gerenciadores de Bancos de Dados utilizam para garantir que tudo funcione da melhor forma possível chamado ACID:

 

  • Atomicidade;
  • Consistência;
  • Isolamento; e
  • Durabilidade.

 

Quem sabe em um outro post eu aborde este tema, por hoje, vou ficando por aqui.

Com isso, e sem mais delongas, chegamos ao final. Ufa deu um pouco de trabalho este post, como de costume, mesmo assim sempre vale a pena poder compartilhar um pouco do conhecimento e experiências adquiridas ao longo dos anos de trabalho como DBA e Professor.

Espero que você tenha gostado, eu posso dizer que sim, mas sua opinião é muito importante.


Referências

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

http://sqlissuessa.blogspot.com/2016/07/table-hints-transact-sql.html#!

https://sqlhelp.wordpress.com/2009/01/13/table-hints-transact-sql/

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d2e78faa-9c79-4faf-8232-5ae08a5dd23e/sql-table-hints-not-working-updlock-holdlock

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a7536fc4-ed1e-44aa-a16b-d4dc9ca6cd70/what-is-the-difference-between-holdlock-and-updlock-in-sql-server

https://www.sqlservercentral.com/Forums/Topic1227171-392-1.aspx

http://www.sql-server-performance.com/2004/2000-table-hints/

https://pedrogalvaojunior.wordpress.com/2018/03/01/script-challenge-13-a-resposta/

https://pedrogalvaojunior.wordpress.com/tag/trace-flag/

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/2018/12/21/23-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/08/22/22-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/05/28/21-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/04/12/20-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/01/02/19-para-que-serve/

Conclusão

Mais uma vez podemos observar como o Microsoft SQL Server apresenta um conjunto grandioso de funcionalidades, aplicando formas diferentes e especificas para cenários simulares em suas estruturas de código, mas que durante sua execução pode ser entendida de uma maneira muito particular.

Neste post podemos conhecer, aprender e visualizar como as table hints UpdLock e TabLock são utilizadas, seus comportamentos, e principalmente como os bloqueios realizados por ambas podem ser aplicar para um mesmo cenário no caso o uso do comando Update dentro de um bloco de transações.

Este é o fantástico Microsoft SQL Server, que desde suas primeiras versões nos apresenta inúmeras possibilidades de se aprender, possibilitando usar o passado como fonte de inspiração para construção de um futuro melhor, por isso que a cada dia eu me apaixono ainda mais por este produto…

Vai SQL Server, Vai SQL Server….

Agradecimentos

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 a ser publicado no mês maio de 2019.

Um grande abraço e ótimo final de semana.

Valeu.

Script Challenge – 2019 – Post 15


Olá, bom dia comunidade…

Tudo bem? Quarta – feira dia 13/02 estamos no verão, sofrendo muito com este super calor que esta “queimando nossas cacholas”, mas hoje aqui em São Roque, amanheceu um dia bem diferente dos últimos, muito chuva, vento e temperaturas bem amenas, podemos dizer que aquele friozinho gostoso esta de volta.

Pois bem, sou suspeito a dizer sobre o frio, ainda mais por ser fã incondicional de temperatura baixas, devido a ter trabalhado muitos anos dentro de os mais variados datacenters de empresas e clientes suportando “aquelas” altas temperaturas de 16 ou 18 graus.

Mas o que esta mudança de tempo tem haver com este novo post da sessão Script Chalenge, bom na minha opinião tem tudo haver, ainda mais dependendo do situação e como diria um velho amigo meu “do nível da problemática da coisa” ele poderá aumentar ou diminuir a sua temperatura em pouso segundo, kkkkkk.

Introdução

Os posts publicados nesta sessão tem o objetivo de desafiar o visitante a descobrir o que um determinado script pode fazer ao ser executado, e não somente isso, mostrar como podemos aprender com o uso da linguagem Transact-SQL e sua vasta coleção de comandos, funções e instruções adicionadas a cada nova versão ou atualização do Microsoft SQL Server.

Se você ainda não conhecia a sessão Script Challenge, fique tranquilo vai ter a possibilidade agora mesmo, como também a cada 4 meses poder desfrutar de um novo desafio e sua respectiva resposta, por isso esta sessão é denominada Script Challenge(Script Desafio ou Desafio do Script), bom a melhor forma de traduzir eu deixo para você escolher.

Seguindo um frente….

Gostaria de destacar mais uma vez duas mudanças iniciais implementadas na sessão Script Challenge tendo como base o post publicado em outubro de 2017, sendo elas:

  1. Como maneira ou forma de dificultar ainda mais o nível de complexidade do código, o bloco de código que representa o Script Challenge selecionado para compor o post será  apresentado no formato de imagem ou figura; e
  2. Algumas partes das linhas de código, trechos, ou partes de código serão omitidas, justamente como forma de tornar este código ainda mais desafiador.

Antes de destacar o script de hoje, não custa nada saber um pouco mais sobre esta sessão, por este motivo, quero lhe contar um pouco da história que cerca os posts relacionadas a ela…

Um pouco de História

Esta é uma sessão criado no final do ano de 2010 e mantida mensalmente até meados de 2012, desde então não me dediquei mais a mesma devido principalmente em não conseguir identificar possíveis scripts dentro da minha biblioteca que poderiam ser apresentados como um scripts considerados como desafio para os profissionais da área de banco de dados.

Mas acredito que nos últimos anos com o crescimento do Microsoft SQL Server, seu nível de evolução, amadurecimento e recursos adicionais, como também, meu nível de experiência profissional e acadêmica também evolui muito, sinto-me preparado para retornar com esta sessão e poder de forma bastante técnica, didática e conceitual poder lançar um “desafio” e responder o mesmo com muito segurança.

Outro detalhe importante, esta é uma sessão que foi criada, pensando em apresentar pequenos blocos de códigos oriundos da linguagem Transact-SQL que possam apresentar um nível de dificuldade e conhecimento técnico um pouco mais elevado, exigindo assim uma análise mais profunda e até mesmo a criação um pequeno cenário de teste para seu entendimento.

Vamos então conhecer o bloco de código selecionado para ser o Script Challenge deste post.


Script Challenge – 15

O bloco de código Transact-SQL selecionado para fazer parte do Script Challenge de hoje, esta relacionado com uma importante atividade exercidas por Administradores de Servidores,  DBAs e Administradores de Banco de Dados.

Trata-se de algo que pode ajudar a identificar possíveis situações que venham a atrapalhar o processamento e execução de nossas querys e transações. Vale ressaltar que este script, não possui a finalidade de apresentar os custos de processamento que estão sendo demandados pelos processos em execução.

Vou dar uma pequena canja, sobre este desafio: O mesmo, pode ser executado em conjunto com as demais sessões, transações ou querys em execução, sem necessitar que qualquer procedimento de manutenção ou alterações na configuração do SQL Server para sua execução.

E ai, ficou curioso para saber o que este script realiza? Qual seria esta possível atividade? Mesmo assim, não vou lhe responder hoje, vou deixar que você quebre um pouco a sua cabeça, afim de tentar matar esta curiosidade, mas como não sou tão mal assim, no final do post como de costume vou deixar uma enquete para você expor sua opinião.

A seguir apresento o bloco de código:

Figura 1 – Short Script 15.

Muito bem, nosso Script Challenge esta apresentado. Você preparado para mais este desafio?

Tenho a certeza que sim!


Sua participação

Legal, agora quero conter com a sua participação, me ajudando a responder este post, outra grande novidade que estou adicionando aos posts da sessão Script Challenge será uma pequena enquete com possíveis respostas para identificar qual é a correta, relacionada com o desafio apresentado através do bloco de código selecionado para este post, desta forma, apresento a seguir a Enquete – Script Challenge – 15 e suas alternativas de resposta:

Seguindo em frente, já conhecemos o Script Challenge selecionado, sua enquete e relação de opções de resposta, fica faltando somente a sua participação, algo que eu tenho a certeza que vai acontecer rapidamente.

Durante quanto tempo posso participar…

Outra grande mudança na sessão Script Challenge é seu tempo de publicação “validade”, anteriormente os posts desta sessão eram publicados mensalmente ou bimestralmente, agora serão publicados em um período mais longo, ou seja, a cada 4 meses estarei compartilhando com vocês novos posts dedicados exclusivamente para esta sessão.

Desta forma, você terá mais tempo para participar e me ajudar e encontrar a resposta correta para este desafio, enviando suas possíveis sugestões, críticas e até mesmo alternativas de resposta para minha enquete.

Posts anteriores

Caso esta seja a primeira vez que você acessa um post desta sessão, fico muito feliz e aproveito para compartilhar os posts mais recentes:

https://pedrogalvaojunior.wordpress.com/2018/10/06/script-challenge-14-a-resposta/

https://pedrogalvaojunior.wordpress.com/2018/06/29/script-challenge-2018-post-14/

https://pedrogalvaojunior.wordpress.com/2017/10/26/script-challenge-2017-o-retorno/

https://pedrogalvaojunior.wordpress.com/2018/03/01/script-challenge-13-a-resposta/


Agradecimentos

Obrigado por sua visita, espero que post apresentado como um possível “desafio” possa 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, ou são tratados com “bichos de sete cabeças”.

Um forte abraço nos encontramos em breve nas demais sessões e especialmente em junho de 2019 em mais um post da sessão Script Challenge, o qual apresentará a resposta para este post.

Sucesso, mais uma vez obrigado por sua visita, espero que você tenha gostado deste post.

Fique a vontade para conhecer demais publicados até o presente momento nas demais sessões.

Abraços.

Material de Apoio – Fevereiro 2019


Olá, muito bom dia….

Tudo bem? O mês de fevereiro já chegou, ainda não é carnaval no Brasil, mas para alegria e folia da garotada e de nós professores as aulas estão de volta (kkkkk).

Mesmo com toda esta loucura de volta as aulas, estou aqui mais uma vez procurando colaborar e compartilhar com a comunidade técnica em mais um post da sessão Material de Apoio dedicado exclusivamente ao meu blog.

Espero que você esteja gostando do conteúdo aqui disponibilizado, como também, possa me ajudar a torná-lo ainda melhor no decorrer do tempo com a sua participação.

O post de hoje

Seja bem-vindo a mais um post da sessão Material de Apoio, sendo o primeiro post da sessão em 2019 e de número 161 no total da mesma.

Para aqueles que já acompanham o meu blog a um certo tempo, os posts dedicados a sessão Material de Apoio, possuem o objetivo de compartilhar o conhecimento de recursos, funcionalidades e procedimentos que podemos realizar no Microsoft SQL Server.

Hoje não será diferente, estou trazendo alguns dos mais recentes scripts catalogados nos últimos meses, que atualmente estão compondo a minha galeria de códigos formada ao longo dos anos de trabalho como DBA e atualmente como Professor de Banco de Dados.

Neste post você vai encontrar arquivos relacionados com os seguintes temas:

  • Check List Diário;
  • Concatenar valores ;
  • Criptografia de Código Fonte;
  • Descriptografia de Código Fonte;
  • Disponibilidade de Ambiente;
  • DMV sys.dm_exec_procedure_stats;
  • DMV sys.dm_exec_query_plan;
  • Formatação de Valores;
  • Função Format;
  • Função Parse;
  • Ordem Crescente de dados;
  • Ordem Descrecente de dados;
  • Plano de Execução;
  • Querys;
  • Stored Procedure;
  • Stored Procedure sp_fixeddrivers; e
  • Texto.

Espero que este conteúdo possa lhe ajudar em seus atividades profissionais e acadêmicas. Por questões de compatibilidade com a plataforma WordPress.com, todos os arquivos estão renomeados com a extensão .docx ao final do seu respectivo nome, sendo assim, após o download torna-se necessário remover esta extensão, mantendo somente a extensão padrão .sql.

Material de Apoio

A seguir apresento a relação de arquivos  selecionados:

1 – Material de Apoio – Fevereiro 2019 – Relação das Querys mais pesadas em conjunto com plano de execução

2 – Material de Apoio – Fevereiro 2019 – Stored Procedure – CheckList Diário – Disponibilidade e Ambiente

3 – Material de Apoio – Fevereiro 2019 – Stored Procedure – sp_fixeddrives

4 – Material de Apoio – Fevereiro 2019 – Stored Procedure para Descriptografia de Stored Procedure

5 – Material de Apoio – Fevereiro 2019 – Stored Procedure – Exibir – Código Stored Procedure Criptografada

6 – Material de Apoio – Fevereiro 2019 – PARSE e FORMAT para alterar exibição de valores

7 – Material de Apoio – Fevereiro 2019 – Função – Formatação de Valores

8 – Material de Apoio – Fevereiro 2019 – Função – Concatenar valores em ordem decrescente

9 – Material de Apoio – Fevereiro 2019 – Função – Contar caracteres específicos dentro de um texto

10 – Material de Apoio – Fevereiro 2019 – Plano de Execução – Obtendo informações sobre Querys e Stored Procedures – sys.dm_exec_procedure_stats + sys.dm_exec_query_plan

Fique a vontade para copiar, editar, compartilhar e distribuir estes arquivos com seus contatos, aproveite se possível deixe seu comentário, críticas, sugestões e observações.

Nota: Todos os arquivos disponibilizados foram obtidos ou criados com autorização de seus autores, sendo estes, passíveis de direitos autorais.

Links

Caso você queira acessar os posts anteriores da sessão, não perca tempo utilize os links listados abaixo:

https://pedrogalvaojunior.wordpress.com/2018/12/18/material-de-apoio-dezembro-2018/

https://pedrogalvaojunior.wordpress.com/2018/10/30/material-de-apoio-outubro-2018/

https://pedrogalvaojunior.wordpress.com/2018/08/14/material-de-apoio-agosto-2018/

https://pedrogalvaojunior.wordpress.com/2018/06/19/material-de-apoio-junho-2018/

https://pedrogalvaojunior.wordpress.com/2018/04/05/material-de-apoio-abril-2018/

Agradecimento

Quero agradecer imensamente a sua visita, sinto-me honrado e orgulhoso de contar com a sua presença.

Não deixe de acessar os outros posts das demais sessões, o próximo post desta sessão será publicado no mês de abril, até lá, continue aproveitando cada momento da sua vida, desfrutando com muita sabedoria os momentos de galeria e também os desafios que são colocados ao seu redor.

Um forte abraço, muita saúde, sucesso e nos encontramos em breve.

Valeu.

Dica do Mês – Temporal Table e o Calor, uma combinação muito quente


Salve pessoal, bom dia.

Estamos no mês de janeiro, férias, sol, calor, chuvas, 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 uma das funcionalidades adicionadas ao Microsoft SQL Server a partir da versão 2016 e que recentemente acabei conhecendo com um pouco mais, como você já pode notar no título deste post, estou fazendo referência as chamadas Temporal Tables (Tabelas Temporais).

Você já conhece? Teve a necessidade de utilizar? Eu particularmente falando conhecia muito pouco sobre este recurso, mas na semana passada neste período de férias tive a ideia de fazer uma brincadeira aqui em casa em conjunto com um termômetro, e justamente através desta brincadeira que utilizei uma temporal table.

Ficou curioso para saber como eu fiz uso dela? Calma, daqui a pouco eu conto mais sobre isso para você.

Pois bem, sem mais delongas, 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 – Temporal Table e o Calor, uma combinação muito quente.


Introdução

A partir da versão 2016 do Microsoft SQL Server, a Microsoft introduziu o suporte para tabelas temporais de sistema baseadas no versionamento de dados como um recurso de banco de dados, sendo este,  uma funcionalidade que traz o suporte interno para fornecer informações sobre dados armazenados na tabela em qualquer ponto no tempo, ao invés de apenas os dados que é corretos no momento atual em está na hora.

Esta nova funcionalidade, também é reconhecida e trata como um recurso de banco de dados criado com base nos padrões em ANSI SQL 2011.

A partir do momento em que idealizamos fazer uso de uma tabela temporal, estamos criando um novo objeto ou transformando um objeto já existente em nosso banco de dados, em um elemento responsável em manter o histórico completo das alterações de dados ocorridos durante um período de tempo, sendo esta a principal finalidade de uso de uma temporal que é tratada internamento como um repositório de gerenciamento de tempo.

Cada tabela temporal tem duas colunas explicitamente definidas, cada um com um tipo de dados datetime2 , estas colunas são referidas como colunas de período, sendo período colunas usadas exclusivamente pelo sistema de registro prazo de validade para cada linha, sempre que uma linha for modificada. Além dessas colunas de período, uma tabela temporal também contém uma referência a outra tabela, a qual será utilizada como esquema espelho.

Por padrão o Microsoft SQL Server utiliza esta tabela para armazenar automaticamente a versão anterior de uma linha cada vez que a mesma na tabela temporal é atualizada ou excluída. Esta tabela adicional é referida como a tabela de histórico, enquanto a tabela principal que armazena versões de linha (real) atual é conhecida como a tabela atual ou simplesmente como a tabela temporal.

Importante ressaltar que durante a criação do quadro temporal, os usuários podem especificar a existência de uma tabela de histórico (deve ser esquema compatível) ou deixar o sistema criar tabela de histórico padrão.

Agora que já conhecemos um pouco do que é uma Temporal Table, vamos avançar mais um pouco em nossa caminhada, vou apresentar o porque tive a ideia de fazer uso deste recurso.

 

SEU FUNCIONAMENTO

Como já destacado anteriormente o sistema de controle de versão de uma tabela temporal é implementado através do uso de um par de tabelas, uma tabela atual e uma tabela de histórico. Dentro de cada uma destas tabelas, as seguintes duas colunas adicionais datetime2 são usadas para definir o período de validade para cada linha:

  • Coluna de início de período: O sistema registra a hora de início para a linha nesta coluna, denotado tipicamente como a coluna de SysStartTime .
  • Coluna de fim do período: O sistema registra a hora final para a linha nesta coluna, normalmente indicado na coluna SysEndTime .

A tabela atual contém o valor atual para cada linha. A tabela de histórico contém cada valor anterior para cada linha, se for o caso, e a hora de início e hora de término para o período para o qual foi válido.

A Figura 1 apresentada abaixo, ilustra de forma simples o funcionamento do sistema de controle dos dados aplicado a partir do uso de uma tabela temporal:

Temporal-HowWorks

Figura 1 – Funcionamento do sistema de controle de uma tabela temporal.

Este sistema de controle de versionamento dos dados é realizado sempre as instruções: Insert, Update, Delete ou Merge venham a ser realizadas de forma individual ou simultânea.

 

PORQUE UTILIZAR UMA TEMPORAL TABLE

Uma das coisas que eu aprendi a gostar no decorrer da minha carreira na área de tecnologia é a importância e as possibilidades de mudanças que um mesmo dado pode apresentar no decorrer de um período de tempo, este é um dos meus maiores prazeres entender o quanto aquele dado a uma minuto atrás agora já é outro dado e podem me trazer representar novas informações e conhecimentos.

Desta forma, ao analisarmos uma temporal table podemos também reconhecer ou fazer uso da mesma como uma Slowly Changing Dimension (Dimensão com mudanças lentas ou mudanças lentas em uma dimensão), o que vai nos possibilitar criar uma visão dos nossos dados com base uma período ou determinada data.

Uma outra funcionalidade que pode ser aplicada a uma temporal table se relacionada a controles de auditoria mais propriamente falando de auditoria de dados, normalmente as fontes de dados reais são dinâmicas e se tornam voláteis ao longo do tempo, para uma empresas isso pode influenciar diretamente em suas decisões as quais dependem de percepções que os analistas podem começar a identificar a partir da evolução ou mudanças de dados.

Já sabemos o porque escolhi fazer uso de uma temporal table, agora vou apresentar o cenário que me permitiu aplicar este recurso com base na minha ideia.

 

MINHA IDEIA

Estamos visando uma forte onda de calor em praticamente todo o Brasil, algo que muitos brasileiros adoram eu sinceramente não sou um destes brasileiros, pois eu não suporto estas altas temperaturas.

Para tentar de alguma maneira aprender algo de novo com este calor e tentando se distrair dentro das possibilidades, pensei em ter uma noção do quanto a temperatura aqui na minha casa localizada na cidade de São Roque interior do estado de São Paulo muda no decorrer de um período de tempo, sendo justamente esta a minha ideia de utilizar uma temporal table, talvez esta não tenha sido a melhor ideia ou até mesmo o melhor cenário para uso, mas entendo que pode ser uma possibilidade dentre as mais variadas possíveis.

Seguindo em frente e avançando mais um pouco, chegou a hora de colocar em prática a minha ideia, para isso vamos construir um simples cenário para fazer uso da Temporal Table.

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: DatabaseTemporalTabel;
  • Tabela Atual: TemporalTableTemperatura;
  • Tabela Historico: TemporalTableTemperaturaHistorico;
  • Colunas Temporais: DataHoraInicial e DataHoraFinal; e
  • Period For System formado por: DataHoraInicial e DataHoraFinal.

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 DatabaseTemporalTable
Go

— Acessando o Banco de Dados —
Use DatabaseTemporalTable
Go

— Criando a Tabela TemporalTableTemperatura —
Create Table TemporalTableTemperatura
(Codigo Int Identity(1,1) Primary Key Clustered,
Local Char(10) Default ‘Minha Casa’,
Cidade Char(9) Default ‘São Roque’,
DataAtual Date Default GetDate(),
HoraAtual Time Default GetDate(),
Temperatura TinyInt,
DataHoraInicial Datetime2 (0) GENERATED ALWAYS AS ROW START,
DataHoraFinal Datetime2 (0) GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (DataHoraInicial, DataHoraFinal))
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalTableTemperaturaHistorico))
Go

A Figura 2 apresentada abaixo, ilustra a estrutura da tabela TemporalTableTemperatura e sua tabela espelho TemporalTableTemperaturaHistorico:

Figura 2 – Tabelas TemporalTableTemperatura e TemporalTableTemperaturaHistorico.

Observações

1 – Para que o Microsoft SQL Server reconheça uma tabela como Temporal Table as colunas temporais devem ser formadas pelo tipo de dados DateTime2 e logo após a declaração do seu tipo de dados informar as instruções:

  • Generated Always as Row Start – Valor gerado sempre no início da linha; e
  • Generated Always as Row End – Valor gerado sempre no final da linha.

2 – O controle do período dos valores é feito através da instrução PERIOD FOR SYSTEM_TIME, declarada obrigatoriamente no final da construção da tabela, formada pelas colunas que recebem os valores DateTime2.

3 – Ao declarar o nome da tabela a ser utilizada para o versionamento dos dados, é obrigatório informar o nome do ower ou schema a qual esta tabela irá pertencer, caso isso não seja feito o Microsoft SQL Server retornará a seguinte mensagem de erro:

Msg 13539, Level 15, State 1, Line 18
Setting SYSTEM_VERSIONING to ON failed because history table ‘TemporalTableTemperaturaHistorico2 is not specified in two-part name format.

4 – Ao informar a tabela que será utilizada para o versionamento dos dados o Database Engine realiza automaticamente a criação desta tabela histórico caso a mesma não exista.

Ótimo estamos no caminho certo, nosso próximo passo será abastecer a tabela TemporalTableTemperatura com dados iniciais e na sequência proporcionar alterações nestes mesmos dados iniciais para que o Database Engine faça uso da nossa Temporal Table registrando na Tabela TemperalTableTemperaturaHistorico todas as manipulações realizadas.

Para isso vamos utilizar o Bloco de Código 2 declarado abaixo:

— Bloco de Código 2 —

— Inserindo Dados na Tabela TemporalTableTemperatura —
Insert Into TemporalTableTemperatura (Temperatura)
Values (25)
Go

— Gerando um Delay de 20 segundos —
WAITFOR DELAY ’00:00:20′
Go

— Atualizando os dados na Tabela TemporalTableTemperatura —
Update TemporalTableTemperatura
Set Temperatura = 26,
HoraAtual = GetDate()
Go

— Gerando um novo Delay de 40 segundos —
WAITFOR DELAY ’00:00:40′
Go

— Atualizando os dados na Tabela TemporalTableTemperatura —
Update TemporalTableTemperatura
Set Temperatura = 27,
HoraAtual = GetDate()
Go

— Gerando um novo Delay de 1 minuto e 20 segundos —
WAITFOR DELAY ’00:01:20′
Go

— Atualizando os dados na Tabela TemporalTableTemperatura —
Update TemporalTableTemperatura
Set Temperatura = 27,
HoraAtual = GetDate()
Go

Até aqui tudo tranquilo, realizamos o processo de inserção de dados iniciais na tabela TemporalTableTemperatura e na sequência através do comando WaitFor forçamos a ocorrência de alguns delays (atrasos) de tempo para simular o aumento da temperatura como se fosse um termômetro realizando uma nova marcação, com isso, já temos neste momento um pequena porção de dados a serem consultados.

Vamos então executar o Bloco de Código 3 a seguir para identificar as possíveis maneiras de se consultar os dados armazenados em nossa temporal table:

— Bloco de Código 3 —

— Consultando dados na Tabela TemporalTableTemperatura —
Select * From TemporalTableTemperatura
Go

Após realizarmos o Select declarado acima teremos um retorno de dados similar ao apresentado na Figura 3 abaixo:
Figura 3 – Posição atual de dados armazenados na tabela TemporalTableTemperatura.

Observe que a coluna Temperatura apresenta o valor 27, número informado no último update realizado, a coluna DataHoraInicial apresentando o valor que representa o início da realização da última manipulação aplicada a tabela, no caso o comando Update e a coluna DataHoraFinal vai apresentar o valor final que representa o encerramento do período de controle de versionamento dos dados com o valor fixo e padrão 9999-12-31 23:59:59.

Pois bem, mas se quisermos então identificar ao longo do tempo todas as manipulação que podem ter ocorrido em nossa tabela temporal? É ai que entra em ação nossa tabela de espelho, nossa tabela TemporalTableTemperaturaHistorico, a qual é responsável em armazenar e controlar todo versionamento e alterações que venham a ser realizadas em nossa Temporal Table.

O próximo passo consiste na execução do Bloco de Código 4, o qual vai nos permitir consumir os dados temporais armazenados em nossa tabela TemporalTableTemperaturaHistorico:

— Bloco de Código 4 —

— Consultando dados Temporais, obtendo todas as manipulações realizadas —
Select * From TemporalTableTemperatura
For System_Time All — Apresenta todas as manipulações realizadas
Go

Figura 4 – Todas as manipulações realizadas na tabela TemporalTableTemperatura armazenadas de forma espealhada na tabela histórico TemporalTableTemperaturaHistorico.

Nota que a coluna DataHoraFinal apresenta na linha 1 o valor fixo e padrão 9999-12-31 23:59:59, mas no decorrer das demais linhas, de acordo com as operações realizadas os valores foram sendo atualizados, como podemos comprovar na linha 7 a qual apresenta o valor 2019-01-22 12:59:42.

Já estamos praticamente no final desta caminhada, nosso últimos passos consistem em realizar outras formas de consultar dados temporais, através das instruções:

  • For System_Time as Of;
  • For System_Time From ” To ”;
  • For System_Time Between ” And ”; e
  • For System_Time Contained In ().

Para realizar estas consultamos, vamos executar o Bloco de Código 5 apresentando abaixo:

— Bloco de Código 5 —

— Conhecendo outras formas de consultar dados temporais —
Select * From TemporalTableTemperatura
For System_Time as Of ‘2019-01-22 12:33:56’
Go

Select * From TemporalTableTemperatura
For System_Time From ‘2019-01-22 12:33:56’ To ‘2019-01-22 12:48:36’
Go

Select * From TemporalTableTemperatura
For System_Time Between ‘2019-01-22 12:48:36’ And ‘2019-01-22 12:58:22’
Order By Temperatura Desc
Go

Select * From TemporalTableTemperatura
For System_Time Contained In (‘2019-01-22 12:33:00′ ,’2019-01-22 12:55:00’)
Go

A Figura 5 a seguir apresentado o resultado tornado após a execução do Bloco de Código 5 declarado acima:Figura 5 – Resultados obtidos após a execução de cada comando select declarado no Bloco de Código 5.

Praticamente términos, mas quero finalizar este post com uma pequena amostra do quanto uma tabela temporal pode ser útil, imagine se excluirmos todos os dados da nossa tabela TemporalTableTemperatura.

O que aconteceria com os dados em nossa tabela espelho:

1 – Os dados seriam excluídos também?

2 – Os dados são mantidos?

3 – A tabela espelho será excluída?

4 – Não podemos remover dados em tabelas que utilizam versionamento de dados?

Bom, vou deixar o Bloco de Código 6 declarado abaixo, mas a respostas para esta pergunta você que vai descobrir e posteriormente publicar seu comentário aqui neste post:

— Bloco de Código 6 —

— Excluíndo os dados cadastrados na Tabela TemporalTableTemperatura —
Delete From TemporalTableTemperatura
Go

— Consultando dados na Tabela TemporalTableTemperaturaHistorico —
Select Local, Cidade, DataAtual, HoraAtual, Temperatura
From TemporalTableTemperaturaHistorico
Go

Com isso chegamos ao final de mais um post da sessão Dica do Mês, 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/tables/temporal-tables?view=sql-server-2017

https://en.wikipedia.org/wiki/Slowly_changing_dimension

https://docs.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table?view=sql-server-2017

https://social.technet.microsoft.com/wiki/pt-br/contents/articles/12580.slowly-changing-dimensions.aspx

https://docs.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/tables/getting-started-with-system-versioned-temporal-tables?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/tables/system-versioned-temporal-tables-with-memory-optimized-tables?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-metadata-views-and-functions?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/waitfor-transact-sql?view=sql-server-2017

Posts Anteriores

https://pedrogalvaojunior.wordpress.com/2018/10/23/dica-do-mes-comando-restore-database-page-restaurando-paginas-de-dados-de-uma-tabela-no-microsoft-sql-server/

https://pedrogalvaojunior.wordpress.com/2018/07/26/dica-do-mes-ocultando-uma-instancia-em-execucao-do-microsoft-sql-server/

https://pedrogalvaojunior.wordpress.com/2018/04/25/dica-do-mes-sql-operations-studio-view-as-chart/

https://pedrogalvaojunior.wordpress.com/2018/03/14/dica-do-mes-microsoft-sql-server-2017-sql-graph-databases/

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

CONCLUSÃO

Como já destaquei em outros posts, a cada nova versão, atualização e correção a Microsoft transforma o SQL Server em um produto surpreende, ainda mais na sua capacidade e versatilidade de permitir aos profissionais de tecnologia, administradores de bancos de dados, programadores, entre outros, utilizar recursos nativos e também novos como ferramentas que podem nos ajudar a aplicar os mais variados possíveis cenários afim de obter soluções rápidas e práticas para nossas necessidades.

No post de hoje, mais uma vez isto foi constatado, o uso de novos recursos com base em funcionalidades já existentes se tornam ferramentas valiosas e de grande importância, podemos fazer esta relação com as tabelas temporais, funcionalidade que nos possibilita viajar, navegar, caminhar ao longo do tempo analisar e entendendo as mudanças ocorridas em nossos dados.

Desta forma, nos deparamos com uma poderosa ferramenta e sua gama de recursos que nos permitem realizar as mais diversas e variados preposições de análises de dados para identificarmos a melhor forma para 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 a 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, sucesso, não se esqueça de se manter hidratado, passar bastante protetor solar para se proteger deste forte calor que estamos vivendo.

Até mais.

Você já ouvi falar na sigla AVIAO na área de banco de dados?


Olá pessoal, boa tarde.

Feliz Ano Novo, seja bem vindo ao primeiro post de 2019 do meu blog, que felicidade te encontrar aqui neste comecinho de ano, ainda mais após estes dias de feriados, festas, descansos e mesa farta (Graças a deus).

Pois bem, também estou tentando colocar meus pensamentos em ordem para redigir este post, tarefa bastante complexa de se realizar hoje, mas acredito que conseguirei.

Posso lhe garantir que este é um daqueles posts totalmente fora dos padrões que normalmente eu costumo utilizar nas sessões existentes neste blog, o conteúdo que você terá acesso no decorrer do mesmo, é resultado dos meus 25 (vinte e cinco) anos de carreira na área de tecnologia em abril deste ano, sendo que, deste totalmente de vinte e cinco anos, 19 (dezenove) deles estão totalmente envolvidos e relacionados com a área de banco de dados, tempo suficiente que me permitiu no ano de 2018 (recém encerrado no último dia 31 de dezembro) “criar” minha primeira sigla totalmente voltado para área de banco de dados, denominada AVIAO.

Antes de se aprofundar e conhecer o significado e como esta sigla foi criada, quero destacar o que me fez ter embasamento suficiente para idealização.


Um pouco de história não faz mal para ninguém

Diariamente nos deparamos com diversas situações, acontecimentos, circunstâncias, fatos e momentos que nos permitem ou apresentam um conjunto infinito de números ou até mesmos palavras que precisam demandar um pouco do nosso raciocínio afim de estabelecermos um possível cenário de entendimento e significado até chegarmos propriamente no reconhecimento do que aquele número ou palavra pode realmente nos ilustrar de significado, o que vai nos permitir então dizer que este conjunto infinito de números ou palavras podem representar um dado.

Mas como podemos definir este tão desconhecido “Dado ou Dados”?

Ao longo dos últimos 12 anos venho lecionando diversas disciplinas na área de banco de dados, entre as mais diversas instituições, faculdades, cursos e área de tecnologia, mas sempre me deparei em todos estes momentos e locais com a maior dúvida, como podemos definir ou afirmar que tal valor pode ser reconhecido como um dado ou informação?

Dados são fatos conhecidos, que podem ser registrados e possuem significado, o mesmo pode  ser definido como a matéria-prima ou o elemento básico para a geração de informações, é um elemento básico para um banco de dados.

Dado é Informação ou Informação é o Dado?

Eis a questão que constantemente me depare a cada inicio de semestre nas disciplinas básicas de banco de dados ou modelagem, e sempre respondo para meus discípulos (alunos): “Dado é tudo aquilo que existe no mundo real e nos permite atribuir ou obter um significado, algo que nos permita reconhecer um valor.”

Já a informação é justamente o valor, significado, atribuído ao dado, que nos estabelece um cenário de entendimento, reconhecimento, compreensão do que “este mesmo dado” quer nos dizer, quer nos revelar.

Então eu apresento o que chamo de Cadeia Evolutiva de um Dado, conforme ilustra a Figura 1 a seguir:

Figura 1 – Cadeia evolutiva de um dado.

Observe que todo e qualquer elemento que reconhecemos com um possível dado, o mesmo, passa por um conjunto de fases até se tornar verdadeiramente algo que nos ajude a tomar decisões e resolve em novos dados.

A Informação

Sabendo então que o dado é o estado anterior de algo que denominados constantemente como informação, o que exatamente pode ser definido ou entendido como informação? Vou tentar te responder de forma simples e objetiva:

A informação pode ser entendida com o dado com significado, passível de interpretação e de entendimento por parte de quem o usa, enfim, é o dado capaz de transmitir conhecimento para a ação ou para a tomada de decisão.

As empresas precisam armazenar os dados relevantes ao seu negócio de alguma forma controlada, organizada, integra e segura. A Tecnologia da Informação oferece recursos para atender a esta importante necessidade das empresas através de sistemas de informação que manipulam as informações da empresa que são armazenadas em um banco de dados.

Um sistema automatizado lida com tarefas comuns aos processos de negócios tais como: contabilidade, controle de estoque, compra e venda. A compreensão de que as informações mantidas por esses sistemas podem ser classificadas, organizadas e pesquisadas apoiando das atividades e tomada de decisões da empresa. Foi a partir deste momento que surgiu a ideia de um Sistema de Informações Gerenciais (SIG). A partir do momento em que os dados estão armazenados, ordenados e organizados, tinha-se a matéria prima disponível para análise por um sistema de suporte à decisão.

A tomada de decisão, ou preparação para o futuro, é a atividade central das organizações modernas. Sendo assim, o sucesso de uma empresa depende da boa tomada de decisão, e para isso a qualidade e disponibilidade dos dados é fundamental.

Sendo assim, todo que qualquer profissional independente da sua área de atuação que consiga reconhecer, entender e diferenciar Dado de uma Informação, estará sempre a frente.


E a sigla AVIAO, o que ela tem haver com estes elementos? Esta resposta eu lhe apresento a seguir:

A Sigla AVIAO

A necessidade de se estudar periodicamente esta cadeia evolutiva que um dado pode apresentar, principalmente estes dois primeiros elementos Dado e Informação, ou Dados e Informações que para muitos representam a mesma coisa, mas obrigatoriamente a maneira que eu sempre busquei, tentei e demorei um certo tempo para encontrar uma forma mais simples e direta visando transmitir para meus alunos como estes “amiguinhos” são tão significantes e importantes para a vida, fez com que eu busca-se de forma mais que constante definir um elemento único para em poucos minutos dizer que estava escrito em diversos livros, apostilas e apresentações.

Além disso, a conjunto de dúvidas, questionamentos e até mesmo críticas em minha abordagem durantes as aulas de Modelagem, Introdução a Banco de Dados, entre outras disciplinas de forma a se relacionar com a área de tecnologia se serviram como base e parâmetro para sua criação.

Como ela foi idealizada

Muito bem, mas posso dizer e garantir que a origem e criação desta sigla não ocorreu dentro de uma sala de aula ou laboratório, na verdade ela surgiu como boa parte das invenções e ideias criada pelo homem, foi em um momento que eu talvez não estivesse de corpo presente mas somente com a minha mente.

Eram quatro horas da manhã do dia 14/11, como sempre em véspera de provas de concurso ou processos de seleção eu tenho dificuldade para dormir, o sono fica leve, como também ansioso para que o tempo passe rápido, naquele mesmo dia eu tinha uma apresentação a ser realizada para um processo de seleção, esta apresentação teria três possíveis temas para serem escolhidos, todos relacionados a área de banco de dados, e eu queria achar uma maneira de tentar justamente resolver em uma única apresentação a junção dos três possíveis temas, foi então que virando várias vezes de um lado para outro e pensando em possibilidades que surgiu  sigla AVIAO, ela é recente, mas vem me orgulhando muito, pois me ajudou absurdamente a resolver sérias dificuldades acadêmicas e profissionais nos últimos dias, como principalmente facilitou o entendimento dos meus alunos.

Neste momento você estar fazendo basicamente estas duas perguntas:

1 – O que representa a sigla Aviao?

2 – Quais são as palavras que forma esta sigla?

Eis as respectivas respostas:

1 – A sigla AVIAO representa um conjunto de definições e características relacionadas diretamente com um dado e seu primeiro estado de evolução conhecido como Informação.

2 – As palavras que forma a sigla AVIAO são: Atomicidade, Veracidade ou Valor, Integridade, Autenticidade e Obrigatoriedade.

Para tentar lhe ajudar a entender melhor o quanto a sigla AVIAO pode lhe ser útil, vou descrever brevemente cada uma destas siglas:

  • Atomicidade: Característica que podemos atribuir diretamente a um dado que nos permite dizer se ele é atômico ou não atômico, ou seja, não divisível ou divisível em partes;
  • Veracidade: Característica que garante que o valor atribuído ou estabelecido para aquele elemento de dado pode ser tratado como algo correto, algo verdadeiro, confiável em sua essência e posteriormente para tomada de decisão;
  • Integridade: Propriedade que devemos estabelecer a um dado, quando estamos analisando a sua origem, ou seja, quem ou o que criou, armazenou ou até mesmo tratou tão conjunto de valores que nos permitem reconhecer como um dado. A integridade se relaciona com o quanto o mesmo o mesmo dado pode ser tratado como verídico, verdadeiro;
  • Autenticidade: Propriedade que nos garante o que valor ou algo que esta sendo apresentado e posteriormente reconhecido pelo dado, realmente é algo que pode existir ou coexistir no mundo real, no qual utilizamos como base as análises e aprendizados obtidos no mundo real, afim de reconhecer e fortalecer que aquele valor é autentico em sua essência, o que nos permite atribuir novos valores, trazendo assim a analise para geração de novos dados; e
  • Obrigatoriedade: Característica que atribuímos a determinados dados, a qual se relaciona com sua importância e posteriormente o quanto este dado pode representar de valor importante para tomada de decisão, ou seja, a obrigatoriedade tem como objetivo principal garantir a qualidade dos nossos dados, um dado que não é obrigatório não pode ser reconhecido, com um dado, verdadeiro, integro ou autentico, esta característica é essencial para definir se aquele dado deve ou não fazer parte de um estudo ou pesquisa.

Sendo assim, acabo de apresentar a sigla AVIAO que venho utilizando desde mês de novembro de 2018, nascida em uma madrugada de sono leve e ansioso devido a um momento importante que iria me deparar na manhã seguinte.


Conclusão

O conhecimento que aprendemos ao longo de nossas vidas é algo transformador, saber a cada dia reconhecer, entender, transmitir e aprender que este conhecimento é nosso dever, o que para muitos pode se tornar uma ameaça, medo ou receio.

Este mesmo elemento chamado de conhecimento, antes de mais nada é um valor ou melhor um dado que passou para algumas fases de evolução até ser reconhecido e tratado como possibilidade de se aprender, tal aprendizado que nos permite criar novos conhecimentos transformando e gerando novos valores.

Foi justamente desta forma, com base, nas minhas experiência profissionais e acadêmicas coletadas ao longo dos anos, reconhecendo e entendendo minhas limitações e dificuldades que me permitiram “criar” um elemento que representa em poucas palavras a junção de diversas conhecimento obtidos durantes anos.

Desta mesma maneira, estou fazendo com que a cadeia de evolução de um dado, continue visa, transformando e criando novos valores, afim de gerar um novo resultando, não deixando que o conhecimento mora comigo, com isso a sigla AVIAO tem me ajudado e transmitir mais facilmente conceitos básicos e de grande importância relacionados com a área de banco de dados e tecnologia.

Agradecimentos

Obrigado por sua visita mesmo primeiro post de 2019, espero que você tenha gostado do conteúdo aqui compartilhado e que o mesmo lhe sirva de inspiração para criar formas de se renovar e transmitir qualquer tipo de conhecimento mais facilmente.

Nos encontraremos nos outros 363 anos de 2019 nos mais variados posts do meu blog.

Um grande abraço, mais uma vez feliz ano novo.

#23 – Para que serve


Olá, bom dia, tudo bem? E ai preparado para festividades de final de Ano?

Seja bem-vindo a mais um post da sessão Para que Serve, sendo este o de número 23, mais um dia de muito começando, repleto de atividades e compromissos, ainda mais hoje sexta – feira e muito próximo ao Natal.

Mesmo no ritmo de fim de ano, tenho mantido a minha rotina, acordar bem cedo, para poder aproveitar da melhor maneira possível meu precioso tempo livre, colocando em prática algo que adoro fazer, publicar um post novo em meu blog mantendo a tradição de querer renovar e compartilhar as experiências e aprendizados adquiridos em minhas atividades profissionais e acadêmicas.

No post de hoje, vou compartilhar com você que esta acessando meu blog, uma nova opção adicionada diretamente aos bancos de dados que criamos a partir do Microsoft SQL Server 2016 que nos permite que seja utilizada de forma exclusiva no nível de banco de dados ao invés de aplicar diretamente no nível de instância.

Talvez você já possa ter utilizada esta nova opção, mas tenho a certeza que muitos dos profissionais que ainda não migraram seus ambientes para versões mais novas talvez não a conheçam, estou me referindo a opção AUTOGROW_ALL_FILES, que basicamente define no nível de banco de dados (Database Level) ao contrário do que acontecia nas versões anteriores que tínhamos a necessidade de ativar a Trace Flag T1117 que aplicava esta alteração de comportamento padrão no nível de instância (Server Level).

No decorrer deste post será realizado um pequeno comparativo entre as opções AUTOGROW_ALL_FILES e AUTOGROW_SINGLE_FILE, analisando seus comportamentos padrões, tendo como base um simples bloco de código inserindo 500.000 (Quinhentas mil linhas de registros lógicos) com dados fixos.

Adianto que esta análise comparativa em nenhum momento leva em consideração características de Hardware, versão de Sistema Operacional ou uso de uma aplicação específica. Na verdade o objetivo desta simples análise é elucidar que o uso destas  opções podem influenciar na maneira que o Microsoft SQL Server utiliza um ou mais arquivos de dados de forma proporcional ou simultânea.

Sendo assim, chegou a hora de conhecer um pouco mais sobre o post de número 23 da sessão Para que serve. Mas uma vez, bem vindo ao #23 – Para que serve – Opções de Bancos de Dados – AUTOGROW_ALL_FILES versus AUTOGROW_SINGLE_FILE.

Espero que você esteja animado para conhecer um pouco mais sobre esta propriedade, caso já conheça, continue lendo este post, sempre podemos aprender algo novo….


Introdução

Todo o banco de dados SQL Server tem, no mínimo, dois arquivos de sistema operacional: um arquivo de dados e um arquivo de log. Os arquivos de dados contêm dados e objetos como tabelas, índices, procedimentos armazenados e exibições.

Os arquivos de log contêm as informações necessárias para recuperar todas as transações no banco de dados, ao contrário dos arquivos de dados que podem ser agrupados em grupos de arquivos para propósitos de alocação e administração.

Os bancos de dados SQL Server possuem três tipos de arquivos, como mostrado na Tabela 1 a seguir:

Arquivo  Descrição 
Primário O arquivo de dados primário contém as informações de inicialização do banco de dados e aponta para os outros arquivos no banco de dados.
Dados do usuário e objetos podem ser armazenados neste arquivo ou em arquivos de dados secundários. Todo banco de dados possui um arquivo de dados primário. A extensão de nome de arquivo indicada para arquivos de dados primários é .mdf.
Secundário Os arquivos de dados secundários são opcionais, definidos pelo usuário, e armazenam dados do usuário.
Arquivos secundários podem ser usados para distribuir os dados entre os diversos discos, colocando cada arquivo em uma unidade de disco diferente. Além disso, caso um banco de dados exceda o tamanho máximo em um único arquivo Windows, será possível usar arquivos de dados secundários, assim, o banco de dados continuará a crescer.
A extensão de nome de arquivo indicada para arquivos de dados secundários é .ndf.
Log de transações Os arquivos de log de transações armazenam as informações de log usadas para recuperar o banco de dados. Deve haver, no mínimo, um arquivo de log para cada banco de dados.
A extensão de nome de arquivo indicada para arquivos de transação é .ldf.

Tabela 1 – Tipos de Arquivos que formam um banco de dados criado no Microsoft SQL Server.

Agora que já conhecemos os tipos de arquivos que podem compor um banco de dados, vamos conhecer um pouco sobre um outro importante recurso que esta diretamente relacionado a este post, os denominados Filegroups.

Filegroups

Quando objetos são criados no banco de dados sem especificar a qual grupo de arquivos eles pertencem, os objetos são atribuídos ao grupo de arquivos padrão. A qualquer hora, um grupo de arquivos é designado como o grupo de arquivos padrão.

Os arquivos no grupo de arquivos padrão devem ser grandes o suficientes para armazenar qualquer objeto novo alocado a outros grupos de arquivo.

O grupo de arquivos PRIMÁRIO é o grupo de arquivos padrão, a menos que seja alterado usando a instrução ALTER DATABASE. A alocação para os objetos de sistema e de tabelas permanece no grupo de arquivos PRIMÁRIO, e não no novo grupo de arquivos padrão. O SQL Server mapeia um banco de dados de um conjunto de arquivos do sistema operacional.

As informações de log e dados nunca ficam misturadas no mesmo arquivo, e os arquivos individuais são usados apenas por um banco de dados, os grupos de arquivos são conhecidos como coleções de arquivos e são usados para simplificar o posicionamento de dados e em tarefas administrativas, como operações de backup e restauração.

Crescimento de Arquivos de Dados

Ao criar um banco de dados, estamos estabelecendo o uso de uma ou mais áreas em disco rígido para alocar nossos arquivos de dados. Dentre as diversas opções e propriedades que podemos configurar no momento da criação de um novo banco de dados ou em sua alteração, nos deparamos com a propriedade FileGrowth, sendo esta responsável em estabelecer a forma ou método de crescimento que toda estrutura de arquivos de dados que formam nossos bancos deverá aplicar, escolhendo a método de rodízio (Round-Robin) na qual os arquivos vão sendo preenchidos de forma aleatória de acordo com a necessidade ou através do método Preenchimento Proporcional (Proportional Fill).

Chegou a hora de colocar a mão nos teclados, como de costume teremos um ambiente de testes a ser criado, o que será utilizado como cenário de estudos.

Criando o Ambiente

Em meu ambiente de estudos estou utilizando o Microsoft SQL Server 2017 Enterprise Edition – Cumulative Update 9 e Sistema Operacional Windows 10, fique a vontade para utilizar o melhor cenário possível dentro das suas necessidades, a partir da versão 2016 do Microsoft SQL Server.

Para realizar nossa simples prática, começaremos pela execução do Bloco de Código 1, responsável por criar a seguinte estrutura:

  • Databases: TesteDatabaseAUTOGROWSINGLEFILE; e TesteDatabaseAUTOGROWALLFILES;
  • Tables: TabelaGrowSingleFile; e TabelaGrowAllFile.

Importante: Destaco que os caminhos informados para criação dos referidos bancos e seus arquivos, estão apresentados de acordo com a configuração do meu ambiente, fique a vontade para alterar de acordo com suas necessidades e configurações.

— Bloco de Código 1 —
— Criando os respectivos bancos de dados  —

— Criando o Banco de Dados TesteDatabaseAUTOGROWSINGLEFILE —
Create Database TesteDatabaseAUTOGROWSINGLEFILE
On Primary
(Name= ‘TesteDatabaseAUTOGROWSINGLEFILE_Data’,
FileName= ‘S:\MSSQL-2017\Data\TesteDatabaseAUTOGROWSINGLEFILE_Data.mdf’,
Size=10 MB,
MaxSize=4096 MB,
FileGrowth=100 MB),
(Name= ‘TesteDatabaseAUTOGROWSINGLEFILE_Data1’,
FileName= ‘S:\MSSQL-2017\Data\TesteDatabaseAUTOGROWSINGLEFILE_Data1.ndf’,
Size=10 MB,
MaxSize=4096 MB,
FileGrowth=100 MB),
(Name= ‘TesteDatabaseAUTOGROWSINGLEFILE_Data2’,
FileName= ‘S:\MSSQL-2017\Data\TesteDatabaseAUTOGROWSINGLEFILE_Data2.ndf’,
Size=10 MB,
MaxSize=4096 MB,
FileGrowth=100 MB)
Log On
(Name= ‘TesteDatabaseAUTOGROWSINGLEFILE_Log’,
FileName= ‘S:\MSSQL-2017\Log\TesteDatabaseAUTOGROWSINGLEFILE_Log.Ldf’,
Size=20 MB,
MaxSize=8192 MB,
FileGrowth=200 MB)
Go

— Criando o Banco de Dados TesteDatabaseAUTOGROWALLFILES —
Create Database TesteDatabaseAUTOGROWALLFILES
On Primary
(Name= ‘TesteDatabaseAUTOGROWALLFILES_Data’,
FileName= ‘S:\MSSQL-2017\Data\TesteDatabaseAUTOGROWALLFILES_Data.mdf’,
Size=10 MB,
MaxSize=4096 MB,
FileGrowth=100 MB),
(Name= ‘TesteDatabaseAUTOGROWALLFILES_Data1’,
FileName= ‘S:\MSSQL-2017\Data\TesteDatabaseAUTOGROWALLFILES_Data1.ndf’,
Size=10 MB,
MaxSize=4096 MB,
FileGrowth=100 MB),
(Name= ‘TesteDatabaseAUTOGROWALLFILES_Data2’,
FileName= ‘S:\MSSQL-2017\Data\TesteDatabaseAUTOGROWALLFILES_Data2.ndf’,
Size=10 MB,
MaxSize=4096 MB,
FileGrowth=100 MB)
Log On
(Name= ‘TesteDatabaseAUTOGROWALLFILES_Log’,
FileName= ‘S:\MSSQL-2017\Log\TesteDatabaseAUTOGROWALLFILE_Log.Ldf’,
Size=20 MB,
MaxSize=8192 MB,
FileGrowth=200 MB)
Go

Nota: Vale ressaltar que ambos os bancos de dados estão estruturados com a mesma quantidade de arquivos de dados e log, respectivamente 3(três) arquivos de dados e somente um único arquivo de log.

Muito bem, neste momento nossos bancos de dados estão criados e ambos contendo a mesma configuração para as propriedades: Size, MaxSize e FileGrowth, como também, configurados para que o crescimento ocorra de forma aleatória para os arquivos de dados.

Vamos validar nossa estrutura através do Bloco de Código 2 apresentado abaixo, o qual vai nos permitir identificar justamente as configurações que aplicamos no momento da criação dos referidos bancos de dados:

— Bloco de Código 2 —
Select DB_NAME() AS [DatabaseName], Name, file_id, physical_name,
(size * 8.0/1024) as Size,
((size * 8.0/1024) – (FILEPROPERTY(name, ‘SpaceUsed’) * 8.0/1024)) As FreeSpace
From sys.database_files
Go

Após executar o Bloco de Código 2, o Management Studio deverá apresentar o resultado similar a ilustrado na Figura 1 para ambos os bancos de dados:
Figura 1 – Informações sobre os bancos de dados seus arquivos, tamanhos e espaço livre ocupado.

Nosso próximo passo será forçar o crescimento dos nossos arquivos para o Banco de Dados TesteDatabaseAUTOGROWSINGLEFILE, observando o comportamento que o SQL Server vai utilizar, ressalto que estaremos fazendo a criação da tabela que utilizaremos como base para inserir os dados, para tal cenário vamos utilizar o Bloco de Código 3 apresentado a seguir:

— Bloco de Código 3 —
Use TesteDatabaseAUTOGROWSINGLEFILE
Go

— Criando a Tabela TabelaGrowSingleFile —
Create Table TabelaGrowSingleFile
(Codigo Int Identity(1,1) Not Null Primary Key,
Texto VarChar(100) Default ‘Grow Single File’,
Quantidade SmallInt Default 2018,
ValoresNumericos Numeric(8,2) Default ‘2018.12’,
DataAtual Date Default GetDate()+Rand()*30)
Go

— Inserindo a massa de dados —
Insert Into TabelaGrowSingleFile Default Values
Go 500000

A partir do momento que o Bloco de Código 3 é executado, o Microsoft SQL Server em conjunto com o SQL OS, Database Engine e Storage Engine, começam a fazer uso dos arquivos de dados, distribuindo em tempo real as páginas de dados conforme o método ou técnica escolhida para preenchimento dos arquivos de dados.

Para confirmarmos a alocação e distribuição destas páginas, vamos executar o Bloco de Código 4 apresentado abaixo em uma Nova Query e observar o resultado apresentado:

— Bloco de Código 4 —
Use TesteDatabaseAUTOGROWSINGLEFILE
Go

DBCC ShowFileStats — Comando que vai apresentar a distribuição de páginas de dados entre os arquivos
Go

Observação: Execute o comando DBCC ShowFileStats durante a execução do Bloco de Código 3, para que você possa obter um resultado similar ao apresentado na Figura 2 abaixo:

Figura 2 – Resultado apresentado pelo comando DBCC ShowFileStats.

No decorrer do processamento do Bloco de Código 3, podemos novamente o Bloco de Código 2 para identificar que neste momento o Microsoft SQL Server esta fazendo uso método de preenchimento dos arquivos de dados Round-Robin, no qual ele identifica qual seria o melhor arquivo para alocar a página, para confirmar este cenário a Figura 3 ilustra o resultado obtido de mais uma execução do Bloco de Código 2:
Figura 3 – Alocação dos arquivos de dados.

Observe a mudança de valores nas colunas Size e FreeSpace, como também, suas diferenças de valores em relação ao File_ID=1 para com os File_ID = 2 e 3, são justamente estas diferenças que nos orientam a entender que o Round-Robin esta sendo utilizado.

Estamos indo bem, falta um pouco para chegarmos ao final….

Nosso próximo passo se destina a alterar a forma de preenchimento e utilização dos arquivos de dados definidos para o Banco de Dados TesteDatabaseAUTOGROWALLFILES, e em seguida forçar o uso de cada arquivos e a distribuição de páginas. Vamos então executar o Bloco de Código 5 e sua sequência de passos:

 — Bloco de Código 5 —
— Alterando a definição de crescimento dos arquivos de Dados para o FileGroup Primary —
Use Master
Go

Alter Database TesteDatabaseAUTOGROWALLFILES
Modify FileGroup [Primary] AUTOGROW_ALL_FILES — Definindo o crescimento proporcional   para todos os arquivos de dados —
Go

— Acessando o Banco de Dados —
Use TesteDatabaseAUTOGROWALLFILES
Go

— Criando a Tabela TabelaGrowSingleFile —
Create Table TabelaGrowAllFile
(Codigo Int Identity(1,1) Not Null Primary Key,
Texto VarChar(100) Default ‘Grow All File’,
Quantidade SmallInt Default 2018,
ValoresNumericos Numeric(8,2) Default ‘2018.12’,
DataAtual Date Default GetDate()+Rand()*30)
Go

— Inserindo a massa de dados —
Insert Into TabelaGrowAllFile Default Values
Go 500000

Neste momento temos o banco de dados TesteDatabaseAUTOGROWALLFILES sendo utilizando, no qual sua estrutura de arquivos esta definida para ser utilizada de forma conjunto e proporcional, ou seja, conforme a necessidade de crescimento dos arquivos de dados, ao invés de um único arquivo ser invocado e ter seu valor de crescimento definido, todos os arquivos serão envolvidos e afetados, conforme a Figura 4 a seguir ilustra:
Figura 4 – Preenchimento de todos os arquivos de dados sendo realizado com base no uso da opção Grow_All_Files.

Estamos quase lá, para finalizar nosso estudos, vamos executar o Bloco de Código 6, o qual tem a finalidade de repetir a inserção das 500.000 mil linhas de registros em cada banco de dados e posteriormente forçar um crescimento para os arquivos de dados:

 — Bloco de Código 6 —
— Acessando o Banco de Dados —
Use TesteDatabaseAUTOGROWSINGLEFILE
Go

— Inserindo a massa de dados —
Insert Into TabelaGrowSingleFile Default Values
Go 500000

— Acessando o Banco de Dados —
Use TesteDatabaseAUTOGROWALLFILES
Go

— Inserindo a massa de dados —
Insert Into TabelaGrowAllFile Default Values
Go 500000

Note que para o Banco de Dados TesteDatabaseAUTOGROWSINGLEFILE o crescimento vai ocorrer no primeiro arquivo de dados e para no Banco de Dados TesteDatabaseAUTOGROWALLFILES este crescimento será aplicado a todos os arquivos, conforme apresenta a Figura 5 abaixo:
Figura 5 – Espaço ocupado pelos arquivos de dados após o crescimento ser ocorrido.

Para finalizar, compartilho a Figura 6 que apresenta a utilização dos arquivos de dados por parte do Storage Engine de acordo com o método de alocação e preenchimento dos arquivos de dados, respeitando a configuração dos bancos de dados aqui utilizados:

Figura 6 – Comparativo entre AutoGrowSingleFile versus AutoGrowAllFiles.

Importante: Observe que todos os arquivos de dados definidos para uso no banco de Dados TesteDatabaseAutoGrowAllFiles apresentam os mesmos valores para colunas Size e FreeSpace, cenário totalmente diferente para o banco de dados TesteDatabaseAutoGrowSingleFile, que ilustra a utilização de forma diferente dos arquivos de dados, no qual os arquivos File_ID=2 e 3 estão neste momento sem espaço livre, o que indica que o crescimento foi aplicado ao primeiro arquivo de dados.

Com isso, e sem mais delongas, chegamos ao final. Ufa deu um pouco de trabalho este post, como de costume, mesmo assim sempre vale a pena poder compartilhar um pouco do conhecimento e experiências adquiridas ao longo dos anos de trabalho como DBA e Professor.

Espero que você tenha gostado, eu posso dizer que sim, mas sua opinião é muito importante.


Referências

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

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options?view=sql-server-2017

https://blogs.msdn.microsoft.com/psssql/2016/03/15/sql-2016-it-just-runs-faster-t1117-and-t1118-changes-for-tempdb-and-user-databases/

https://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-2017

https://www.sqlshack.com/understanding-sql-server-proportional-fill-algorithm/

http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30218/

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/2018/08/22/22-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/05/28/21-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/04/12/20-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/01/02/19-para-que-serve/

Conclusão

Conhecer a cada nova versão as mudanças e novidades aplicadas ao Microsoft SQL Server não é uma tarefa fácil, mas deixar de fazer uso delas pode em algum momento parecer falta de interesse ou até mesmo desconhecimento do potencial existente no produto.

Neste post, podemos conhecer esta nova opção Auto_Grow_All_Files, que nos permite aplicar uma nova maneira de orientar o SQL Server no uso, alocação e principalmente crescimento de nossos arquivos de dados, o que pode ou não impactar de forma direta na performance, contenção ou distribuição de recursos relacionados a disco rígido.

Em momento algum, o cenário aqui utilizado, muito menos a análise feita, teve o objetivo de comprovar qual forma de alocação e uso dos arquivos de dados é melhor, isso deve ser analisado para cada necessidade e ambiente.

Espero que o conteúdo aqui apresentado possa lhe ajudar a conhecer um pouco sobre como os arquivos de dados são importantes e úteis para nossos bancos, além disso, a importância de se utilizar mais de um arquivo de dados ou filegroups.

Este é o fantástico Microsoft SQL Server, que desde suas primeiras versões nos apresenta inúmeras possibilidades de se aprender, possibilitando usar o passado como fonte de inspiração para construção de um futuro melhor, por isso que a cada dia eu me apaixono ainda mais por este produto…

Vai SQL Server, Vai SQL Server….

Agradecimentos

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 a ser publicado no mês fevereiro de 2019.

Um grande abraço e ótima semana.

Valeu.