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.

Anúncios

Short Scripts – Novembro 2018


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

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

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

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

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

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

O post de hoje

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

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

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

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

Short Scripts

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

SET IDENTITY_INSERT NomedaTabela On
Go

SET IDENTITY_INSERT NomeDaTabela Off
Go

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

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

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

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

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

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

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

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

— Exemplo 1 —
Create Table Valores
(Codigo Int)

Insert Into Valores Values(1)
Go 100

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

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

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


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

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

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

Links

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

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

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

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

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

Agradecimento

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

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

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

Até mais.

Short Scripts – Setembro 2018


Buenos dias, bom dia, comunidade….

E ai como passaram os últimos meses?

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

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

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

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

O post de hoje

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

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

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

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

Short Scripts

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

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

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

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

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

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

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

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

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

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

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

END
RETURN
END

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

Links

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

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

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

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

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

Agradecimento

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

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

Abraços…

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


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

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

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

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

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

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

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


Introdução

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

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

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

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

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

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

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

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

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

Elementos básicos

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

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

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

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

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

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

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

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

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

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

Funções

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

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

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

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

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

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

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

Tabelas de Sistemas e Metadados

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

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

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

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

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

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

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

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

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

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

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

Implementando o SQL Graph Databases

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

Figura 2 – Diagrama – Estrutura utilizada para o exemplo.

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

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

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

Criando o ambiente

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

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

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

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

— Criando o Banco de Dados —
Create Database SQLGraphDatabases
Go

— Acessando o Banco de Dados —
Use SQLGraphDatabases
Go

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

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

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

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

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

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

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

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

Select * From Products
Go

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Figura 4 – Dados inseridos na edge Table Microsoft.

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

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

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

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

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

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

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

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

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

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


Referências

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

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

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

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

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

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

 

Posts Anteriores

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

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

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

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

Conclusão

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

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

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

Agradecimentos

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

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

Viva o Microsoft SQL Server 2017 e suas novas funcionalidades…

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

Script Challenge – 13 – A resposta….


Salve pessoal, bom dia.

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

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

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


Script Challenge 13

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

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

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

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

A resposta

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

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

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

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

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

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

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

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

— Simulando a ocorrência de dados fantasmas —

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

— Acessando o Banco de Dados —
Use GhostDB
Go

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


Sua Participação

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

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

Referências

Agradecimentos

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

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

Até a próxima…

Short Scripts – Setembro 2017


Muito boa tarde!!! Olá galera….

Após mais um sábado de aulas na Fatec São Roque, chegou a hora de se dedicar a comunidade de tecnologia, mais especificamente aos amantes de bancos de dados.

Como promessa é dívida e deve ser cumprida “ou melhor” paga, estou pagando a minha feita a exatos três meses no final do último post da sessão Short Scripts, hoje publicando mais um conjunto de scripts adicionados a minha biblioteca particular de códigos e exemplos nos últimos meses.

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 Datas;
  • Comando Select;
  • Computed Column;
  • CTE Recursiva;
  • Formatação de Plano de Contas;
  • Função Format();
  • Função Parse();
  • Operador Cross Apply;
  • Operador Outer Apply;
  • Scalar User Defined Function;
  • Sequência Fibonacci; e
  • Sequência numérica de CEPs.

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

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

Short Scripts

— Short Script 1  – Calculando o Ano e Mês posterior através do ano e mês  —

Declare @AnoMes int = 201712

Declare @AnoMesPosteriorDate date = DATEADD(MONTH, @AnoMes % 100, DATEADD(YEAR, @AnoMes / 100 – 1900, 0))

Declare @AnoMesPosterior int = YEAR(@AnoMesPosteriorDate) * 100 + Month(@AnoMesPosteriorDate)

Select @AnoMes, @AnoMesPosterior
Go

— Short Script 2  –  Comando Select especificando o nome da coluna e o valor através do sinal de igual —

Select ‘1/0’ = ‘Hello’, ‘2…’ = ‘teste’
Go

— Short Script 3  – Formatando a apresentação de um Plano de Contas —

