搜尋本站文章

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)的相關狀態




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



-- 003_已全部停用資料庫內的 CHECK 條件約束




-- 004_全部啟用:資料庫內的 CHECK 條件約束
-- 不檢查現有資料


-- 005_已全部啟用資料庫內的 CHECK 條件約束






SSMS 檢視資料表上的條件約束

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

-- 010_SSMS_查詢資料表上的條件約束



-- 011_SSMS_無法停用_CHECK_條件約束





參考文件

[SQL Server]:全部停用 (DISABLE) 資料庫內的 DML 觸發程序(Trigger)
http://sharedderrick.blogspot.tw/2017/06/sql-server-disable-trigger.html

How to disable all triggers and constraints?
http://www.sqlusa.com/bestpractices2005/disabletriggerconstraint/

[SQL Server][Replication]:複寫使用者正在插入至 NOT FOR REPLICATION 識別欄位時...遭遇錯誤
http://sharedderrick.blogspot.tw/2017/06/sql-serverreplication-not-for.html

DISABLE TRIGGER (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms189748.aspx

停用(Disable)與啟用(Enable)「觸發程序(Trigger)」
http://sharedderrick.blogspot.tw/2010/09/disableenabletrigger.html

停用複寫的檢查條件約束
https://msdn.microsoft.com/zh-tw/library/ms190235.aspx

唯一條件約束與檢查條件約束
https://msdn.microsoft.com/zh-tw/library/ms187550.aspx#Check