Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlilliterate
Starting Member
40 Posts |
 Posted - 2008-02-28 : 23:55:29
|
| Hi all,One of my columns is the table has some Null values, and I Would like to stop having NULL values into that column any more.I know, If I alter the column to NOT NULL will throw me an error, since it does a batch update.Is there any way to achieve this...Thanks... |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-02-29 : 00:03:45
|
| [code]Alter Table dbo.foowith nocheck add constraint col_check1 check(col1 is not null)[/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-02-29 : 11:57:41
|
quote:
Originally posted by harsh_athalye
Alter Table dbo.foowith nocheck add constraint col_check1 check(col1 is not null)
On thing to be careful of when using NOCHECK is how the optimizer handles that column. From BOL: The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table CHECK CONSTRAINT ALL. So, if you are going to change a column to NOT NULL, you should remove/update all the NULL vales so you do not have to add a constraint with a NOCHECK. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-02-29 : 12:09:30
|
| sqlilliterate -- thing logically about what you are trying to do here. If you don't want NULL values in your column, and you have NULLS in there, the first thing you need to do before trying to set any constraints is to figure out what you want to do with your existing NULLS and change those values with an UPDATE statement. Then, you can configure the column to no longer allow NULLS. Does this make sense? Why would you want a column that disallows NULLS, but still has NULL values in there?- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
|
|
|
Changing the Column from NULL to NOT NULL