sql-server – DEFAULT CONSTRAINT,值得吗？

我通常按照下一条规则设计我的数据库：

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

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,...);

在这种情况下,你认为值得使用DEFAULT CONSTRAINTs,还是我在DB服务器上添加额外的不必要的工作？

更新

据我所知,通过使用DEFAULT约束,我向其他必须管理数据库的人提供了更多信息.但我最感兴趣的是表现.

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

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

解决方法

I assume that the database is checking always default values,even if I supply the correct value,hence I’m doing the same job twice.

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

幸运的是,由于问题中的这一陈述,我们都不需要承担任何事情：

I’m mostly interested on performance.

关于性能的问题几乎总是可以测试的.所以我们只需要进行测试以允许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,[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和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,@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,@EndTime);

您应该看到测试1A和1B之间或测试2A和2B之间的时间没有实际差异.所以,不,没有定义DEFAULT但没有使用的性能损失.

此外,除了仅记录预期的行为之外,您还需要记住,主要是您关心的DML语句完全包含在您的存储过程中.支持人员不关心.未来的开发人员可能不会意识到您希望将所有DML封装在这些存储过程中,或者即使他们知道也要关心.在你离开后(无论是另一个项目或工作)维护这个数据库的人可能不关心,或者无论他们抗议多少都可能无法阻止使用ORM.因此,默认值可以帮助他们在执行INSERT时给人们一个“out”,特别是由支持代表完成的ad-hoc INSERT,因为这是他们不需要包含的一列(这就是我总是使用默认值的原因)在审计日期列).

并且,我刚刚想到,当INSERT语句中存在关联列时,是否可以非常客观地显示DEFAULT：仅提供无效值.以下测试就是这样做的：

-- DROP TABLE #BadDefault;
CREATE TABLE #BadDefault
(
  [BadDefaultID] INT NOT NULL IDENTITY(1,[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成功了.但是如果使用默认值,则最终执行时会出现错误.

Is there a way to avoid DEFAULT constraint within a trigger execution?

虽然需要避免默认约束(至少在此上下文中)是完全没有必要的,但为了完整起见,可以注意到只能在INSTEAD OF触发器中“避免”默认约束,但不能在AFTER中触发.根据CREATE TRIGGER的文档：

If constraints exist on the trigger table,they are checked after the INSTEAD OF trigger execution and before the AFTER trigger execution. If the constraints are violated,the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not fired.

当然,使用INSTEAD OF Trigger需要：

>禁用默认约束
>创建启用约束的AFTER触发器

但是,我不建议这样做.

sql-server – DEFAULT CONSTRAINT,值得吗？

解决方法

猜你在找的MsSQL相关文章