Como criar visões materializadas no SQL Server?

Eu vou projetar um DW e ouvi sobre visões materializadas. Na verdade, quero criar uma visualização e ela deve ser atualizada automaticamente quando as tabelas base forem alteradas. Alguém pode explicar com um exemplo de consulta ..

Eles são chamados de exibições indexadas no SQL Server – leia esses white papers para obter mais informações básicas:

  • Criando uma exibição indexada
  • Melhorando o desempenho com exibições indexadas do SQL Server 2008

Basicamente, tudo que você precisa fazer é:

  • criar uma visão regular
  • criar um índice clusterizado nessa visão

e pronto!

A parte complicada é: a visão tem que satisfazer um grande número de restrições e limitações – essas são descritas no white paper. Se você fizer isso, é tudo o que existe. A exibição está sendo atualizada automaticamente, não é necessária manutenção.

Recursos adicionais:

  • Criando e otimizando exibições no SQL Server
  • Exibições indexadas do SQL Server

Embora puramente da perspectiva da engenharia, as visualizações indexadas soam como algo que todos podem usar para melhorar o desempenho, mas o cenário da vida real é muito diferente. Eu não tive sucesso em usar visões indexadas onde eu mais preciso, devido a muitas restrições sobre o que pode ser indexado e o que não pode.

Se você tiver associações externas nas visualizações, elas não poderão ser usadas. Além disso, as expressões de tabelas comuns não são permitidas … Na verdade, se você tiver alguma ordenação em subseleções ou tabelas derivadas (como na cláusula partition by), também estará sem sorte.

Isso deixa apenas cenários muito simples para usar visualizações indexadas, algo que, na minha opinião, pode ser otimizado criando índices apropriados em tabelas subjacentes de qualquer maneira.

Ficarei muito feliz em ouvir alguns cenários da vida real em que as pessoas realmente usaram as visualizações indexadas em seu benefício e não poderiam ter feito sem elas

Você pode precisar de um pouco mais de fundo sobre o que é realmente uma visão materializada. No Oracle, esses são um object que consiste em vários elementos quando você tenta construí-lo em outro lugar.

Um MVIEW é essencialmente um instantâneo de dados de outra fonte. Ao contrário de uma exibição, os dados não são encontrados quando você consulta a exibição, ela é armazenada localmente em uma forma de tabela. O MVIEW é atualizado usando um procedimento em segundo plano que inicia em intervalos regulares ou quando os dados de origem são alterados. O Oracle permite atualizações completas ou parciais.

No SQL Server, eu usaria o seguinte para criar um MVIEW básico para atualização (completa) regularmente.

Primeiro, uma visão. Isso deve ser fácil para a maioria, pois as visualizações são bastante comuns em qualquer database. Em seguida, uma tabela. Isso deve ser idêntico à exibição em colunas e dados. Isso armazenará um instantâneo dos dados da exibição. Em seguida, um procedimento que trunca a tabela e a recarrega com base nos dados atuais na exibição. Finalmente, um trabalho que aciona o procedimento para iniciar seu trabalho.

Tudo o resto é experimentação.

Quando a visualização indexada não é uma opção e atualizações rápidas não são necessárias, você pode criar uma tabela de cache de hack:

 select * into cachetablename from myviewname alter table cachetablename add primary key (columns) -- OR alter table cachetablename add rid bigint identity primary key create index... 

em seguida, sp_rename view / table ou altere quaisquer consultas ou outras visualizações que façam referência a ela para apontar para a tabela de cache.

agendar diariamente / noite / semanal / que não atualizar como

 begin transaction truncate table cachetablename insert into cachetablename select * from viewname commit transaction 

NB: isso vai comer espaço, também em seus logs de tx. Melhor usado para conjuntos de dados pequenos que são lentos para serem computados. Talvez refatorar para eliminar colunas “fáceis, mas grandes” primeiro em uma visão externa.

Para o MS T-SQL Server, sugiro investigar a criação de um índice com a instrução “include”. A exclusividade não é necessária, nem a sorting física de dados associada a um índice clusterizado. O “Índice … Include ()” cria um armazenamento de dados físico separado mantido automaticamente pelo sistema. É conceitualmente muito semelhante a um Oracle Materialized View.

https://msdn.microsoft.com/pt-br/library/ms190806.aspx

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