主页 / dba / 问题 / 173435
Accepted
McNets
Asked: 2017-05-13 05:50:41 +0800 CST

默认约束，值得吗？

  • 772

我通常按​​照以下规则设计我的数据库：

  • 除了 db_owner 和 sysadmin 之外没有其他人可以访问数据库表。
  • 用户角色在应用层控制。我通常使用一个 db 角色来授予对视图、存储过程和函数的访问权限，但在某些情况下，我会添加第二条规则来保护某些存储过程。
  • 我使用触发器来初步验证关键信息。

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 使用存储过程执行：

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

您是否认为在这种情况下值得使用默认约束，或者我正在向数据库服务器添加额外且不必要的工作？

更新

我知道通过使用 DEFAULT 约束，我正在向必须管理数据库的其他人提供更多信息。但我最感兴趣的是性能。

我假设数据库总是检查默认值，即使我提供了正确的值，因此我做了两次相同的工作。

例如，有没有办法在触发器执行中避免 DEFAULT 约束？

sql-server database-design

2 个回答

  1. Best Answer

    我假设数据库总是检查默认值，即使我提供了正确的值，因此我做了两次相同的工作。

    嗯，你为什么会这么认为？;-)。鉴于默认值存在以在INSERT语句中不存在它们所附加的列时提供值，我会假设完全相反：如果语句中存在关联的列，它们将被完全忽略INSERT。

    幸运的是，由于问题中的以下陈述，我们都不需要假设任何事情：

    我最感兴趣的是性能。

    关于性能的问题几乎总是可以测试的。所以我们只需要拿出一个测试来让SQL Server（这里的真正权威）来回答这个问题。

    设置

    运行以下一次：

    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;

    单独执行测试 1A 和 1B，而不是一起执行，因为这会影响时间。将每一项运行几次，以了解每一项的平均时间。

    测试 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

    测试 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

    单独执行测试 2A 和 2B，而不是一起执行，因为这会影响时间。将每一项运行几次，以了解每一项的平均时间。

    测试 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);

    测试 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);

    您应该看到测试 1A 和 1B 之间或测试 2A 和 2B 之间的时序没有真正的差异。因此，不，DEFAULT定义但未使用不会造成性能损失。

    此外，除了记录预期的行为之外，您还需要记住，主要是您关心 DML 语句是否完全包含在您的存储过程中。支持的人不在乎。未来的开发人员可能没有意识到您希望将所有 DML 封装在这些存储过程中，或者即使他们知道，也不会在意。在你离开后维护这个数据库的人（另一个项目或工作）可能不在乎，或者无论他们多么抗议，都可能无法阻止使用 ORM。因此，默认值可以帮助他们在执行时给人们一个“出局” INSERT，尤其是由支持代表完成的临时INSERT任务，因为这是他们不需要包括的一列（这就是为什么我总是在审计中使用默认值日期列）。

    DEFAULT并且，我刚刚想到，当语句中存在关联列时，可以相当客观地显示是否检查 a INSERT：只需提供一个无效值。以下测试就是这样做的：

    -- 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

    如您所见，当提供一列（和一个值，而不是关键字DEFAULT）时，默认值 100% 被忽略。我们知道这一点，因为INSERT成功了。但是，如果使用默认值，则会出现错误，因为它最终会被执行。

    有没有办法在触发器执行中避免 DEFAULT 约束？

    虽然需要避免默认约束（至少在这种情况下）是完全没有必要的，但为了完整起见，可以注意到只能在触发器内“避免”默认约束INSTEAD OF，而不是在AFTER触发器内。根据CREATE TRIGGER的文档：

    如果触发器表上存在约束，则在 INSTEAD OF 触发器执行之后和 AFTER 触发器执行之前检查它们。如果违反了约束，则 INSTEAD OF 触发器操作将回滚，并且不触发 AFTER 触发器。

    当然，使用INSTEAD OF触发器需要：

    1. 禁用默认约束
    2. 创建AFTER启用约束的触发器

    但是，我不完全建议这样做。

    • 24

  2. 我认为拥有默认约束没有重大危害。事实上，我看到了一个特别的优势 - 您已经在与表定义本身相同的逻辑级别定义了默认值。如果您在存储过程中提供了默认值，则必须有人去那里找出默认值是什么；而且，这对于系统的新人来说并不是显而易见的事情，必然（例如，如果你明天继承 10 亿美元，买下你自己的热带岛屿，然后离开并搬到那里，留下一些其他可怜的树液来解决问题自己出去）。

    • 9

相关问题