Last Friday I asked myself: imagine I have a CHECK constraint enabled on a column in a table. What would happen if I try to enable it again?
My question is not about "expecting some error" - I expect that the engine will check if the constraint is enabled and if it is - nothing will be made.
If the constraint was off and the on - the SQL Server would have to load all data from a particular table just to check if the constraint is not violated.
If the constraint is turned on and I issue the commend "turn it on" - I expect that no data is loaded to the memory
Let’s start
from the beginning. I created a new database for the testing purpose. It will be easier to navigate on various DMV later :)
CREATE
DATABASE
CheckTEST
go
USE
CheckTEST
GO
Now it's time to create a table - let's call it Months. The table has only one column called MonthNo and one CHECK constraint that will assure us that we have valid month number which is from 1 to 12.
CREATE
TABLE dbo.Months (MonthNo TINYINT)
GO
ALTER
TABLE dbo.Months ADD
CONSTRAINT
[Months_ValidMonth] CHECK(MonthNo
BETWEEN 1 AND 12)
GO
Let's add some data
INSERT
INTO dbo.Months(
MonthNo )
VALUES ( RAND() *12 )
GO 2000
I know the
INSERT will cause error when a 0 is generated – but that’s ok.
Next step
would be to check how my constraint looks like:
SELECT
* FROM
sys.check_constraints
WHERE
name = 'Months_ValidMonth'
GO
I can see
that values in the column is_disabled is set to 0 (ok – constraint is
active) , so I am sure that no value is going to be entered to the table that
violate the constraint. For sure I could insert NULL.
Suppose I turned off the constraint:
ALTER
TABLE dbo.Months WITH
CHECK NOCHECK CONSTRAINT
[Months_ValidMonth] ;
GO
And turned
it back again:
ALTER
TABLE dbo.Months WITH
CHECK CHECK CONSTRAINT
[Months_ValidMonth] ;
GO
Nothing has
happened, data is ok. Now I clear all buffers:
DBCC
FREEPROCCACHE
DBCC
DROPCLEANBUFFERS
GO
For
this database I should not see any results through the dm_os_buffer_descriptors
DMV (or at least I do not want to see any DATA pages that store the actual data
of the table)
SELECT
* FROM
sys.dm_os_buffer_descriptors
WHERE
database_id = DB_ID()
What will
happen if I issue the code once again – but only this part of code:
ALTER
TABLE dbo.Months WITH
CHECK CHECK CONSTRAINT
[Months_ValidMonth] ;
GO
I expect
that NO DATA pages is going to be loaded to the buffers. The constraint is
already enabled so why to load data ?
The conclusion of this blog post: I was
surprised but the DATA pages were loaded. What is
the reason to do it?