Início / dba / Perguntas / 173435
Accepted
McNets
Asked: 2017-05-13 05:50:41 +0800 CST

CONSTRAINT PADRÃO, vale a pena?

  • 772

Eu costumo projetar meus bancos de dados seguindo as próximas regras:

  • Ninguém além de db_owner e sysadmin tem acesso às tabelas do banco de dados.
  • As funções do usuário são controladas na camada do aplicativo. Eu costumo usar uma função de banco de dados para conceder acesso às visualizações, procedimentos armazenados e funções, mas em alguns casos, adiciono uma segunda regra para proteger alguns procedimentos armazenados.
  • Eu uso TRIGGERS para validar inicialmente informações críticas.

CREATE TRIGGER <TriggerName>
ON <MyTable>
[BEFORE | AFTER] INSERT
AS
    IF EXISTS (SELECT 1 
               FROM   inserted
               WHERE  Field1 <> <some_initial_value>
               OR     Field2 <> <other_initial_value>)
    BEGIN
        UPDATE MyTable
        SET    Field1 = <some_initial_value>,  
               Field2 = <other_initial_value>  
        ...  
    END
  • DML é executado usando procedimentos armazenados:

sp_MyTable_Insert(@Field1, @Field2, @Field3, ...);
sp_MyTable_Delete(@Key1, @Key2, ...);
sp_MyTable_Update(@Key1, @Key2, @Field3, ...);

Você acha que, nesse cenário, vale a pena usar DEFAULT CONSTRAINTs, ou estou adicionando um trabalho extra e desnecessário ao servidor DB?

Atualizar

Eu entendo que usando a restrição DEFAULT estou dando mais informações para outra pessoa que deve administrar o banco de dados. Mas estou mais interessado no desempenho.

Presumo que o banco de dados esteja sempre verificando os valores padrão, mesmo que eu forneça o valor correto, portanto, estou fazendo o mesmo trabalho duas vezes.

Por exemplo, existe uma maneira de evitar a restrição DEFAULT em uma execução de gatilho?

sql-server database-design

2 respostas

  1. Best Answer

    Presumo que o banco de dados esteja sempre verificando os valores padrão, mesmo que eu forneça o valor correto, portanto, estou fazendo o mesmo trabalho duas vezes.

    Hum, por que você assumiria isso? ;-). Dado que Defaults existem para fornecer um valor quando a coluna à qual eles estão anexados não está presente na INSERTdeclaração, eu diria exatamente o oposto: que eles são completamente ignorados se a coluna associada estiver presente na INSERTdeclaração.

    Felizmente, nenhum de nós precisa assumir nada devido a esta afirmação na pergunta:

    Estou mais interessado no desempenho.

    Perguntas sobre desempenho são quase sempre testáveis. Então, só precisamos criar um teste para permitir que o SQL Server (a verdadeira autoridade aqui) responda a essa pergunta.

    CONFIGURAR

    Execute o seguinte uma vez:

    SET NOCOUNT ON;