DECLARE @tableDados TABLE ( Codigo VARCHAR(100) )

INSERT  INTO @tableDados
        SELECT  ‘1.0.0.0.0.00.00’
        UNION  ALL
        SELECT  ‘1.1.0.0.0.00.00’
        UNION ALL
        SELECT  ‘1.1.1.1.2.00.00’
        UNION ALL
        SELECT  ‘2.3.7.1.1.01.00’
        UNION ALL
        SELECT  ‘2.3.2.0.5.00.00’

SELECT  Codigo FROM @tableDados

SELECT TD.Codigo,  LEFT(TD.CODIGO, LEN(TD.CODIGO) – PATINDEX(‘%[1-9]%’, REVERSE(TD.CODIGO)) + 1)
FROM @tableDados AS TD
Go

— Short Script 4  –  Formatando a apresentação de dados através das funções Parse() e Format() —

Declare @Valor Varchar(10)
Set @Valor=’1,540.20′

Select @Valor As Antes, PARSE(@Valor As money Using ‘en-US’) As Depois

Select FORMAT(Cast(@Valor As Money),’C’,’pt-BR’)
Go

— Short Script 5  – Analisando as diferentes de comportamente entre os operadores Cross Apply e Outer Apply —

Declare @Tabela1 Table
(Codigo Int,
Valor Int)

Declare @Tabela2 Table
(Codigo Int,
Valor Int)

Insert Into @Tabela1 Values (1,1),(2,2),(Null, Null)
Insert Into @Tabela2 Values (1,1),(2,2),(3,3),(4,4),(5,5), (Null, Null)

— Utilizando operador Outer Apply —
Select T.Codigo,
T.Valor
From @Tabela1 T Outer Apply (Select Codigo From @Tabela2
Where Codigo = T.Codigo) As T2

— Utilizando operador Cross Apply —
Select T.Codigo,
T.Valor
From @Tabela1 T Cross Apply (Select Codigo From @Tabela2
Where Codigo = T.Codigo) As T2

— Short Script 6  – Criando a sequência Fibonacci de valores —

Create Table Sequence
(Code Int Primary Key Identity(1,1),
Number BigInt Not Null)
Go

Declare @Counter Int = 1, @String Varchar(Max)

While @Counter <=50
Begin

Insert Into Sequence (Number)
Select IsNull(Sum(Number),1) from Sequence
Where Code < @Counter -1

Set @String = (Select Concat(@String,’,’,Number) from Sequence Where Code = @Counter)

Set @Counter +=1

End

Update Sequence
Set Number = 0
Where Code = 1

Select Number ‘Number List’ From Sequence

Select ‘0’+@String As ‘Sequence Finobacci’
Go

— Short Script 7 –  Criando uma Computed Column para uso de uma Scalar User Defined Function —

— Criando a Function F_CalcularDiferencaAnos —
Create Function F_CalcularDiferencaAnos (@DataNascimento Date)
Returns Int
As
Begin
Return (Select DATEDIFF(Year, @DataNascimento, GetDate()))
End
Go

— Criando a Tabela1 para Teste —
Create Table Tabela1
(Codigo Int,
DataNascimento Date,
DiferencaComputada As (dbo.F_CalcularDiferencaAnos(DataNascimento))) — Criando uma coluna computada com a function —
Go

— Inserindo os dados —
Insert Into Tabela1 (Codigo, DataNascimento)
Values (1,’1980-04-28′), (2,’1981-01-28′)
Go

— Validando o resultado —
Select * from Tabela1
Go

— Short Script 8  –  Criando uma sequência de valores de CEPs através de uma CTE Recursiva —

Declare @Tabela table
(Codigo int, Cidade varchar(40), Inicio char(9), Fim char(9));

insert into @Tabela values (1, ‘Belo Horizonte’, ‘30000-000’, ‘35000-000’)

