Recentemente, tomei conhecimento dos procedimentos armazenados temporários e fiquei chocado por nunca tê-los visto usados antes em minha carreira. Fiquei muito decepcionado quando lembrei que esse CREATE PROCEDUREdeveria ser o primeiro extrato do lote, o que certamente limita seu uso. Esta ideia foi apoiada quando vi quão raramente eles são mencionados neste site . Eles nem têm etiqueta!
Quais são os casos de uso idiomáticos para procedimentos armazenados temporários? Como eles evitam as limitações impostas pela necessidade de ser a primeira instrução em seus lotes?
comum
Os usos mais comuns que tenho para procedimentos armazenados temporários são:
ASYNC_NETWORK_IOinterferência de espera: linkNa verdade, não é para isso que eles são bons ou úteis. Você está latindo um pouco para a árvore errada procurando por eles.
permissões e procedimentos
Em alguns ambientes, como consultor, posso não ter permissão para criar procedimentos armazenados como objetos permanentes.
Isso pode ocorrer devido ao nível de acesso que minha conta de usuário tem ou porque vários processos de gerenciamento de alterações não permitem a criação ad hoc deles.
Nesses casos, criar um procedimento armazenado temporário que possa ser executado normalmente costuma ser mais rápido do que editar um procedimento armazenado para transformá-lo em um script de execução única.
Também é uma técnica útil para testar alterações em procedimentos armazenados existentes sem o medo de substituir acidentalmente o que está em uso no momento.
lado do aplicativo
Digamos que os desenvolvedores usem principalmente algum tipo de ORM para gerar consultas e que elas sejam parametrizadas. Se eu quiser explorar oportunidades de ajuste para um desses, minhas escolhas em SSMS são um tanto limitadas.
Usar variáveis locais como substitutos resultará em planos de consulta estranhos em muitos casos, e usar dicas de recompilação com variáveis locais pode não ser apropriado se o problema for detecção de parâmetros.
Nesses casos, posso:
Ambas as opções são equivalentes para teste, mas muitas vezes a rota do procedimento armazenado temporário é mais rápida, pois não preciso me preocupar em mexer com aspas simples para dobrá-las.
Você também pode criar procedimentos armazenados temporários globais prefixando ## em vez de ##. No SSMS, você pode usar o terminador GO para separar lotes. Para um procedimento armazenado temporário normal, ele tem como escopo a sessão e permanecerá ativo enquanto a sessão estiver ativa. Para um procedimento armazenado temporário global, ele pode ser acessado por qualquer sessão e permanece ativo enquanto a sessão que o criou ainda estiver aberta.
Eu os uso para ajuste de desempenho. Eles são particularmente úteis se você quiser testar uma nova versão de um procedimento armazenado em uma cópia enviada por log do seu banco de dados de produção (no modo standby/somente leitura). Normalmente, eu roteirizo o procedimento armazenado - prefixo o nome com ## (procedimento armazenado temporário global) e faço as alterações necessárias. Posso então pegar uma consulta capturada em outra janela do SSMS e prefixá-la com ## para testar a nova versão.
Uma opção alternativa é criar um script do procedimento, remover CREATE PROC, alterar a definição de parâmetros para DECLARE e definir os valores dos parâmetros conforme necessário. Criar um procedimento armazenado temporário é melhor - é mais fácil e você também obtém a detecção de parâmetros como faria com um procedimento armazenado regular.