Início / dba / Perguntas / 7422
Accepted
bernd_k
Asked: 2011-11-01 05:21:11 +0800 CST

Existem maneiras de substituir apenas os procedimentos armazenados do SQL Server quando a definição for alterada?

  • 772

Conheço os dois padrões a seguir para escrever scripts para criar procedimentos armazenados, que podem ser executados repetidamente sem gerar erros.

if object_id('my_proc') > 0 drop procedure my_proc
go
create procedure dbo.my_proc as Print 'This is not a dummy';

e o outro que preserva as permissões

if object_id('my_proc') is null
    EXEC ('create procedure dbo.my_proc as Print ''This is a dummy''');
go
ALTER PROCEDURE dbo.my_proc as Print 'This is not a dummy';

Eu acho que quando o procedimento existe e seu código hash é o mesmo da nova versão, não haveria necessidade de descartar e recriar ou alterar o procedimento.

Meu problema é que o HashBytes é limitado a no máximo 8000 bytes e geralmente não consigo usá-lo como

if object_id('my_proc') is null
    EXEC ('create procedure dbo.my_proc as Print ''This is a dummy''');
go
if object_Id('my_proc') > 0
and
(
Select HashBytes('MD5',definition) MD5
from sys.sql_modules m
join sysobjects o on o.id = m.object_id
where o.name = 'my_proc'
) <> 0x9028A1B9D93AC7592EC939CCABF9D3DE
begin
    print 'definition has changed';
    EXEC ('ALTER PROCEDURE dbo.my_proc as Print ''This is not a dummy''');
end

Para procedimentos cuja definição tenha mais de 4.000 caracteres. Alguma proposta para lidar com esses casos de maneira semelhante?

Editar:

Não quero apenas evitar a liberação de planos em cache.

Também tenho que lidar com clientes diferentes com versões diferentes de um procedimento armazenado, onde desejo apenas substituir uma dessas variantes por uma versão mais recente.

sql-server stored-procedures

3 respostas

  1. As permissões não devem ser atribuídas ao procedimento armazenado

    Seu procedimento armazenado deve estar em um esquema, as permissões estão no esquema (concedidas a funções). Dessa forma, novos objetos nesse esquema têm permissões automaticamente. Nenhum objeto único deve ter permissões

    Então, de qualquer maneira seria OK.

    Veja estas outras questões de DBA:

    FYI, object_id é assinado int , então use "não é igual"

    if object_id('my_proc') <> 0 drop procedure my_proc

    Editar, nvarchar(max) é comparável diretamente

    if object_Id('my_proc') <> 0
    drop proc ThenewProc
GO
CREATE PROC ThenewProc
...
GO

IF object_definition(object_id('my_proc')) <> 
         object_definition(object_id('ThenewProc')) <> 0
BEGIN
    drop procedure my_proc
    drop proc ThenewProc
END
GO
CREATE PROC my_proc
...
GO
    • 6
  2. Best Answer

    Recentemente, tivemos essa discussão no meu local de trabalho.

    Primeiro, quero elogiá-lo por fazer a "coisa certa" usando HASHBYTES()over CHECKSUM()para detectar alterações. A Microsoft adverte especificamente contra o uso CHECKSUM(@input)para essa finalidade, pois sua taxa de colisão é muito alta em comparação com a do HASHBYTES('SHA1', @input). Uma vantagem CHECKSUM(), porém, é que não há restrição (óbvia) no tamanho de sua entrada.

    Em segundo lugar, se você usar HASHBYTES(), recomendo usar SHA1como algoritmo de hash. Das opções disponíveis, SHA1tem a menor taxa de colisão e a velocidade não é uma preocupação para o seu caso de uso.

    Finalmente, para usar HASHBYTES()contra entradas maiores que 8000 bytes, você terá que:

    1. Divida sua entrada em blocos de 8.000 bytes.
    2. Faça um hash em cada pedaço.

    3. De alguma forma, combine os hashes resultantes e faça o hash deles para obter sua saída final.

      Você pode fazer isso de duas maneiras:

      1. Converta suas saídas de hash em strings, concatene-as e faça o hash do resultado.
      2. Coloque todas as suas saídas de hash em uma tabela de memória e obtenha sua soma de verificação agregada usando CHECKSUM_AGG().

    4. Encapsule esse trabalho como uma função que recebe NVARCHAR(MAX)como entrada.

    Dito isso, é mais simples comparar as definições de proc diretamente usando OBJECT_DEFINITION()como gbn sugeriu , ou simplesmente enviar todas as definições para todos os lugares quantas vezes quiser, como sugeriu Mike .

    Eu me pergunto que tipo de ambiente se beneficiaria significativamente de um processo que implantasse apenas procedimentos alterados e usasse hashes para evitar a cópia e a comparação de definições completas. Você precisaria ter muitos procedimentos para se manter sincronizado.

    • 3

  3. Eu diria que a melhor resposta aqui é apenas descartar/recriar quantas vezes quiser - Apenas crie o script das permissões e execute-o. Você pode executar o script de permissões quantas vezes quiser, sem erros.

    Tentar criar uma metodologia de hash eficaz, segura e confiável para modificar apenas os procedimentos afetados não é realmente necessário quando você apenas solta, recria e executa seu script de permissão.

    Você encontrará muitas lojas que fazem implantações dessa maneira.

    • 2

relate perguntas