;With CTE_Rec as
(
Select Cidade,
            Cast(left(Fim, 5) + right(Fim, 3) as int) as Fim,
            Cast(left(Inicio, 5) + right(Inicio, 3) as int) as Cep
From @Tabela

Union all

Select Cidade, Fim, Cep + 1
from CTE_rec
where Cep < Fim
)

Select Cidade, cast(Cep / 1000 as char(5)) + ‘-‘ + right(’00’ + cast(Cep % 1000 as varchar), 3) as Cep,
(Cep / 1000) as Div,
(Cep % 1000) as Div2
from CTE_Rec
OPTION (MAXRECURSION 0)
Go

 

Ufa, conseguimos! Mais uma 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:

Agradecimento

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

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

Um forte abraço, até mais.

Dica do Mês – Simulando a inserção de uma massa de dados aleatória


Olá pessoal, boa tarde.

Estou retornando com mais um post da sessão Dica do Mês, onde hoje vou compartilhar algo que poderá lhe ajudar de maneira simples e direta em suas atividades relacionadas a simulação de manipulação de dados no Microsoft SQL Server.

Acredito que em algum momento você já teve a necessidade de inserir dezenas, centenas ou até mesmo milhares de linhas em uma determinada tabela em seus bancos de dados, se você ainda não passou por esta necessidade pode ter a certeza que em algum momento isso poderá lhe acontecer.

Pensando justamente nisso, vou compartilhar no post de hoje um script de minha autoria que utilizo justamente para realizar esta inserção de dados aleatórios em uma tabela específica sem levar em consideração a qualidade e veracidade dos dados, como também regras de integridade referencial ou normalização.

Sendo assim, seja bem vindo ao post – Dica do Mês – Simulando a inserção de uma massa de dados aleatória.

Bom divertimento……


Introdução

A necessidade de se trabalhar com um volume considerável de dados pode ser algo bastante comum para muitos profissionais de bancos de dados, para outros nem tanto.

Este pode parecer um cenário bastante complexo, que venha a exigir um grande conhecimento técnico ou até mesmo o uso de ferramentas de terceiros para tal finalidade. Mas na sua grande maioria os recursos existentes no próprio Microsoft SQL Server nos permite criar scripts ou blocos de códigos capazes de realizar tão procedimento sem requerer a obtenção ou exigência de um alto nível de conhecimento.

Nosso Cenário

Afim de tentar ilustrar como podemos realizar este tipo de procedimento em um ambiente de banco de dados, vamos trabalhar com um ambiente de banco de dados bastante simples e que apresenta baixa complexidade.

Nosso ambiente será forma por uma única tabela denominada MassaDeDados, composta pela seguinte estrutura de colunas:

  • Codigo;
  • ClienteID;
  • VendedorID;
  • Quantidade;
  • Valor; e
  • Data

A coluna denominada Codigo será definida como nossa coluna chave primária artificial numerada de forma sequência próprio SQL Server, tento como finalidade evitar a duplicação de registros e principalmente ser utilizada como atributo de busca e pesquisa de nossos dados. As demais colunas estarão sendo utilizadas como atributos complementares para ilustrar a capacidade de geração de dados aleatórios e posteriormente inseridos em nossa tabela MassaDeDados.

A seguir apresento o Bloco de Código 1, utilizado para criação da tabela MassaDeDados:

— Bloco de Código 1 —

Create Table MassaDeDados
(Codigo int IDENTITY(1,1) NOT NULL Primary Key,
ClienteId int NOT NULL,
VendedorId varchar(10) NOT NULL,
Quantidade smallint NOT NULL,
Valor numeric(18, 2) NOT NULL,
Data date NOT NULL)
Go

Perfeito, note que este bloco de código é bastante simples, nosso próximo passo será justamente trabalhar com o bloco de código que nos permitirá gerar o volume de dados aleatórios inseridos na sequência dentro da tabela MassaDeDados, para tal procedimentos vamos utilizar o Bloco de Código 2 apresentado abaixo:

— Bloco de Código 2 —

Declare @Texto Char(130), @Posicao TinyInt, @ContadorLinhas Int

