iMaster Developers

Wagner Crivelini

Wagner Crivelini

Consultor Sênior na Microsoft, na área de Data Insights para América Latina. Especialista em bancos de dados, é colunista em diversos portais de TI do Brasil e do exterior, com mais de 100 artigos técnicos publicados. É também co-produtor do DatabaseCast, primeiro podcast brasileiro sobre bancos de dados.
  • Publicou
    91
    Artigo(s)
    book
  • Visualizados
    828100
    Vezes
    eye
  • Desde
    2010
    clock
awardArtigos Publicados
Wagner Crivelini
Banco de Dados
Scripts SQL e Tabelas de Sistema - Parte 4: Atuando em Toda Instância

Fechando esta série sobre Banco de dados e Scripts SQL, apresento um caso mais trabalhoso, onde as consultas envolvem dados de toda instância. Isso quer…

Wagner Crivelini
Banco de Dados
Scripts SQL e Tabelas de Sistema - Parte 3: Arquivos de Log

O assunto desta vez é a geração de scripts SQL que se apliquem a múltiplos bancos de dados. É claro que scripts que afetam toda instância SQL são muito…

Wagner Crivelini
Banco de Dados
Scripts SQL e Tabelas de Sistema - Parte 2: Criando Índices

Hoje, apresento o caso em que as colunas que terão suas definições alteradas….. e como recriar índices associados a colunas usando tabelas SQL Server.

Wagner Crivelini
Banco de Dados
Scripts SQL e Tabelas de Sistema - Parte 1: Alterando Colunas

Todo administrador ou desenvolvedor de bancos de dados, cedo ou tarde, vai precisar criar scripts para executar múltiplas tarefas…. Scripts SQL

Wagner Crivelini
Data
O Caso das Consultas Lentas no Synapse

Um dos componentes do Azure Synapse Analytics é o recurso conhecido como SQL Pool, que funciona, em linhas gerais, como um grande servidor SQL. O Synapse SQL Pool usa uma arquitetura de Processamento Paralelo Massivo (ou MPP, na sigla em inglês), que lhe permite oferecer uma capacidade de processamento extremamente alta. Mas é importante lembrar…

Wagner Crivelini
SQL Server
Análise de Grafos no Azure SQL DB

O uso de bancos de dados de grafos continua crescendo rapidamente, mas ainda assim a grande maioria dos profissionais de TI desconhece os conceitos envolvidos. Hoje existem vários produtos desta categoria, tais como Azure Cosmos Gremlin API e NEO4J (clique aqui para informações sobre o mercado de bancos de dados de grafos). E existem também…

Wagner Crivelini
MySQL
O Caso do Índice Temporário

Este caso aconteceu na avaliação de um script SQL onde um simples caractere adicional no nome de um índice acabou causando muita discussão. O fato é que a documentação é um tanto vaga no quesito nomenclatura de índices, dando margem a entendimentos diferentes. Este artigo apresenta o estudo que foi feito para esclarecer a situação….

Wagner Crivelini
Banco de Dados
Azure SQL DB: Trabalhando com Listas e Arrays (parte 2)

No artigo anterior (link) falei sobre a leitura de listas de valores em arquivos JSON usando recursos do Azure SQL DB. Agora é a vez de tratar de listas de valores em arquivos texto (TXT, CSV etc.). Arquivos Texto e Listas Nos exemplos que apresentarei a seguir, eu uso um arquivo CSV com o mesmo…

Wagner Crivelini
SQL Server
Azure SQL DB: trabalhando com listas e arrays (Parte 1)

Listas, arrays, matrizes, vetores. Independente do nome que você prefira usar, cedo ou tarde vai ter que lidar com estes tipos de estrutura de dados. Estas não são estruturas comuns no mundo relacional, porém temos que ter em mente que hoje a diversidade de formatos e estruturas é uma regra no mundo corporativo. Mais que…

Wagner Crivelini
SQL Server
Tabelas Temporais Particionadas - Criando Auditoria de Dados em Tabelas Grandes

Desde a versão 2016 do SQL Server está disponível o recurso para auditoria automática de dados através das Tabelas Temporais.

