SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 How to add a NOT NULL constraint without check
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

yeek
Starting Member

6 Posts

Posted - 06/03/2005 :  15:15:19  Show Profile
I have a table with million of records, one column suppose to be NOT NULL, but we don't have a NOT NULL CONSTRAINT on it yet. I know the existing data are all NOT NULL. Is there a way I can add a NOT NULL constraint without checking existing data?

When I use

ALTER TABLE <TableName>
ALTER COLUMN <ColumnName> <DataType> NOT NULL

It is extreamly slow.

Any suggestion will be appreciated.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 06/03/2005 :  15:22:32  Show Profile
How did you check that the Column has no nulls

SELECT COUNT(*) FROM myTable99 WHERE Col IS NOT NULL?

Also. it's slow because of the logging.

Also, nr (Nigel) had a great article about ALTER Causing a lot of wasted space. Don't know if that would apply with an Alter or not. Regardless, I would do:

1. Create a table with structure you want
2. bcp out all the data
3. bcp the data in to the new object
4. rename the original table to soemthing else
5. change the new table to the old name



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

yeek
Starting Member

6 Posts

Posted - 06/03/2005 :  15:31:14  Show Profile
Thanks for the reply.

I used following query to check the existance.
SELECT TOP 1 *
FROM <TableName> WITH(NOLOCK)
WHERE ColName IS NULL

Is there a solution that I can do without interrupt others.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 06/03/2005 :  15:32:30  Show Profile  Visit tkizer's Homepage
quote:
Originally posted by yeek


Is there a solution that I can do without interrupt others.



No. Whatever solution you go with, it'll impact your users, so this should be done during a maintenance window.

Tara
Go to Top of Page

yeek
Starting Member

6 Posts

Posted - 06/03/2005 :  15:37:27  Show Profile
I know I can add a trigger to validate future insert and update on this column to insure NOT NULL, without interrupt others.

But that is not a good choice, is it?

Edited by - yeek on 06/03/2005 15:39:36
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/03/2005 :  15:38:13  Show Profile
How long does it take to rename a table? Microseconds?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/03/2005 :  15:45:40  Show Profile
quote:
Originally posted by yeek

I know I can add a trigger to validate future insert and update on this column to insure NOT NULL, without interrupt others.

But that is not a good choice, is it?



Depends....You'll incur more overhead.

Why is the solution I gave you not a good idea?


EDIT: And the funny thing, is that the update to millions of rows is Already causing more locking and logging and probably giving your users more grief than what I suggested.


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Edited by - X002548 on 06/03/2005 15:47:08
Go to Top of Page

TimS
Posting Yak Master

USA
198 Posts

Posted - 06/03/2005 :  16:10:01  Show Profile
ALTER TABLE doc_exd WITH NOCHECK
ADD CONSTRAINT exd_check CHECK (column_a IS NOT NULL)

Any reason the above won't work.

Tim S
Go to Top of Page

yeek
Starting Member

6 Posts

Posted - 06/03/2005 :  21:28:23  Show Profile
Thanks TimS, I will try see if NOCHECK can be used.

Go to Top of Page

yeek
Starting Member

6 Posts

Posted - 06/03/2005 :  21:32:59  Show Profile
Thanks Brett, you solution is good, but I have about 5 tables referencing this one, and the 5 children tables also have referencing tables.
Go to Top of Page

yeek
Starting Member

6 Posts

Posted - 06/04/2005 :  15:16:02  Show Profile
It works great. Thanks

quote:
Originally posted by TimS

ALTER TABLE doc_exd WITH NOCHECK
ADD CONSTRAINT exd_check CHECK (column_a IS NOT NULL)

Any reason the above won't work.

Tim S


Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000