Set @Texto = ‘0123456789@ABCDEFGHIJKLMNOPQRSTUVWXYZ\_abcdefghijklmnopqrstuvwxyzŽŸ¡ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåæçèéêëìíîïðñòóôõöùúûüýÿ’ — Existem 130 caracteres neste texto —

Set @ContadorLinhas = Rand()*100000 — Definir a quantidade de linhas para serem inseridas —

While (@ContadorLinhas >=1)
Begin

Set @Posicao=Rand()*130

 

If @Posicao <=125
Begin
Insert Into MassaDeDados (ClienteId, VendedorId, Quantidade, Valor, Data)
Values(@ContadorLinhas,
Concat(SubString(@Texto,@Posicao+2,2),SubString(@Texto,@Posicao-4,4),SubString(@Texto,@Posicao+2,4)),
Rand()*1000,
Rand()*100+5,
DATEADD(d, 1000*Rand() ,GetDate()))

End
Else
Begin
Insert Into MassaDeDados (ClienteID, VendedorID, Quantidade, Valor, Data)
Values(@ContadorLinhas,
Concat(SubString(@Texto,@Posicao-10,1),SubString    (@Texto,@Posicao+4,6),SubString(@Texto,@Posicao-12,3)),
Rand()*1000,
Rand()*100+5,
DATEADD(d, 1000*Rand() ,GetDate()))

End

Set @ContadorLinhas = @ContadorLinhas – 1
End

Observação: Note que estou utilizando a função Concat() para realizar a concatenação dos dados sem a necessidade de conversão implícita, sendo esta função recurso adicionado a partir da versão 2012 do SQL Server, caso você esteja utilizando uma versão anterior remova a função Concat() e aplique a concatenação utilizando o operador de soma.

Agora basta executar o bloco de código e verificar o resultado obtido, no meu caso após a execução deste mesmo exemplo obtive o resultado apresentado pela Figura 1 apresentada abaixo:

Figura 1 – Massa de Dados aleatórios inseridos na tabela MassaDeDados.

Observando a Figura 1, torna-se fácil analisar o conjunto de dados aleatórios inseridos em cada coluna de nossa tabela, como também, a quantidade de linhas inseridas sendo um total de 41.857 linhas de registros.

Inicialmente o tamanho do bloco de código poderá parecer complexo ou até mesmo confuso, mas na verdade não é bem assim, o grande segredo esta justamente no uso da variável @Texto que apresenta um conjunto de 130 caracteres que podemos utilizar no processo de geração do nosso volume aleatório de dados.

Outro fator muito importante é o uso da função RAND() que nos possibilidade o sorteio de números decimais, algo que ajuda bastante quando queremos trabalhar com faixas de valores distintos.

Vale ressaltar que a função RAND trabalhar com valores decimais, para que seja possível realizar o sorteio aleatório de números e depois transformar em números inteiros temos a necessidade de utilizar variáveis que trabalhem com tipos de dados inteiros, neste caso: TinyInt, SmallInt, Int ou BigInt, caso contrário os valores retornados por esta função será sempre tratados e apresentados no formato de números decimais.

Com isso chegamos ao final de mais um post da sessão Dica do Mês, espero que você tenha gostado.

Referências

https://technet.microsoft.com/pt-br/library/ms177610(v=sql.105).aspx

https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-local-variable-transact-sql

https://technet.microsoft.com/pt-br/library/hh231515(v=sql.110).aspx

Posts Anteriores

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

https://pedrogalvaojunior.wordpress.com/2017/04/13/dica-do-mes-microsoft-sql-server-identificando-as-transacoes-que-estao-utilizando-o-transact-log/

https://pedrogalvaojunior.wordpress.com/2017/03/01/dica-do-mes-microsoft-sql-server-2016-sp1-novo-argumento-use-hint-disponivel-para-query-hints/

https://pedrogalvaojunior.wordpress.com/2017/01/16/dica-do-mes-conhecendo-a-nova-dmf-sys-dm_exec_input_buffer-no-microsoft-sql-server-2016/

Conclusão