visualizando 10 de 91
Publicidade
\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003C\u002Ftbody\u003E\r\n\u003C\u002Ftable\u003E\r\n\u003Cspan style=\"font-weight: 400;\"\u003EO output que vai gerar para a leitura do arquivo JSON apresentado na Tabela 1 é:\u003C\u002Fspan\u003E\r\n\u003Ctable\u003E\r\n\u003Ctbody\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cb\u003Ekey\u003C\u002Fb\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cb\u003Evalue\u003C\u002Fb\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cb\u003Etype\u003C\u002Fb\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EOrderNumber\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E574895677\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E1\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ECustomer\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EJohn Doe\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E1\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EAdress\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E[           {      \"Name\": \"Casa\",            \"Street\": \"Rua 5, no 42\",   ...  }      ]\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E4\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EProductID\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E[ \"AAF\",\"AAR\",\"AAS\",\"AAT\",\"AAV\",\"AAW\" ]\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E4\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003C\u002Ftbody\u003E\r\n\u003C\u002Ftable\u003E\r\n\u003Cspan style=\"font-weight: 400;\"\u003ECaso se deseje lidar com o esquema interno do JSON, substituímos o SELECT da linha 24 por uma declaração que reflita este esquema, como vemos a seguir.\u003C\u002Fspan\u003E\r\n\u003Ctable\u003E\r\n\u003Ctbody\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E01\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E02\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E03\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E04\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E05\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E06\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E07\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E08\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E09\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E10\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E11\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E12\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E13\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E14\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E15\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E16\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E17\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ESELECT\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E J\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E.\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003EOrderNumber\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E J\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E.\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003ECustomer\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E P\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E.\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003Evalue\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EAS\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E ProductID\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E A\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E.\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003EName\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E A\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E.\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003EStreet\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E A\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E.\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003ECity\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E A\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E.\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003EState\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003EFROM\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E  \u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003EOPENJSON \u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E @json\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003E' window.__SERVER_VARS__ = {"applicationRestUrl":"https:\u002F\u002Fadmin.imasters.com.br\u002Fwp-json","applicationBaseUrl":"https:\u002F\u002Fadmin.imasters.com.br"}\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E \u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003EWITH \u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E  OrderNumber\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EVARCHAR\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E200\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003E'$.OrderNumber'\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E \u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E Customer   \u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EVARCHAR\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E200\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003E'$.Customer'\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E Adress     \u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003ENVARCHAR\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003EMAX\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E'$.Adress'\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EAS\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EJSON\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E ProductID  \u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003ENVARCHAR\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003EMAX\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E'$.ProductID'\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EAS\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EJSON\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EAS\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E J\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003ECROSS\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EAPPLY\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EOPENJSON \u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003EJ\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E.\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003EProductID\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E' window.__SERVER_VARS__ = {"applicationRestUrl":"https:\u002F\u002Fadmin.imasters.com.br\u002Fwp-json","applicationBaseUrl":"https:\u002F\u002Fadmin.imasters.com.br"}\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E  P\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003ECROSS\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EAPPLY\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EOPENJSON \u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003EJ\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E.\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003EAdress\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E  \u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003EWITH \u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E  \u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003EName\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E         \u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EVARCHAR\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E200\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003E'$.Name'\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E \u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E Street       \u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EVARCHAR\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E200\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003E'$.Street'\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E \u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E City         \u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EVARCHAR\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E200\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003E'$.City'\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E \u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EState\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E        \u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EVARCHAR\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E200\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003E'$.State'\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E \u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EAS\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E A\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003C\u002Ftbody\u003E\r\n\u003C\u002Ftable\u003E\r\n\u003Cspan style=\"font-weight: 400;\"\u003EObserve que a primeira chamada OPENJSON define a estrutura principal do arquivo, estabelece as colunas OrderNumber, Customer, Adress e ProductID. Nessa definição, as duas últimas colunas foram classificadas como novos JSONs, de modo que se pudesse expandir seu conteúdo.\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003EAcontece que o tratamento dessas duas colunas “JSON” ocorre de forma distinta. Apesar de ambas invocarem o operador OPENJSON, a primeira delas, Adress, é uma lista de JSONs e por isso foi expandida em novas colunas, do mesmo modo que se tratou a estrutura principal.\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003ENo caso da coluna ProductID, trata-se de uma lista de valores associada à marcação “ProductID”. Isto requer um tratamento diferenciado, embora tenha solução simples e elegante. Aqui basta usar o operador OPENJSON e na cláusula SELECT apontar para a lista de valores usando a sintaxe “\u003C\u002Fspan\u003E\u003Ci\u003E\u003Cspan style=\"font-weight: 400;\"\u003EMeuAlias.value\u003C\u002Fspan\u003E\u003C\u002Fi\u003E\u003Cspan style=\"font-weight: 400;\"\u003E”. No exemplo acima, usei o alias “P”.\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003EO output dessa nova consulta é uma tabela de valores.\u003C\u002Fspan\u003E\r\n\u003Ctable\u003E\r\n\u003Ctbody\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cb\u003EOrderNumber\u003C\u002Fb\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cb\u003ECustomer\u003C\u002Fb\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cb\u003EProductID\u003C\u002Fb\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cb\u003EName\u003C\u002Fb\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cb\u003EStreet\u003C\u002Fb\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cb\u003ECity\u003C\u002Fb\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cb\u003EState\u003C\u002Fb\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E574895677\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EJohn Doe\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EAAF\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ECasa\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERua 5, no 42\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ESão Paulo\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ESP\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E574895677\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EJohn Doe\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EAAF\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EEscritorio\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERua D Pedro, no 500\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERio de Janeiro\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERJ\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E574895677\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EJohn Doe\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EAAR\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ECasa\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERua 5, no 42\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ESão Paulo\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ESP\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E574895677\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EJohn Doe\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EAAR\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EEscritorio\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERua D Pedro, no 500\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERio de Janeiro\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERJ\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E574895677\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EJohn Doe\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EAAS\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ECasa\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERua 5, no 42\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ESão Paulo\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ESP\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E574895677\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EJohn Doe\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EAAS\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EEscritorio\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERua D Pedro, no 500\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERio de Janeiro\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERJ\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E574895677\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EJohn Doe\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EAAT\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ECasa\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERua 5, no 42\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ESão Paulo\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ESP\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E574895677\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EJohn Doe\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EAAT\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EEscritorio\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERua D Pedro, no 500\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERio de Janeiro\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERJ\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E574895677\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EJohn Doe\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EAAV\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ECasa\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERua 5, no 42\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ESão Paulo\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ESP\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E574895677\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EJohn Doe\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EAAV\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EEscritorio\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERua D Pedro, no 500\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERio de Janeiro\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERJ\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E574895677\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EJohn Doe\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EAAW\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ECasa\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERua 5, no 42\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ESão Paulo\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ESP\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E574895677\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EJohn Doe\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EAAW\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003EEscritorio\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERua D Pedro, no 500\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERio de Janeiro\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ERJ\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003C\u002Ftbody\u003E\r\n\u003C\u002Ftable\u003E\r\n\u003Ch3\u003E\u003Cb\u003EAvaliando a Performance\u003C\u002Fb\u003E\u003C\u002Fh3\u003E\r\n\u003Cspan style=\"font-weight: 400;\"\u003EDe modo geral, a performance das operações envolvendo operador OPENJSON não é boa. Para entender melhor este impacto, vamos avaliar três situações diferentes. \u003C\u002Fspan\u003E\r\n\u003Ch3\u003E\u003Cb\u003ECaso 1\u003C\u002Fb\u003E\u003C\u002Fh3\u003E\r\n\u003Cspan style=\"font-weight: 400;\"\u003EAqui consideramos o plano de execução da consulta que gerou a listagem apresentada acima. O procedimento envolve duas consultas, sendo que a segunda, que trata da formatação do JSON, responde por 96% de todo custo do processo: 0,6605. O tempo de execução foi de 01 segundo.\u003C\u002Fspan\u003E\r\n\u003Ch3\u003E\u003Cimg class=\"alignnone size-full wp-image-156777\" src=\"https:\u002F\u002Fadmin.imasters.com.br\u002Fwp-content\u002Fuploads\u002F2022\u002F01\u002Fcaso1.png\" alt=\"\" width=\"585\" height=\"435\" \u002F\u003E\u003C\u002Fh3\u003E\r\n\u003Ch3\u003E\u003Cb\u003ECaso 2\u003C\u002Fb\u003E\u003C\u002Fh3\u003E\r\n\u003Cspan style=\"font-weight: 400;\"\u003EDesta vez vamos consultar o mesmo arquivo JSON, mas vamos desconsiderar a lista de JSONs da marcação “Adress”. A primeira parte do procedimento continua idêntica, mas segunda vai usar a seguinte declaração:\u003C\u002Fspan\u003E\r\n\u003Ctable\u003E\r\n\u003Ctbody\u003E\r\n\u003Ctr\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003E01\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E02\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E03\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E04\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E05\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E06\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E07\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E08\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E09\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003Ctd\u003E\u003Cspan style=\"font-weight: 400;\"\u003ESELECT\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E J\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E.\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003EOrderNumber\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E J\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E.\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003ECustomer\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E P\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E.\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003Evalue\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EAS\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E ProductID\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003EFROM\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E  \u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003EOPENJSON \u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E @json\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003E' window.__SERVER_VARS__ = {"applicationRestUrl":"https:\u002F\u002Fadmin.imasters.com.br\u002Fwp-json","applicationBaseUrl":"https:\u002F\u002Fadmin.imasters.com.br"}\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E \u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003EWITH \u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E  OrderNumber\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EVARCHAR\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E200\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003E'$.OrderNumber'\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E \u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E Customer   \u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EVARCHAR\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E200\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003E'$.Customer'\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E Adress     \u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003ENVARCHAR\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003EMAX\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E'$.Adress'\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EAS\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EJSON\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E ProductID  \u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003ENVARCHAR\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003EMAX\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E'$.ProductID'\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EAS\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EJSON\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EAS\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E J\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003ECROSS\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EAPPLY\u003C\u002Fspan\u003E \u003Cspan style=\"font-weight: 400;\"\u003EOPENJSON \u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E(\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003EJ\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E.\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003EProductID\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E,\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E' window.__SERVER_VARS__ = {"applicationRestUrl":"https:\u002F\u002Fadmin.imasters.com.br\u002Fwp-json","applicationBaseUrl":"https:\u002F\u002Fadmin.imasters.com.br"}\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E)\u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E  P\u003C\u002Fspan\u003E\u003C\u002Ftd\u003E\r\n\u003C\u002Ftr\u003E\r\n\u003C\u002Ftbody\u003E\r\n\u003C\u002Ftable\u003E\r\n\u003Cspan style=\"font-weight: 400;\"\u003ENeste caso, vamos converter em tabela a lista de valores da coluna ProductID. O custo da primeira consulta é exatamente o mesmo do caso anterior, porém a segunda consulta roda 50 vezes mais rápido, tendo seu custo reduzido para 0.0130 (contra 0,6605 no Caso1)!!! O tempo de execução praticamente não mudou (01 seg).\u003C\u002Fspan\u003E\r\n\r\n\u003Cimg class=\"alignnone size-full wp-image-156778\" src=\"https:\u002F\u002Fadmin.imasters.com.br\u002Fwp-content\u002Fuploads\u002F2022\u002F01\u002Fcaso2.png\" alt=\"\" width=\"587\" height=\"441\" \u002F\u003E\r\n\r\n\u003Cb\u003ECaso 3\u003C\u002Fb\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003EAgora vamos avaliar uma expansão do Caso 2. Usaremos outro arquivo JSON com mesmo esquema, porém dessa vez o campo ProductID tratará uma lista com 100 mil valores ao invés de apenas 6.\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003EA única alteração no procedimento é reapontar o script para ler o arquivo “exemplo2.json”, cujo tamanho é de 811 Kb, ao invés do arquivo “exemplo1.json”, de 445 bytes.\u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003EObserve no plano de execução abaixo que, apesar de estar lendo um arquivo quase 2 mil vezes maior, os custos da primeira e segunda consultas são exatamente os mesmos que observamos no Caso 2! O tempo de execução subiu, mas muito pouco: apenas 06 segundos.\u003C\u002Fspan\u003E\r\n\r\n\u003Cb\u003E\u003Cimg class=\"alignnone size-full wp-image-156779\" src=\"https:\u002F\u002Fadmin.imasters.com.br\u002Fwp-content\u002Fuploads\u002F2022\u002F01\u002Fcaso3.png\" alt=\"\" width=\"588\" height=\"439\" \u002F\u003E\r\nComentários Finais\u003C\u002Fb\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003EComo vimos neste estudo, a preparação para acesso a arquivos JSON armazenados em contas de armazenamento requer alguns cuidados especiais. Porém, uma vez configurados, a leitura desses arquivos no Azure SQL DB funciona muito bem. \u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003ESobre a leitura de listas de valores em formato JSON, os dois pontos que se destacam são:\u003C\u002Fspan\u003E\r\n\u003Col\u003E\r\n \t\u003Cli style=\"font-weight: 400;\" aria-level=\"1\"\u003E\u003Cspan style=\"font-weight: 400;\"\u003EA sintaxe usada para leitura dessas estruturas é bastante simples. Basta definir a coluna como JSON, abri-la com o operador OPENJSON e apontar para seus valores com a expressão \u003C\u002Fspan\u003E\u003Cspan style=\"font-weight: 400;\"\u003E“\u003C\u002Fspan\u003E\u003Ci\u003E\u003Cspan style=\"font-weight: 400;\"\u003EMeuAlias.value\u003C\u002Fspan\u003E\u003C\u002Fi\u003E\u003Cspan style=\"font-weight: 400;\"\u003E”.\u003C\u002Fspan\u003E\u003C\u002Fli\u003E\r\n \t\u003Cli style=\"font-weight: 400;\" aria-level=\"1\"\u003E\u003Cspan style=\"font-weight: 400;\"\u003EO custo de execução de consultas envolvendo listas de valores é praticamente invariável. O impacto do tamanho da lista na performance da operação é muito menor do que se poderia esperar.\u003C\u002Fspan\u003E\u003C\u002Fli\u003E\r\n\u003C\u002Fol\u003E\r\n\u003Cspan style=\"font-weight: 400;\"\u003ENo próximo artigo, a conversa é sobre o uso de listas em arquivos CSV. \u003C\u002Fspan\u003E\r\n\r\n\u003Cspan style=\"font-weight: 400;\"\u003EAté lá!\u003C\u002Fspan\u003E","excerpt":"\u003Cp\u003EListas, arrays, matrizes, vetores. Independente do nome que você prefira usar, cedo ou tarde vai ter que lidar com estes tipos de estrutura de dados. Estas não são estruturas comuns no mundo relacional, porém temos que ter em mente que hoje a diversidade de formatos e estruturas é uma regra no mundo corporativo. Mais que…\u003C\u002Fp\u003E\n","link":"https:\u002F\u002Fimasters.com.br\u002Fsql-server\u002Fazure-sql-db-trabalhando-com-listas-e-arrays-parte-1","date":"11 jan, 2022","thumbnail":"https:\u002F\u002Fstatic.imasters.com.br\u002Fwp-content\u002Fuploads\u002F2022\u002F01\u002F11075531\u002FNews-Blog-Graphic.jpg","externalMention":"","author":{"id":"452","thumbnail":"https:\u002F\u002Fstatic.imasters.com.br\u002Fwp-content\u002Fuploads\u002F2018\u002F05\u002F30184745\u002FwagnercriveliniP-128x128.jpeg","name":"Wagner Crivelini","description":"Consultor Sênior na Microsoft, na área de Data Insights para América Latina. Especialista em bancos de dados, é colunista em diversos portais de TI do Brasil e do exterior, com mais de 100 artigos técnicos publicados. É também co-produtor do DatabaseCast, primeiro podcast brasileiro sobre bancos de dados.","slug":"wagner-crivelini","url":"https:\u002F\u002Fimasters.com.br\u002Fperfil\u002Fwagner-crivelini","registered":"2010-12-30 11:28:37","social":{"linkedin":null,"youtube":null,"facebook":"","twitter":"https:\u002F\u002Ftwitter.com\u002Fwcrivelini@gmail.com","instagram":null,"github":null,"url":"http:\u002F\u002Fwww.wcrivelini.com.br","mail":"wcrivelini@gmail.com"},"articles_count":91,"views_count":828100,"certifications":null,"conquests":null,"office":null},"categories":[{"title":"SQL Server","slug":"sql-server","id":7212,"link":"https:\u002F\u002Fimasters.com.br\u002Fsql-server"}],"tags":[{"title":"AZURE SQL DB","slug":"azure-sql-db","id":8875,"link":"https:\u002F\u002Fimasters.com.br\u002Fazure-sql-db"},{"title":"sql server","slug":"sql-server-2","id":324,"link":"https:\u002F\u002Fimasters.com.br\u002Fsql-server-2"}],"relateds":[],"seo":[],"type":"post"},{"id":155851,"title":"Tabelas Temporais Particionadas - Criando Auditoria de Dados em Tabelas Grandes","content":"Desde a versão 2016 do SQL Server está disponível o recurso para auditoria automática de dados através das Tabelas Temporais.\r\n\r\nEu mesmo publiquei em 2018 aqui no iMasters \u003Ca href=\"https:\u002F\u002Fimasters.com.br\u002Fdata\u002Fsql-server-auditando-dados-com-tabelas-temporais\"\u003Eum artigo tratando deste recurso\u003C\u002Fa\u003E que, além de útil, é muito fácil de implementar.\r\n\r\nRecentemente precisei implementar este recurso em um cliente. Porém, neste caso, tratava-se de uma tabela com mais de 2 bilhões de registros, o que exigiu alguns cuidados especiais, como descrevo a seguir.\r\n\u003Ch2\u003E\u003Cstrong\u003EComo Lidar com Tabelas Grandes\u003C\u002Fstrong\u003E\u003C\u002Fh2\u003E\r\nUma solução muito usada para melhorar performance de tabelas com muitos registros é o uso de particionamento. Também tratei deste tema aqui no iMasters, em \u003Ca href=\"https:\u002F\u002Fimasters.com.br\u002Fdata\u002Ftrabalhando-com-tabelas-particionadas\"\u003Eum artigo de 2017\u003C\u002Fa\u003E.\r\n\r\nA ideia é subdividir a tabela em inúmeros “blocos” menores. Na prática, vemos a tabela como um só componente e a tratamos como se fosse uma tabela normal. Mas fisicamente ela está segmentada em N partes, sendo que os dados serão alocados em cada partição segundo o valor de uma coluna que chamamos de chave de particionamento.\r\n\r\nNão é preciso dizer que o bom ou mau desempenho das operações com estas tabelas depende muito da escolha da chave de particionamento.\r\n\r\nÉ comum considerar colunas com datas como candidatas para chave de particionamento. Assim, datas próximas tenderão a ser agrupadas na mesma partição.\r\n\r\nUm efeito desta escolha é que novos dados tenderão a ser escritos na mesma partição. Em função disso, neste caso teremos uma partição “quente” para escrita, enquanto todas as demais só terão acesso de leitura.\r\n\u003Ch2\u003EComo Implementar Auditoria de Dados\u003C\u002Fh2\u003E\r\nApesar de simples, a auditoria oferecida pelas tabelas temporais é bastante eficiente e, além de tudo, ocorre automaticamente.\r\n\r\nAo se criar uma tabela temporal, é automaticamente gerada uma tabela histórica com as mesmas colunas que a original.\r\n\r\nQuando ocorre uma atualização ou exclusão de dados da tabela principal (a tabela temporal), o registro antigo é movido para a tabela secundária (chamada tabela histórica).\r\n\r\nAli veremos que este registro terá uma data de validade inicial: \u003Cstrong\u003ESysStartTime\u003C\u002Fstrong\u003E. Esta data informa quando aquele registro sofreu sua ação, seja ela uma inserção ou alteração. Existe também a data em que aquela versão do registro foi “eliminada”: \u003Cstrong\u003ESysEndTime\u003C\u002Fstrong\u003E, que trata de quando o registro foi alterado ou excluído da tabela\r\ntemporal.\r\n\r\nQuando o registro é inserido, ele é escrito na tabela temporal tendo como \u003Cstrong\u003ESysStartTime\u003C\u002Fstrong\u003E a data em que ocorreu este evento. E, como se poderia prever, a coluna \u003Cstrong\u003ESysEndTime\u003C\u002Fstrong\u003E terá o valor NULO. Porém, nada acontece na tabela histórica, visto que esta é a primeira versão do registro.\r\n\r\nQuando tal registro for alterado ou excluído, a versão antiga é movida para a tabela histórica, informando em \u003Cstrong\u003ESysEndTime\u003C\u002Fstrong\u003E a data em que esta versão foi “eliminada”. E tudo isso é controlado automaticamente pelo mecanismo do SQL Server.\r\n\u003Ch2\u003ETabela Temporal Particionada\u003C\u002Fh2\u003E\r\nQual problema pode acontecer ao se criar uma tabela temporal particionada? Normalmente, se estamos particionando uma tabela por ser muito grande, provavelmente a tabela histórica associada a ela também será muito grande. Consequentemente, é altamente recomendável que a tabela histórica também seja\r\nparticionada.\r\n\r\nPorém, se executarmos o processo normal de criação de uma tabela temporal, será gerada uma tabela histórica sem partições. Não importa que seu script esteja particionando a tabela temporal. A histórica não será convertida automaticamente em tabela particionada.\r\n\r\nSendo assim, é necessária uma pequena adaptação do processo, criando primeiramente a tabela histórica com as partições. E depois se cria a tabela temporal particionada que irá apontar para a tabela histórica que já se criou.\r\nÍndices adicionais que sejam criados para a tabela temporal e\u002Fou tabela histórica devem igualmente ser particionados.\r\n\r\nDesta maneira, seu ambiente estará adequado às necessidades e à escala do seu banco de dados.\r\n\u003Ch2\u003ECriando os Objetos\u003C\u002Fh2\u003E\r\n\u003Cp id=\"E495\" class=\"x-scope qowt-word-para-1\"\u003E\u003Cspan id=\"E496\" class=\"qowt-font10-Arial\"\u003EO que dá mais trabalho na criação dessa solução é a \u003C\u002Fspan\u003E\u003Cspan id=\"E497\" class=\"qowt-font10-Arial\"\u003Esequência\u003C\u002Fspan\u003E\u003Cspan id=\"E498\" class=\"qowt-font10-Arial\"\u003E de tarefas a serem executadas.\u003C\u002Fspan\u003E\u003C\u002Fp\u003E\r\n\u003Cp id=\"E499\" class=\"x-scope qowt-word-para-1\"\u003E\u003Cspan id=\"E500\" class=\"qowt-font10-Arial\"\u003EEm primeiro lugar, é preciso criar o \u003C\u002Fspan\u003E\u003Cspan id=\"E501\" class=\"qowt-font10-Arial\"\u003Efilegroups\u003C\u002Fspan\u003E\u003Cspan id=\"E502\" class=\"qowt-font10-Arial\"\u003E que serão usados.\u003C\u002Fspan\u003E\u003Cspan id=\"E503\" class=\"qowt-font10-Arial\"\u003E \u003C\u002Fspan\u003E\u003Cspan id=\"E504\" class=\"qowt-font10-Arial\"\u003EQuando se trabalha com o SQL Server nativo, é\u003C\u002Fspan\u003E\u003Cspan id=\"E505\" class=\"qowt-font10-Arial\"\u003E altamente recomendável que se use um \u003C\u002Fspan\u003E\u003Cspan id=\"E506\" class=\"qowt-font10-Arial\"\u003Efilegroup\u003C\u002Fspan\u003E\u003Cspan id=\"E507\" class=\"qowt-font10-Arial\"\u003E para cada partição para garantir melhor gerenciamento do banco de dados.\u003C\u002Fspan\u003E\u003Cspan id=\"E508\" class=\"qowt-font10-Arial\"\u003E (No caso d\u003C\u002Fspan\u003E\u003Cspan id=\"E509\" class=\"qowt-font10-Arial\"\u003Eo\u003C\u002Fspan\u003E\u003Cspan id=\"E510\" class=\"qowt-font10-Arial\"\u003E \u003C\u002Fspan\u003E\u003Cspan id=\"E511\" class=\"qowt-font10-Arial\"\u003EAzure SQL Database\u003C\u002Fspan\u003E\u003Cspan id=\"E512\" class=\"qowt-font10-Arial\"\u003E, que é \u003C\u002Fspan\u003E\u003Cspan id=\"E513\" class=\"qowt-font10-Arial\"\u003Eum serviço PAAS\u003C\u002Fspan\u003E\u003Cspan id=\"E514\" class=\"qowt-font10-Arial\"\u003E, não temos controle sobre \u003C\u002Fspan\u003E\u003Cspan id=\"E515\" class=\"qowt-font10-Arial\"\u003Eos \u003C\u002Fspan\u003E\u003Cspan id=\"E516\" class=\"qowt-font10-Arial\"\u003Edatafiles\u003C\u002Fspan\u003E\u003Cspan id=\"E517\" class=\"qowt-font10-Arial\"\u003E \u003C\u002Fspan\u003E\u003Cspan id=\"E518\" class=\"qowt-font10-Arial\"\u003Eusados, por isso normalmente usamos o \u003C\u002Fspan\u003E\u003Cspan id=\"E519\" class=\"qowt-font10-Arial\"\u003Efilegroup\u003C\u002Fspan\u003E\u003Cspan id=\"E520\" class=\"qowt-font10-Arial\"\u003E padrão, o PRIMARY)\u003C\u002Fspan\u003E\u003C\u002Fp\u003E\r\n\r\n\u003Cpre\u003E\u003Ccode class=\"language-sql\"\u003E--Criando filegroups\r\n\r\nUSE MASTER\r\n\r\nGO\r\n\r\n\r\n\r\nALTER DATABASE MeuBD ADD FILEGROUP fg2018 ;\r\n\r\nALTER DATABASE MeuBD ADD FILEGROUP fg2019 ;\r\n\r\nALTER DATABASE MeuBD ADD FILEGROUP fg2020 ;\r\n\r\nALTER DATABASE MeuBD ADD FILEGROUP fg2021 ;\r\n\r\nALTER DATABASE MeuBD ADD FILEGROUP fg2022 ;\r\n\r\nALTER DATABASE MeuBD ADD FILEGROUP fg2023 ;\r\n\r\nGO\u003C\u002Fcode\u003E\u003C\u002Fpre\u003E\r\nNo passo seguinte, criamos os datafiles usados por cada partição.\r\n\r\n \r\n\u003Cpre\u003E\u003Ccode class=\"language-sql\"\u003E-- define 1 or more datafile per partition\r\n\r\nUSE MASTER\r\n\r\nGO\r\n\r\n\r\n\r\nALTER DATABASE meuBD ADD FILE ( NAME = N'part2018_1', \r\n\r\n FILENAME = N'C:\\Data\\meuDB_part2018_1.ndf' , \r\n\r\n SIZE = 100MB , MAXSIZE = 100GB , FILEGROWTH = 100MB \r\n\r\n) TO FILEGROUP [fg2018];\r\n\r\n\r\n\r\nALTER DATABASE meuBD ADD FILE ( NAME = N'part2019_1', \r\n\r\n FILENAME = N'C:\\Data\\meuBD_part2019_1.ndf' , \r\n\r\n SIZE = 100MB , MAXSIZE = 100GB , FILEGROWTH = 100MB \r\n\r\n) TO FILEGROUP [fg2019];\r\n\r\n\r\n\r\nALTER DATABASE meuBD ADD FILE ( NAME = N'part2020_1', \r\n\r\n FILENAME = N'C:\\Data\\meuBD_part2020_1.ndf' , \r\n\r\n SIZE = 100MB , MAXSIZE = 100GB , FILEGROWTH = 100MB \r\n\r\n) TO FILEGROUP [fg2020];\r\n\r\n\r\n\r\nALTER DATABASE meuBD ADD FILE ( NAME = N'part2021_1', \r\n\r\n FILENAME = N'C:\\Data\\meuBD_part2021_1.ndf' , \r\n\r\n SIZE = 100MB , MAXSIZE = 100GB , FILEGROWTH = 100MB \r\n\r\n) TO FILEGROUP [fg2021];\r\n\r\n\r\n\r\nALTER DATABASE meuBD ADD FILE ( NAME = N'part2022_1', \r\n\r\n FILENAME = N'C:\\Data\\meuBD_part2022_1.ndf' , \r\n\r\n SIZE = 100MB , MAXSIZE = 100GB , FILEGROWTH = 100MB \r\n\r\n) TO FILEGROUP [fg2022] ;\r\n\r\n\r\n\r\nALTER DATABASE meuBD ADD FILE ( NAME = N'part2023_1', \r\n\r\n FILENAME = N'C:\\Data\\meuBD_part2023_1.ndf' , \r\n\r\n SIZE = 100MB , MAXSIZE = 100GB , FILEGROWTH = 100MB \r\n\r\n) TO FILEGROUP [fg2023];\r\n\r\nGO\u003C\u002Fcode\u003E\u003C\u002Fpre\u003E\r\n \r\n\r\n\u003Cspan id=\"E926\" class=\"qowt-font10-Arial\"\u003ENo passo 3, se define \u003C\u002Fspan\u003E\u003Cspan id=\"E927\" class=\"qowt-font10-Arial\"\u003Ea função de particionamento.\u003C\u002Fspan\u003E\r\n\u003Cpre\u003E\u003Ccode class=\"language-sql\"\u003E--Funcao de particionamento\r\n\r\nUSE meuBD\r\n\r\nGO\r\n\r\n\r\n\r\nCREATE PARTITION FUNCTION [fnPartitioningByYear](DATETIME) AS RANGE RIGHT FOR VALUES \r\n\r\n\t(\r\n\r\n\t -- tipo de particionamento: “RANGE RIGHT”\r\n\r\n\t --NAO INFORMAR o primeiro intervalo (01\u002Fjan a 31\u002Fdez\u002F2018) \r\n\r\n\t --dados anteriores a 01\u002Fjan\u002F2019 serao gravados naquela particao\r\n\r\n\t '20190101 00:00:00'\r\n\r\n\t, '20200101 00:00:00'\r\n\r\n\t, '20210101 00:00:00'\r\n\r\n\t, '20220101 00:00:00'\r\n\r\n\t, '20230101 00:00:00'\r\n\r\n\t)\r\n\r\nGO\u003C\u002Fcode\u003E\u003C\u002Fpre\u003E\r\n \r\n\r\nAgora é a vez de criar o esquema de particionamento dos dados.\r\n\u003Cpre\u003E\u003Ccode class=\"language-sql\"\u003E-- Esquema de particionamento \r\n\r\nUSE meuBD\r\n\r\nGO\r\n\r\n\r\n\r\nCREATE PARTITION SCHEME [schPartitioningByYear]\r\n\r\nAS PARTITION [fnPartitioningByYear]\r\n\r\nTO ( fg2018, fg2019, fg2020, fg2021, fg2022, fg2023)\r\n\r\nGO\u003C\u002Fcode\u003E\u003C\u002Fpre\u003E\r\n \r\n\r\n\u003Cspan id=\"E1052\" class=\"qowt-font10-Arial\"\u003EFinalmente podemos criar a primeira tabela, que\u003C\u002Fspan\u003E\u003Cspan id=\"E1053\" class=\"qowt-font10-Arial\"\u003E no nosso caso\u003C\u002Fspan\u003E\u003Cspan id=\"E1054\" class=\"qowt-font10-Arial\"\u003E dever ser\u003C\u002Fspan\u003E\u003Cspan id=\"E1055\" class=\"qowt-font10-Arial\"\u003E necessariamente a tabela histórica. Como esta será também uma tabela particionada, é \u003C\u002Fspan\u003E\u003Cspan id=\"E1056\" class=\"qowt-font10-Arial\"\u003Erequerido que a chave de particionamento faça parte de um índice clusterizado ou chave primária.\u003C\u002Fspan\u003E\r\n\u003Cpre\u003E\u003Ccode class=\"language-sql\"\u003E-- Criar tabela historica\r\n\r\nUSE meuBD\r\n\r\nGO\r\n\r\n\r\n\r\nCREATE TABLE test_temporal_HISTORY(\r\n\r\n id bigint NOT NULL,\r\n\r\n initdate datetime NOT NULL,\r\n\r\n SysStartTime datetime2(7) NOT NULL,\r\n\r\n SysEndTime datetime2(7) NOT NULL\r\n\r\n) ON [schPartitioningByYear](initdate)\r\n\r\n\r\n\r\nGO\r\n\r\n\r\n\r\nCREATE CLUSTERED INDEX ix_test_temporal_History \r\n\r\nON test_temporal_HISTORY (id , initdate)\r\n\r\nGO\r\n\r\n\u003C\u002Fcode\u003E\u003C\u002Fpre\u003E\r\n\u003Cspan id=\"E1142\" class=\"qowt-font10-Arial\"\u003EAgora, finalmente, definimos a tabela temporal, com sua sintaxe especial. Note que estamos apontando para uma tabela que já existe\u003C\u002Fspan\u003E\u003Cspan id=\"E1144\" class=\"qowt-font10-Arial\"\u003E (e que usa o mesmo modelo de colunas e tipos de dados). Além disso, ela também está associada a um\u003C\u002Fspan\u003E\u003Cspan id=\"E1145\" class=\"qowt-font10-Arial\"\u003E esquema de particionamento.\u003C\u002Fspan\u003E\r\n\u003Cpre\u003E\u003Ccode class=\"language-sql\"\u003E-- Criar tabela temporal\r\n\r\nUSE meuBD\r\n\r\nGO\r\n\r\n\r\n\r\nCREATE TABLE dbo.test_temporal(\r\n\r\n id bigint NOT NULL,\r\n\r\n initdate datetime NOT NULL,\r\n\r\n SysStartTime datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,\r\n\r\n SysEndTime datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,\r\n\r\nCONSTRAINT PK_test PRIMARY KEY CLUSTERED \r\n\r\n(\r\n\r\n id ASC,\r\n\r\n initdate\r\n\r\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ,\r\n\r\n PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])\r\n\r\n) ON [schPartitioningByYear](initdate)\r\n\r\nWITH\r\n\r\n(\r\n\r\nSYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.test_temporal_HISTORY )\r\n\r\n)\r\n\r\nGO\u003C\u002Fcode\u003E\u003C\u002Fpre\u003E\r\n\u003Cspan id=\"E1335\" class=\"qowt-font10-Arial\"\u003EFinalmente temos o ambiente pronto para uso. A consulta a seguir verifica \u003C\u002Fspan\u003E\u003Cspan id=\"E1336\" class=\"qowt-font10-Arial\"\u003Equal o tipo de cada tabela e \u003C\u002Fspan\u003E\u003Cspan id=\"E1337\" class=\"qowt-font10-Arial\"\u003Ese \u003C\u002Fspan\u003E\u003Cspan id=\"E1338\" class=\"qowt-font10-Arial\"\u003Eelas são particionadas ou não.\u003C\u002Fspan\u003E\r\n\u003Cpre\u003E\u003Ccode class=\"language-sql\"\u003E--verificacao\r\n\r\nUSE meuBD\r\n\r\nGO\r\n\r\n\r\n\r\nSELECT db_name() + '.' + OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS TAB\r\n\r\n\t, MIN(temporal_type_desc) AS temporal_type_desc, CASE WHEN MAX(pstat.partition_number) > 1 THEN 'PARTITIONED' ELSE 'REGULAR' END AS Partition_Type, MAX(pstat.partition_number) AS Partition_count \r\n\r\nFROM sys.dm_db_partition_stats pstat\r\n\r\n\tINNER JOIN sys.indexes i ON pstat.object_id = i.object_id AND pstat.index_id = i.index_id\r\n\r\n\tLEFT JOIN sys.partition_schemes ps on ps.data_space_id = i.data_space_id \r\n\r\n\tLEFT JOIN sys.partition_range_values rv on rv.function_id = ps.function_id AND rv.boundary_id = pstat.partition_number\r\n\r\n\tLEFT JOIN sys.tables t on i.object_id = t.object_id \r\n\r\nWHERE OBJECT_SCHEMA_NAME(i.object_id) <> 'sys' \r\n\r\nGROUP BY i.object_id\r\n\r\nHAVING MAX(pstat.partition_number) > 1\r\n\r\nGO\u003C\u002Fcode\u003E\u003C\u002Fpre\u003E\r\n\u003Ca href=\"https:\u002F\u002Fadmin.imasters.com.br\u002Fwp-content\u002Fuploads\u002F2020\u002F12\u002FTABELAa.jpg.jpg\"\u003E\u003Cimg class=\"alignnone size-full wp-image-155855\" src=\"https:\u002F\u002Fadmin.imasters.com.br\u002Fwp-content\u002Fuploads\u002F2020\u002F12\u002FTABELAa.jpg.jpg\" alt=\"\" width=\"588\" height=\"111\" \u002F\u003E\u003C\u002Fa\u003E\r\n\u003Ch2 id=\"E1618\" class=\"qowt-stl-Heading3 x-scope qowt-word-para-3\"\u003E\u003Cstrong\u003E\u003Cspan id=\"E1619\"\u003ECo\u003C\u002Fspan\u003E\u003Cspan id=\"E1620\"\u003Ementários Finais\u003C\u002Fspan\u003E\u003C\u002Fstrong\u003E\u003C\u002Fh2\u003E\r\n\u003Cp id=\"E1621\" class=\"x-scope qowt-word-para-1\"\u003E\u003Cspan id=\"E1622\" class=\"qowt-font10-Arial\"\u003EA implementação de tabelas temporais particionadas, como se pode ver, é simples\u003C\u002Fspan\u003E\u003Cspan id=\"E1623\" class=\"qowt-font10-Arial\"\u003E.\u003C\u002Fspan\u003E\u003C\u002Fp\u003E\r\n\u003Cp id=\"E1624\" class=\"x-scope qowt-word-para-1\"\u003E\u003Cspan id=\"E1625\" class=\"qowt-font10-Arial\"\u003EA questão principal aqui é se dar conta da necessidade de particionamento da tabela histórica\u003C\u002Fspan\u003E\u003Cspan id=\"E1626\" class=\"qowt-font10-Arial\"\u003E. Se não tomarmos providências esta\u003C\u002Fspan\u003E\u003Cspan id=\"E1627\" class=\"qowt-font10-Arial\"\u003E \u003C\u002Fspan\u003E\u003Cspan id=\"E1628\" class=\"qowt-font10-Arial\"\u003Etabela\u003C\u002Fspan\u003E\u003Cspan id=\"E1629\" class=\"qowt-font10-Arial\"\u003E ser\u003C\u002Fspan\u003E\u003Cspan id=\"E1630\" class=\"qowt-font10-Arial\"\u003Eá\u003C\u002Fspan\u003E\u003Cspan id=\"E1631\" class=\"qowt-font10-Arial\"\u003E criada automaticamente sem partição nenhuma\u003C\u002Fspan\u003E\u003Cspan id=\"E1632\" class=\"qowt-font10-Arial\"\u003E e muito provavelmente o assunto será esquecido.\u003C\u002Fspan\u003E\u003Cspan id=\"E1633\" class=\"qowt-font10-Arial\"\u003E \u003C\u002Fspan\u003E\u003C\u002Fp\u003E\r\n\u003Cp id=\"E1634\" class=\"x-scope qowt-word-para-1\"\u003E\u003Cspan id=\"E1635\" class=\"qowt-font10-Arial\"\u003ENeste cenário, o\u003C\u002Fspan\u003E\u003Cspan id=\"E1636\" class=\"qowt-font10-Arial\"\u003E problema só ser\u003C\u002Fspan\u003E\u003Cspan id=\"E1637\" class=\"qowt-font10-Arial\"\u003Eá\u003C\u002Fspan\u003E\u003Cspan id=\"E1638\" class=\"qowt-font10-Arial\"\u003E descoberto quando a solução já estive\u003C\u002Fspan\u003E\u003Cspan id=\"E1639\" class=\"qowt-font10-Arial\"\u003Er\u003C\u002Fspan\u003E\u003Cspan id=\"E1640\" class=\"qowt-font10-Arial\"\u003E em produção\u003C\u002Fspan\u003E\u003Cspan id=\"E1641\" class=\"qowt-font10-Arial\"\u003E e começar a haver problemas de performance para consultas da tabela histórica.\u003C\u002Fspan\u003E\u003Cspan id=\"E1642\" class=\"qowt-font10-Arial\"\u003E \u003C\u002Fspan\u003E\u003Cspan id=\"E1643\" class=\"qowt-font10-Arial\"\u003EE então os esforços necessários para correção serão muito maiores e mais impactantes.\u003C\u002Fspan\u003E\u003C\u002Fp\u003E","excerpt":"\u003Cp\u003EDesde a versão 2016 do SQL Server está disponível o recurso para auditoria automática de dados através das Tabelas Temporais.\u003C\u002Fp\u003E\n","link":"https:\u002F\u002Fimasters.com.br\u002Fsql-server\u002Ftabelas-temporais-particionadas-criando-auditoria-de-dados-em-tabelas-grandes","date":"5 jan, 2021","thumbnail":"https:\u002F\u002Fstatic.imasters.com.br\u002Fwp-content\u002Fuploads\u002F2019\u002F08\u002F24172315\u002F1768.sql_logob.jpg","externalMention":"","author":{"id":"452","thumbnail":"https:\u002F\u002Fstatic.imasters.com.br\u002Fwp-content\u002Fuploads\u002F2018\u002F05\u002F30184745\u002FwagnercriveliniP-128x128.jpeg","name":"Wagner Crivelini","description":"Consultor Sênior na Microsoft, na área de Data Insights para América Latina. Especialista em bancos de dados, é colunista em diversos portais de TI do Brasil e do exterior, com mais de 100 artigos técnicos publicados. É também co-produtor do DatabaseCast, primeiro podcast brasileiro sobre bancos de dados.","slug":"wagner-crivelini","url":"https:\u002F\u002Fimasters.com.br\u002Fperfil\u002Fwagner-crivelini","registered":"2010-12-30 11:28:37","social":{"linkedin":null,"youtube":null,"facebook":"","twitter":"https:\u002F\u002Ftwitter.com\u002Fwcrivelini@gmail.com","instagram":null,"github":null,"url":"http:\u002F\u002Fwww.wcrivelini.com.br","mail":"wcrivelini@gmail.com"},"articles_count":91,"views_count":828100,"certifications":null,"conquests":null,"office":null},"categories":[{"title":"SQL Server","slug":"sql-server","id":7212,"link":"https:\u002F\u002Fimasters.com.br\u002Fsql-server"}],"tags":[{"title":"sql","slug":"sql","id":706,"link":"https:\u002F\u002Fimasters.com.br\u002Fsql"},{"title":"sql server","slug":"sql-server-2","id":324,"link":"https:\u002F\u002Fimasters.com.br\u002Fsql-server-2"},{"title":"Tabelas temporais","slug":"tabelas-temporais","id":8793,"link":"https:\u002F\u002Fimasters.com.br\u002Ftabelas-temporais"}],"relateds":[],"seo":[],"type":"post"}],"totalArticles":91,"maxPages":10,"page":1,"isFetchingPagination":false},"category":{"success":false,"data":{}},"categoryArticles":{"success":false,"isFetchingPagination":false,"maxPages":0,"page":1,"data":{}},"categoryMostRead":{"success":false,"data":{}},"home":{"success":false,"featured":[],"options":{"homeProducts":[]},"allCategories":{},"featuredArticles":{},"magazines":{"data":[]},"tv":{},"forums":[],"authorsRanking":{},"events":{},"certificatedUsers":[],"news":[]},"page":{"success":false,"data":{}},"magazine":{"success":false,"magazines":[]},"schedule":{"success":false,"data":{}},"searchResult":{"success":false,"totalPosts":0,"totalPage":0,"currentPage":1,"posts":[],"term":"","orderBy":"recents","currentFilter":"articles","isLoading":true,"isLoadingPagination":false},"singleNews":{"success":false,"data":{}},"listNews":{"success":false,"isFetchingPagination":false,"maxPages":0,"page":1,"data":[]},"sponsors":{"success":false,"data":{}},"header":{"success":true,"menus":{"main":[{"ID":138755,"order":1,"parent":0,"title":"Back-End","url":"https:\u002F\u002Fimasters.com.br\u002Fback-end","attr":"package","target":"","classes":"package","xfn":"","description":"Tecnologias utilizadas no backend da aplicação","object_id":7158,"object":"category","type":"taxonomy","type_label":"Categoria","children":[]},{"ID":138756,"order":2,"parent":0,"title":"Mobile","url":"https:\u002F\u002Fimasters.com.br\u002Fmobile","attr":"package","target":"","classes":"package","xfn":"","description":"Conteúdos sobre Desenvolvimento Mobile nas plataformas iOS (iPhone e iPad) e Android.","object_id":4255,"object":"category","type":"taxonomy","type_label":"Categoria","children":[]},{"ID":139476,"order":3,"parent":0,"title":"Front End","url":"https:\u002F\u002Fimasters.com.br\u002Ffront-end","attr":"package","target":"","classes":"package","xfn":"","description":"Conteúdos sobre HTML5, CSS3, Javascript, JQuery, Web Standards e SEO.","object_id":7304,"object":"category","type":"taxonomy","type_label":"Categoria","children":[]},{"ID":138764,"order":4,"parent":0,"title":"DevSecOps","url":"https:\u002F\u002Fimasters.com.br\u002Fdevsecops","attr":"package","target":"","classes":"package","xfn":"","description":"","object_id":1,"object":"category","type":"taxonomy","type_label":"Categoria","children":[]},{"ID":139204,"order":5,"parent":0,"title":"Design & UX","url":"https:\u002F\u002Fimasters.com.br\u002Fdesign-ux","attr":"package","target":"","classes":"package","xfn":"","description":"Conteúdos sobre User Experience, Arquitetura de Informação, Usabilidade, Acessibilidade, Design Responsivo, Teoria do Design e Photoshop.","object_id":7252,"object":"category","type":"taxonomy","type_label":"Categoria","children":[]},{"ID":138759,"order":6,"parent":0,"title":"Data","url":"https:\u002F\u002Fimasters.com.br\u002Fdata","attr":"package","target":"","classes":"package","xfn":"","description":"Conteúdos sobre Banco de Dados Oracle, MySQL, SQL Server, MongoDB, DB2 e Postgre SQL, sobre BI e análise de dados.","object_id":16,"object":"category","type":"taxonomy","type_label":"Categoria","children":[]},{"ID":138758,"order":7,"parent":0,"title":"APIs e Microsserviços","url":"https:\u002F\u002Fimasters.com.br\u002Fapis-microsservicos","attr":"package","target":"","classes":"package","xfn":"","description":"Dicas e tutoriais sobre APIs públicas como Facebook, Twitter, PayPal, Buscapé, Google, etc e arquitetura de microsserviços, com códigos de exemplo, vídeos, cursos.","object_id":4257,"object":"category","type":"taxonomy","type_label":"Categoria","children":[]}],"secondary":[{"ID":138752,"order":1,"parent":0,"title":"Fórum iMasters","url":"https:\u002F\u002Fforum.imasters.com.br\u002F","attr":"","target":"_blank","classes":"","xfn":"","description":"","object_id":138752,"object":"custom","type":"custom","type_label":"Link personalizado","children":[]},{"ID":139067,"order":2,"parent":0,"title":"Portal E-Commerce Brasil","url":"http:\u002F\u002Fwww.ecommercebrasil.com.br","attr":"","target":"","classes":"","xfn":"","description":"","object_id":139067,"object":"custom","type":"custom","type_label":"Link personalizado","children":[]},{"ID":156289,"order":3,"parent":0,"title":"iMasters Business","url":"http:\u002F\u002Fbusiness.imasters.com.br","attr":"","target":"","classes":"","xfn":"","description":"","object_id":156289,"object":"custom","type":"custom","type_label":"Link personalizado","children":[]}]}},"webinar":{"success":false,"data":{}},"webinars":{"success":false,"isFetchingPagination":false,"maxPages":0,"page":1,"data":{"next":[],"prev":[]}},"tv":{"sectionNameLeft":{"small":"Vídeos","large":"iMasters TV"},"itemsLeft":[{"text":"Em Destaque","slug":"recents"},{"text":"Mais Vistos","slug":"most-viewed"}],"sectionNameRight":{"small":"","large":""},"itemsRight":[{"text":"Mais Recentes","slug":"recents"},{"text":"7Masters","slug":"7-masters"},{"text":"PHP Experience","slug":"php-experience"},{"text":"Android DevConference","slug":"android-devconference"},{"text":"Busca","slug":"search"}],"currentVideo":"recents","currentListVideo":"recents"}} window.__SERVER_VARS__ = {"applicationRestUrl":"https:\u002F\u002Fadmin.imasters.com.br\u002Fwp-json","applicationBaseUrl":"https:\u002F\u002Fadmin.imasters.com.br"}