none
SQL SERVER 2008 . Not Null vs Check Constraint

    Question

  • Hi,

    I have a table with 7 columns. One of the column data type is Varchar(max).

    Table have 9 crore records.

    I want to the enforce not null constraint to the varchar(max) column. If i add the not null constraint to the existing records(9 crore records) It will take more time because of page split.

    Instead of adding not null constraint i have added the below check constraint.

    alter table t1 add constraint c1 check(column is not null).

    My questions is the above check constraint will affect my insert/update statement?.

    Which constraint will give better performance for my insert and update statements.(Not null or Check)

    Is there any performance differece between the constraints?


    Please vote if you find this posting was helpful or Mark it as answered.

    Tuesday, July 16, 2013 6:30 AM

Answers

  • Read Books Online and you will see that a NULL value in a column will always cause the check constraint to return true, so a check constraint will not do what you are expecting.

    That is not correct.  It is true that if the result of the test in the check constraint is UNKNOWN because a value in the column(s) is NULL, then the check constraint will allow that row to be inserted.  So if your check constraint is

    check(MyData = 'a')

    then you will still be able to insert a row where the column MyData is NULL because the result of that test is UNKNOWN when MyData is NULL.  But the check constraint

    check(MyData is not null)

    returns FALSE when MyData is NULL, so that check constraint does indeed prevent rows with NULL's from being inserted (or the column being set to NULL by an UPDATE or MERGE).  For example, try

    create table FooTest(i int primary key, MyData varchar(10));
    go
    alter table FooTest add constraint c1 check(MyData is not null);
    go
    insert FooTest(i, MyData) Values (1,Null);
    go
    drop table FooTest;

    That row insert will be rejected.

    Tom

    Tuesday, July 16, 2013 1:45 PM
  • NOT NULL is more "low level" or "core" in SQL Server, so I'd assume that it is a faster check for the db engine and potentially there are other optimizations too available by the fact that the column cannot be null (known at a lower level if you specify NOT NULL). Whether or not those potential differences will be measurable and worth while for you is of course hard to say...

    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, July 16, 2013 2:54 PM
  • As others have mentioned, if you only alter the null-ability of a column, then the actual change is just a metadata change. In other words, the change will not produce any page splits.

    Personally, I prefer the NULL-ability property over the equivalent CHECK constraint, because it is a standard property. For example, for Primary Key columns it will automatically be set to NOT NULL when you declare the Primary Key.

    From a performance point of view, for your situation (which is: a populated table), there are two stages:

    1. Adding the constraint / changing the NULL-ability. Both approaches require a table scan / index scan to check for any conflicting values. The performance is the same
    2. After the change, for every Insert and relevant Update. I expect any performance difference that you are able to measure to be negligible. So from a performance point of view, there is no real difference


    Gert-Jan

    Tuesday, July 16, 2013 7:06 PM

All replies

  • First of all do not change the structure of a table during peak business hours especially if the table contains large number of rows. Try the changes in off-business hours or during maintenance window periods.

    Since the table is very large making a column NOT NULL takes time because it has to check whether any columns contain NULL values that prevent the change. Otherwise this is a meta data change. I don't think NOT NULL will cause a page split..

    If you really want to alter the table to make a column NOT NULL, and in no time, then create a CHECK constraint WITH NOCHECK option like below:

    ALTER TABLE yourLargeTableName
    	WITH NOCHECK
    	 ADD CONSTRAINT CHK_constraintName CHECK (columnName IS NOT NULL);
    But I don't recommended this method. You have to make sure, the already existing data do not contain NULL.


    Krishnakumar S

    Tuesday, July 16, 2013 7:07 AM
  • The CHECK constraint is used to limit the value range that can be placed in a column.

    If you define a CHECK constraint on a single column it allows only certain values for this column.

    If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

    As a conclusion, 'Not null' and CHECK constraint are all validating the input value to the column.


    Many Thanks & Best Regards, Hua Min

    Tuesday, July 16, 2013 7:13 AM
  • Read Books Online and you will see that a NULL value in a column will always cause the check constraint to return true, so a check constraint will not do what you are expecting.

    Tom G.

    Tuesday, July 16, 2013 1:22 PM
  • Read Books Online and you will see that a NULL value in a column will always cause the check constraint to return true, so a check constraint will not do what you are expecting.

    That is not correct.  It is true that if the result of the test in the check constraint is UNKNOWN because a value in the column(s) is NULL, then the check constraint will allow that row to be inserted.  So if your check constraint is

    check(MyData = 'a')

    then you will still be able to insert a row where the column MyData is NULL because the result of that test is UNKNOWN when MyData is NULL.  But the check constraint

    check(MyData is not null)

    returns FALSE when MyData is NULL, so that check constraint does indeed prevent rows with NULL's from being inserted (or the column being set to NULL by an UPDATE or MERGE).  For example, try

    create table FooTest(i int primary key, MyData varchar(10));
    go
    alter table FooTest add constraint c1 check(MyData is not null);
    go
    insert FooTest(i, MyData) Values (1,Null);
    go
    drop table FooTest;

    That row insert will be rejected.

    Tom

    Tuesday, July 16, 2013 1:45 PM
  • NOT NULL is more "low level" or "core" in SQL Server, so I'd assume that it is a faster check for the db engine and potentially there are other optimizations too available by the fact that the column cannot be null (known at a lower level if you specify NOT NULL). Whether or not those potential differences will be measurable and worth while for you is of course hard to say...

    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, July 16, 2013 2:54 PM
  • As others have mentioned, if you only alter the null-ability of a column, then the actual change is just a metadata change. In other words, the change will not produce any page splits.

    Personally, I prefer the NULL-ability property over the equivalent CHECK constraint, because it is a standard property. For example, for Primary Key columns it will automatically be set to NOT NULL when you declare the Primary Key.

    From a performance point of view, for your situation (which is: a populated table), there are two stages:

    1. Adding the constraint / changing the NULL-ability. Both approaches require a table scan / index scan to check for any conflicting values. The performance is the same
    2. After the change, for every Insert and relevant Update. I expect any performance difference that you are able to measure to be negligible. So from a performance point of view, there is no real difference


    Gert-Jan

    Tuesday, July 16, 2013 7:06 PM