Dealing with null in sql queries in SQL Server 2000/2005

We may need to deal with null values often when we write queries in SQL Server 2000/2005. Here I provide some examples that are commonly used. One of my friend asked how to check whether a field is null, he tried col1=null but didn't worked. So I thought I will share this small but useful examples to all.

1) To check whether a field/column value is null
Just check if it is null

Eg:
select * from tbl where col1 is null;

2) To check whether a field/column value is not null
Just check if it is not null

Eg:
select * from tbl where col1 is not null;

3) To set a field/column value to null (update a field value to null)
Just set it to NULL

Eg:
update tbl set col1= NULL Where val1 = 1;

Another important Note:
When we want to take the maximum value (max) or the total(sum) using aggregate functions, it is always good to check for null values.If the table do not have any records or the result of the query is null, this may result in error.

select @nextVal = max(col1) + 1 from table1; --Not preffered way

So we will try
select @nextVal = max(isnull(col1,0)) + 1 from table1; --Not always success
Hey, this is also not working...

Ok finally, the right way
select @nextVal = isnull(max(col1),0) + 1 from table1;

To find the sum
select @Total = isnull(sum(col1),0) from table1;

Happy Coding ....
:)