預設約束，值得嗎？

我通常按​​照以下規則設計我的數據庫：

• 除了 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 約束？

我假設數據庫總是檢查預設值，即使我提供了正確的值，因此我做了兩次相同的工作。

嗯，你為什麼會這麼認為？;-)。鑑於預設值的存在是為了在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啟用約束的觸發器

但是，我不完全建議這樣做。

引用自：https://dba.stackexchange.com/questions/173435