Em diversos cenários temos a necessidade de imaginar diversas possibilidades para se obter a solução de um problema que inicialmente se apresenta com algo de outro mundo, por outro lado realizando uma análise com calma e simulando diversos cenários esta possível solução tão “misteriosa” e “complexa” pode estar na frente de nossos olhos.

No post de hoje isso não foi diferente, realizar a inserção de um volume aleatório e fictício de dados dentro do Microsoft SQL Server pode ser feito de forma bastante simples e prática, sem requerer qualquer tipo ferramentas adicionais ou conhecimento de alto nível.

Agradecimentos

Mais uma vez obrigado por sua visita, agradeço sua atenção, fique a vontade para enviar suas críticas, sugestões, observações e comentários.

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

Valeu….

Short Script – Junho – 2015


Bom dia, Comunidade! Hello Everybody!!!

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

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

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

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

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

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

 

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

Create PROCEDURE dbo.usp_ExecCmdShellProcess

AS

BEGIN

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

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

 

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

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

 

EXEC msdb..sp_add_job @job_name = @job,

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

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

 

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

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

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

 

EXEC msdb..sp_add_jobserver @job_name = @job ;

 

EXEC msdb..sp_start_job @job_name = @job ;

 

Exec(@Bulkcmd)

 

END ;

GO

 

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

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

RETURNS VARCHAR(50)

BEGIN

RETURN

(

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

)

END

 

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

 

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

Create Table MySampleTable

(Id1 Int,

Id2 Int,

SomeData Varchar(100))

 

Create View SampleView

With SchemaBinding

As

Select COUNT(*) TableCount,

Id2

From dbo.MySampleTable

Group By Id2

 

— Erro ao criar —

Create Unique Clustered Index [IX_ViewSample]

On [dbo].[SampleView]

(Id2 Asc)

Go

 

— Alterando o tipo de contagem de dados —

Create View SampleView

With SchemaBinding

As

Select COUNT_BIG(*) TableCount,

Id2

From dbo.MySampleTable

Group By Id2

 

— Criando o Índice —

Create Unique Clustered Index [IX_ViewSample]

On [dbo].[SampleView]

(Id2 Asc)

Go

 

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

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

 

Insert Into #A Values (‘1’);

Insert Into #A Values (‘6’);

Insert Into #A Values (‘2’);

Insert Into #A Values (‘3’);

Insert Into #A Values (‘5’);

Insert Into #A Values (‘5’);

Insert Into #A Values (‘6’);

Insert Into #A Values (‘9’);

Go

 

Create Table #B (M BigInt);

 

Insert Into #B Values(5);

Insert Into #B Values(5);

Insert Into #B Values(6);

Insert Into #B Values(7);

Insert Into #B Values(7);

Go

 

SELECT x AS ‘Select #1’ FROM #A

INTERSECT SELECT M FROM #B;

 

