德瑞克：SQL Server 學習筆記: [SQL Server]：全部停用 (DISABLE) 資料庫內的 CHECK 條件約束(CONSTRAINT)

2017-06-17

[SQL Server]：全部停用 (DISABLE) 資料庫內的 CHECK 條件約束(CONSTRAINT)

因故需要全部停用 (DISABLE) 資料庫內的 CHECK 條件約束(CONSTRAINT)

效益是優化效能，經由停用 CHECK 條件約束，可讓新增、修改(INSERT、UPDATE) 資料的速度更快。

舉例來講，可應用於：測試用資料庫、複寫(Replication)機制訂閱者端(Subscriber)的資料庫等。

可以使用以下 T-SQL 陳述式達成

-- 全部停用：資料庫內的 CHECK 條件約束(CHECK CONSTRAINT)
USE UserDB
GO
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
GO

-- 全部啟用：資料庫內的 CHECK 條件約束(CHECK CONSTRAINT)
-- 不檢查現有資料
USE AdventureWorks2014
GO
EXEC sp_MSforeachtable @command1="ALTER TABLE ? WITH NOCHECK CHECK CONSTRAINT ALL"
GO

示範環境：SQL Server 2016

無法利用 SSMS 觀察到 CHECK 條件約束(CONSTRAINT) 是否被停用或啟用。

全部停用資料庫內的 CHECK 條件約束(CHECK CONSTRAINT)

示範如下

USE AdventureWorks2014
GO

-- 查詢：資料庫內的 CHECK 條件約束(CHECK CONSTRAINT)的相關狀態
SELECT s.name N'結構描述',o.name N'資料表', c.name N'CHECK 條件約束', 
 is_disabled N'已停用(1)', is_not_trusted N'不檢查現有資料(1)',
 definition N'T-SQL 運算式', c.type_desc N'物件類型描述', c.type N'物件類型'
FROM sys.check_constraints c
 INNER JOIN sys.objects o ON c.parent_object_id = o.object_id
 INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
ORDER BY s.name
GO

-- 全部停用：資料庫內的 CHECK 條件約束(CHECK CONSTRAINT)
USE AdventureWorks2014
GO
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
GO

-- 全部啟用：資料庫內的 CHECK 條件約束(CHECK CONSTRAINT)
-- 不檢查現有資料
USE AdventureWorks2014
GO
EXEC sp_MSforeachtable @command1="ALTER TABLE ? WITH NOCHECK CHECK CONSTRAINT ALL"
GO

-- 001_資料庫內 CHECK 條件約束(CHECK CONSTRAINT)的相關狀態