-- DROP TABLE #HasDefault;
CREATE TABLE #HasDefault
(
  [HasDefaultID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [SomeInt] INT NULL,
  [SomeDate] DATETIME NOT NULL DEFAULT (GETDATE())
);

-- DROP TABLE #NoDefault;
CREATE TABLE #NoDefault
(
  [NoDefaultID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [SomeInt] INT NULL,
  [SomeDate] DATETIME NOT NULL
);

-- make sure that data file and Tran Log file are grown, if need be, ahead of time:
INSERT INTO #HasDefault ([SomeInt])
  SELECT TOP (2000000) NULL
  FROM   [master].sys.[all_columns] ac1
  CROSS JOIN [master].sys.[all_columns] ac2;

    Execute os testes 1A e 1B individualmente, não juntos, pois isso distorce o tempo. Execute cada um várias vezes para ter uma noção do tempo médio de cada um.

    Teste 1A

    TRUNCATE TABLE #HasDefault;
GO

PRINT '#HasDefault:';
SET STATISTICS TIME ON;
INSERT INTO #HasDefault ([SomeDate])
  SELECT TOP (1000000) '2017-05-15 10:11:12.000'
  FROM   [master].sys.[all_columns] ac1
  CROSS JOIN [master].sys.[all_columns] ac2;
SET STATISTICS TIME OFF;
GO

    Teste 1B

    TRUNCATE TABLE #NoDefault;
GO

PRINT '#NoDefault:';
SET STATISTICS TIME ON;
INSERT INTO #NoDefault ([SomeDate])
  SELECT TOP (1000000) '2017-05-15 10:11:12.000'
  FROM   [master].sys.[all_columns] ac1
  CROSS JOIN [master].sys.[all_columns] ac2;
SET STATISTICS TIME OFF;
GO

    Execute os testes 2A e 2B individualmente, não juntos, pois isso distorce o tempo. Execute cada um várias vezes para ter uma noção do tempo médio de cada um.

    Teste 2A

    TRUNCATE TABLE #HasDefault;
GO

DECLARE @Counter INT = 0,
        @StartTime DATETIME,
        @EndTime DATETIME;

BEGIN TRAN;
--SET STATISTICS TIME ON;
SET @StartTime = GETDATE();
WHILE (@Counter < 100000)
BEGIN
  INSERT INTO #HasDefault ([SomeDate]) VALUES ('2017-05-15 10:11:12.000');
  SET @Counter = @Counter + 1;
END;
SET @EndTime = GETDATE();
--SET STATISTICS TIME OFF;
COMMIT TRAN;
PRINT DATEDIFF(MILLISECOND, @StartTime, @EndTime);

    Teste 2B

    TRUNCATE TABLE #NoDefault;
GO

DECLARE @Counter INT = 0,
        @StartTime DATETIME,
        @EndTime DATETIME;

BEGIN TRAN;
--SET STATISTICS TIME ON;
SET @StartTime = GETDATE();
WHILE (@Counter < 100000)
BEGIN
  INSERT INTO #NoDefault ([SomeDate]) VALUES ('2017-05-15 10:11:12.000');
  SET @Counter = @Counter + 1;
END;
SET @EndTime = GETDATE();
--SET STATISTICS TIME OFF;
COMMIT TRAN;
PRINT DATEDIFF(MILLISECOND, @StartTime, @EndTime);

    Você deve ver que não há diferença real no tempo entre os testes 1A e 1B, ou entre os testes 2A e 2B. Portanto, não, não há penalidade de desempenho para ter um DEFAULTdefinido, mas não usado.

    Além disso, além de documentar o comportamento pretendido, você precisa ter em mente que é principalmente você quem se preocupa com as instruções DML sendo completamente contidas em seus procedimentos armazenados. As pessoas de apoio não se importam. Os desenvolvedores futuros podem não estar cientes de seu desejo de ter todo o DML encapsulado nesses procedimentos armazenados ou se importar, mesmo que saibam. E quem mantiver esse banco de dados depois que você se for (outro projeto ou trabalho) pode não se importar ou não ser capaz de impedir o uso de um ORM, não importa o quanto eles protestem. Então, Defaults, pode ajudar na medida em que eles dão às pessoas um "out" ao fazer um INSERT, especialmente um ad-hoc INSERTfeito por um representante de suporte, já que essa é uma coluna que eles não precisam incluir (e é por isso que eu sempre uso defaults em auditoria colunas de data).

    E, acabou de me ocorrer que pode ser mostrado de maneira bastante objetiva se a DEFAULTé verificado ou não quando a coluna associada está presente na INSERTinstrução: basta fornecer um valor inválido. O teste a seguir faz exatamente isso:

    -- DROP TABLE #BadDefault;
CREATE TABLE #BadDefault
(
  [BadDefaultID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [SomeInt] INT NOT NULL DEFAULT (1 / 0)
);


INSERT INTO #BadDefault ([SomeInt]) VALUES (1234); -- Success!!!
SELECT * FROM #BadDefault; -- just to be sure ;-)



INSERT INTO #BadDefault ([SomeInt]) VALUES (DEFAULT); -- Error:
/*
Msg 8134, Level 16, State 1, Line xxxxx
Divide by zero error encountered.
The statement has been terminated.
*/
SELECT * FROM #BadDefault; -- just to be sure ;-)
GO

    Como você pode ver, quando uma coluna (e um valor, não a palavra-chave DEFAULT) é fornecida, o Padrão é 100% ignorado. Sabemos disso porque o INSERTsucesso. Mas se o Default for usado, haverá um erro, pois ele finalmente está sendo executado.

    Existe uma maneira de evitar a restrição DEFAULT em uma execução de gatilho?

    Embora a necessidade de evitar Default Constraints (pelo menos neste contexto) seja completamente desnecessária, por uma questão de completude pode-se notar que só seria possível "evitar" uma Default Constraint dentro de um INSTEAD OFTrigger, mas não dentro de um AFTERTrigger. De acordo com a documentação para CREATE TRIGGER :

    Se existirem restrições na tabela de gatilhos, elas serão verificadas após a execução do gatilho INSTEAD OF e antes da execução do gatilho AFTER. Se as restrições forem violadas, as ações do gatilho INSTEAD OF serão revertidas e o gatilho AFTER não será acionado.

    Claro, usar um INSTEAD OFTrigger exigiria:

    1. Desativando a restrição padrão
    2. Criando um AFTERgatilho que habilita a restrição

    No entanto, eu não recomendaria exatamente fazer isso.

    • 24

  2. Não vejo nenhum dano significativo em ter restrições padrão. Na verdade, vejo uma vantagem em particular - você definiu o padrão no mesmo nível de lógica que a própria definição da tabela. Se você tiver um valor padrão fornecido em seu procedimento armazenado, alguém precisará ir até lá para descobrir qual é o valor padrão; e, isso não é algo óbvio para alguém novo no sistema, necessariamente (se, por exemplo, você herdar um bilhão de dólares amanhã, comprar sua própria ilha tropical, sair e se mudar para lá, deixando algum outro pobre coitado para descobrir as coisas por conta própria).

    • 9

relate perguntas