Início / dba / Perguntas / 324297
Accepted
RobMartin
Asked: 2023-03-03 00:27:26 +0800 CST

Alterar uma UDF escalar usada em uma restrição de verificação no SQL Server 2012

  • 772

Eu tenho uma restrição de verificação em uma coluna que usa uma UDF escalar. Eu tenho um requisito onde preciso fazer alterações no UDF. Com base no que sei, preciso descartar a restrição, fazer alterações na UDF e, em seguida, adicionar novamente a restrição. Durante o período em que a restrição é removida, estou preocupado que dados inválidos possam ser inseridos na tabela. Existe uma maneira de alterar o UDF sem eliminar a restrição? Se não, existe uma maneira de impedir que dados incorretos entrem na tabela?

sql-server-2012

3 respostas

  1. Best Answer

    você não deveria

    Quando você coloca uma UDF escalar em uma coluna computada ou verifica uma restrição, você se depara com dois problemas:

    • As consultas que tocam a tabela não poderão gerar um plano de consulta paralelo ( mais detalhes aqui )
    • Se não forem persistidos ou indexados, serão executados uma vez por linha necessária para processar a consulta

    Sempre que possível:

    • Use T-SQL simples fora de uma função para fazer o cálculo (possível se todas as colunas estiverem em uma única tabela)
    • Escreva um gatilho para capturar linhas que não deveriam entrar na tabela
    • Use um tipo diferente de restrição (chave estrangeira, etc.) para capturar linhas que não deveriam existir na tabela

    com script

    /*A table*/
CREATE TABLE
    dbo.bad_idea
(
    id int PRIMARY KEY IDENTITY,
    some_date date
);
GO 

/*A silly function*/
CREATE FUNCTION
    dbo.please_do_not
(
    @some_date date
)
RETURNS bit
AS
BEGIN
      RETURN 
      (
          CASE 
              WHEN @some_date < '19000101' 
              THEN 0 
              ELSE 1 
          END
      );
END;
GO 

/*A bad idea for a check constraint*/
ALTER TABLE
    dbo.bad_idea
WITH CHECK
ADD CONSTRAINT  
    god_no 
CHECK 
(
    dbo.please_do_not(some_date) = 1
);
GO 

/*An updated silly function*/
CREATE FUNCTION
    dbo.please_i_beg_you_do_not
(
    @some_date date
)
RETURNS bit
AS
BEGIN
      RETURN 
      (
          CASE 
              WHEN @some_date < '19800101' 
              THEN 0 
              ELSE 1 
          END
      );
END;
GO 

/*Another bad idea*/
ALTER TABLE
    dbo.bad_idea
WITH CHECK
ADD CONSTRAINT  
    god_no 
CHECK 
(
    dbo.please_do_not(some_date) = 1
);
GO 

/*An equally bad idea*/
ALTER TABLE
    dbo.bad_idea
WITH CHECK
ADD CONSTRAINT  
    god_no_please
CHECK 
(
    dbo.please_i_beg_you_do_not(some_date) = 1
);
GO 

/*Drop the old one off*/
ALTER TABLE
    dbo.bad_idea
DROP CONSTRAINT
    god_no;
    • 5

  2. Não, infelizmente você não pode alterar uma função usada por uma restrição de verificação, mesmo quando desabilitada com NOCHECK.

    Você pode contornar isso (sem risco de dados incorretos) por:-

    1. criando uma segunda função com a nova definição
    2. criando uma segunda restrição de verificação usando a nova função
    3. descartando a restrição de verificação original
    4. descartando a primeira função

    Alternativamente, se você precisar reter os nomes da função e restrição de verificação, você pode fazer isso por:-

    1. criando uma segunda função com a nova definição
    2. criando uma segunda restrição de verificação usando a nova função
    3. descartando a restrição de verificação original
    4. alterando a primeira função com a nova definição
    5. recriando a restrição de verificação original
    6. descartando a segunda restrição de verificação
    7. descartando a segunda função
    • 3

  3. Você pode fazer tudo dentro de uma transação, o que forçará um Sch-Mbloqueio de modificação de esquema na tabela, impedindo que qualquer dado seja inserido.

    Por exemplo:

    BEGIN TRAN;

ALTER TABLE YourTable
DROP CONSTRAINT YourConstraint;

GO

CREATE OR ALTER FUNCTION dbo.YourFunction.....

/*
etc
*/

GO

ALTER TABLE YourTable
ADD CONSTRAINT YourConstraint CHECK (dbo.YourFunction(YourColumn) = 1);

COMMIT;

    Concordo que funções em restrições de verificação são uma má ideia e devem ser evitadas, se possível.

    Um motivo comum para precisar de tal função é criar uma restrição de várias tabelas. No entanto, isso pode ser feito melhor usando um truque pouco conhecido usando exibições indexadas . Um método um pouco menos eficiente é um gatilho.

    • 2

relate perguntas