THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Damian Widera

Strange behaviour on CHECK constraint

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 :)






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. 



ALTER TABLE dbo.Months ADD CONSTRAINT [Months_ValidMonth] CHECK(MonthNo BETWEEN 1 AND 12)



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'



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:




And turned it back again:




Nothing has happened, data is ok. Now I  clear all buffers:





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:




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?

Published Sunday, March 16, 2014 6:15 PM by Damian
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



SomewhereSomehow said:

That's an interesting observation!

If we enable in Profiler 3 events (SP:StmtCompleted, SQL:StmtCompleted, Showplan Statistics XML) and re-run you test query, we'll see 3 events:

1. Showplan Statistics XML - Plan with assert: Assert <- Compute Scalar <- Table scan

2. SP:StmtCompleted - SELECT * FROM [dbo].[Months]

3. SQL:StmtCompleted - ALTER TABLE dbo.Months WITH CHECK CHECK CONSTRAINT [Months_ValidMonth]

So, it seems that server checks constraint anytime, regardless of it's initial state.

March 17, 2014 2:34 AM

Damian said:

Yes exactly :(. And this might lead to some problems with the performance (rare scenario but I actually faced this)

March 17, 2014 2:39 AM

Peter said:

Good approach and a little bit unexpected behavior :(

March 17, 2014 10:02 AM

Peter said:

What version  of the SQL Server you tested ?

March 17, 2014 11:21 AM

Damian said:

Peter -  I ran my queries on the SQL Server 2012. The same behaviour you can find in the SQL Server 2014 CTP2.

March 17, 2014 11:25 AM

Nakul Vachhrajani said:

I do not believe that this is unexpected. I would have expected it.

Here's why. Assume a scenario where:

1. Table has constraints enabled

2. User disables the constraint and adds some data into the table (it may or may not violate the constraint)

3. Now, user enables the constraint WITH NOCHECK, i.e. constraint is enabled, but checking of existing data is not done

4. Now, when the user issues a WITH CHECK CHECK, SQL Server has to validate the existing data

What I am trying to say is that whenever a WITH CHECK CHECK is issued, there is no way for SQL Server to know whether all data was validated when constraint was last enabled?

March 24, 2014 12:12 AM

Damian said:

Nakul - great - that is how the SQL Server works (that is perfect, ok, thats what we expect).

But please add another point:

5. Issues a WITH CHECK CHECK again - this is what the blog post is all about

The SQL Server validate the data once again however - it maybe should not because in the point 4 you already validated data.

I was talking about that internally with other MVP fellows and one of the answer to my question was: "You issued the DDL command and this command is executed because this is expected by you". So the SQL Server does not check IF DDL command SHOULD be executed (because in our case the check is very simple - just check the is_disabled column in the DMV) but just executes it.

March 24, 2014 2:09 AM

roopa said:

please answer me that i created the table movies with column release year . i added some data in it as below 2000 as release year the i added constraint that the release year should be greater than 2000. so when i added new data to the table it satisfying the constraint but about already existing data?

August 11, 2014 3:46 PM

Damian said:


I do a short demo:

--First I create a table

Create table Movies


name nvarchar(100)

,release_date date



--Now I add some data to the table


VALUES('Title1', '1999-01-01'),

('Title2', '2000-01-01'),

('Title3', '2001-01-01')


--Now I try to add the constraint for which one of the rows already existing in a table does not fulfill the requirements


ADD CONSTRAINT [CK_ReleaseDate_Greater_2000] CHECK (release_date>'19991231')

And - I have an error. I expected that - if you create an active constraint (that is not disabled) you have to take care about data. Data that do not fulfill the constraint must be changed:

Msg 547, Level 16, State 0, Line 14

The ALTER TABLE statement conflicted with the CHECK constraint "CK_ReleaseDate_Greater_2000".

The conflict occurred in database "tempdb", table "dbo.Movies", column 'release_date'.

August 11, 2014 7:41 PM

Leave a Comment

Privacy Statement