— (Select #2)

SELECT DISTINCT(x) AS ‘Select #2’

FROM #A

LEFT OUTER JOIN #B

ON #A.x = #B.M

 

— (Select #3)

SELECT DISTINCT(x) AS ‘Select #3’

FROM #A

LEFT OUTER JOIN #B

ON #A.x = #B.M

 

— (Select #4)

SELECT DISTINCT(x) AS ‘Select #4’

FROM #A

INNER JOIN #B

ON #A.x = #B.M

 

— (Select #5)

SELECT x AS ‘Select #5’

FROM #A

INNER JOIN #B

ON #A.x = #B.M

 

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

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

returns table as return

SELECT Cast(@C *

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

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

as money) as Prestação;

go

 

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

— Exemplo 1 —

DROP TABLE #temp;

 

CREATE TABLE #temp (

RowID INT IDENTITY PRIMARY KEY CLUSTERED,

Name VARCHAR(25),

Column1 INT SPARSE,

Column2 INT SPARSE,

Column3 INT SPARSE,

Column4 INT SPARSE,

ColumnSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);

 

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

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

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

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

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

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

 

SELECT * FROM #temp;

— Exemplo 2 —

DROP TABLE #temp;

 

CREATE TABLE #temp (

RowID INT IDENTITY PRIMARY KEY CLUSTERED,

Name VARCHAR(25),

Column1 INT SPARSE,

Column2 INT SPARSE,

Column3 INT SPARSE,

Column4 INT SPARSE);

 

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

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

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

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

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

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

 

SELECT * FROM #temp;

 

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

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

Declare @XMLIntLog As XML

Declare @IDoc Int

 

Set @XMLIntLog = Null

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

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

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

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

 

Exec sp_xml_preparedocument @IDoc Output, @XMLIntLog

 

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

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

 

Exec sp_xml_removedocument @IDoc

 

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

CREATE TABLE dbo.Table1 (

RowID INTEGER IDENTITY PRIMARY KEY CLUSTERED,

DateTimeStamp DATETIME DEFAULT GETDATE(),

Col1 INTEGER SPARSE,

Col2 INTEGER SPARSE,

Col3 INTEGER SPARSE,

TblColumnSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);

 

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

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

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

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

 

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

FROM dbo.Table1;

 

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

Até mais.

Material de Apoio – Fevereiro – 2015


Hello comunidade, bom dia.

Graças a deus esta chovendo, que alegria….. Aproveitando o embalo das águas, estou novamente aqui no meu blog para compartilhar com vocês mais alguns arquivos da minha galeria neste sessão intitulada Material de Apoio!!!

Esta sessão em conjunto com a Short Scripts são as que mais apresentam interesse dos visitantes no modo geral, algo que me traz muito prazer, mas também a cada post um grande responsabilidade.

Muito bem, na relação de arquivos deste mês, destaco os seguintes recursos e funcionalidades:

  • BulkInsert com arquivo .fmt;
  • Common Table Expression em conjunto com Full Join;
  • Cuidados ao utilizar Funções: DateDiff, Convert, SubString;
  • Descobrindo se o Trigger é de Insert, Update, Delete;
  • Extended Stored Procedure – XP_FixedDrives;
  • Page Life Expectancy;
  • Server Memory;
  • Sys.dm_os_memory_clerks; e
  • User Stored Procedure – SP_FixedDrives.

Vale ressaltar, que alguns destes scripts são resultados de pesquisas realizadas na internet, aplicados em cenários de teste e homologação. Além isso, alguns scripts podem apresentar incompatibilidades com as versões mais antigas, dentre elas: 2005 e 2008.

Por padrão os scripts são compatíveis para as versões 2008 R2, 2012 e 2014, segue abaixo a relação de arquivos:

Exemplo – Avançado – Utilizando – Stored Procedure – sp_fixeddrives.sql

Exemplo – Básico – Descobrindo se o Trigger é Insert, Update ou Delete.sql

Exemplo – Básico – Utilizando a Extended Procedure – XP_fixeddrives.sql

Exemplo – Informações – Server Memory.sql

Exemplo – Intermediário – BulkInsert com arquivo de formato.fmt.sql

Exemplo – Intermediário – Cuidados – DATEDIFF, CONVERT, SUBSTRING e funções sobre colunas na cláusula WHERE e JOINs.sql

Exemplo – Obtendo informações – Page Life Expectancy.sql

Exemplo – Obtendo informações – Utilizando sys.dm_os_memory_clerks.sql

Exemplo – Utilizando CTE + Full Join.sql

Após fazer o download você poderá notar, que todos os arquivos possuem ao final do seu nome a extensão .doc, por questões de compatibilidade com a plataforma WordPress.com, basta remover esta extensão mantendo a extensão padrão do SQL Server .sql para que o arquivo possa ser aberto diretamente na ferramenta de banco de dados que você costuma utilizar.

Mais uma vez obrigado por sua visita.

Até a próxima.

Resposta – Script Challenge – Número 9


Salve pessoal…

Estou de volta, agora com a resposta para o Script Challenger – Número 9, postado a algumas a semanas. Bom, para aqueles que não se lembram do código postado, segue abaixo novamente o script utilizado neste desafio.

– Script Challenge – Número 9 –

Create Trigger T_Calcular_Horas
On CTProducao_Moinho
For Insert, Update
As
Declare @TotalDias VarChar(4),
@TotalHoras VarChar(3),
@TotalMinutos VarChar(4),
@NUMMO CHAR(7),
@ValorFormatado Char(5)

Set @NUMMO=(Select NUMMO from Inserted)

Set @TotalDias=(Select DateDiff(Day,DataInicio,DataFinal) from CTProducao_Moinho Where NUMMO=@NUMMO)
Set @TotalHoras=(Select DateDiff(Hour,Convert(Char(20),DataInicio,103)+HoraInicio,Convert(Char(20),DataFinal,103)+HoraFinal) from CTProducao_Moinho Where NUMMO=@NUMMO)
Set @TotalMinutos=(Select DateDiff(Minute,Convert(Char(20),DataInicio,103)+HoraInicio,Convert(Char(20),DataFinal,103)+HoraFinal) from CTProducao_Moinho Where NUMMO=@NUMMO)

If @TotalDias >=2
Begin
Update CTProducao_Moinho
Set TotalHoras=@TotalHoras+’:00′
Where NUMMO=@NUMMO
End
Else
Begin
If (@TotalHoras >=1) And (@TotalHoras <=24)
Begin
If (@TotalHoras = 1) And (@TotalMinutos < 60)
Set @ValorFormatado=’00:’+@TotalMinutos

If (Len(@TotalHoras) = 1) And (@TotalMinutos > 60)
Begin
Set @ValorFormatado=Convert(Char(4),Convert(Int,@TotalMinutos)/Convert(Int,@TotalHoras))
Set @ValorFormatado=’0′+@TotalHoras+’:’+@ValorFormatado
End

If (Len(@TotalHoras) = 1)
Set @ValorFormatado=’0′+@TotalHoras+’:00′

If (Len(@TotalHoras) = 1) And (@TotalMinutos = 60)
Set @ValorFormatado=’0′+@TotalHoras+’:00′

Update CTProducao_Moinho
Set TotalHoras=@ValorFormatado
Where NUMMO=@NUMMO
End
Else
Begin
If @TotalMinutos=60
Begin
Update CTProducao_Moinho
Set TotalHoras=’01:00′
Where NUMMO=@NUMMO
End
Else
Begin
If Len(@TotalMinutos) = 1
Set @ValorFormatado=’00:0′+@TotalMinutos
Else
Set @ValorFormatado=’00:’+@TotalMinutos

Update CTProducao_Moinho
Set TotalHoras = @ValorFormatado
Where NUMMO=@NUMMO
End
End
End

Pois bem, você conseguiu encontrar a respostas para este Código? Diversos participantes me enviaram por e-mail suas respostas, alias foram diversas, eu fiquei surpreso, pois este desafio apresentou o maior número de respostas obtidas até agora. Foram aproxidamente 100 respostas, com observações, dicas, críticas e comentários sobre o código.

Para aqueles que não participaram, a resposta deste Script Challenger – Número 9 é muito simples.

Como vocês podem observar, trata-se de um Trigger chamado T_Calcular_Horas vínculado a tabel CTProducao_Moinho, disparado na ocorrências de Inserts e Updates.

A função básica deste trigger e análisar através de algumas condições uma possível diferenção entre horas, minutos e segundos para formatar o valor de preenchimento do campo TotalHoras, utilizado em um ERP para controle de qualidade na área de produção de uma indústria que trabalhei a algum tempo.

De acordo com o valores de diferença, realizo a concatenação dos valores e posteriormente, a trigger executa um Update sobre a Table CTProducao_Moinho diretamente no campo TotalHoras.

Vale ressaltar que este script respeita algumas regras de negócios da empresa e também do ERP por isso, foi necessário realizar estas análises, conversões e concatenações de valores.

Muito bem galera, esta aqui a resposta, espero que todos possam ter entendido e gostado.

Nos encontramos nos próximos Script Challenger.

Até mais.