Funny NULL in T-SQL

NULL is one funny word because it has some interesting behavior in T-SQL.

When you search NULL values in a table, usually you have to use IS NULL prediction. If you try use expression “= NULL”, you will get nothing by default unless you SET ANSI_NULLS OFF in your session. Seems T-SQL treat NULL as an object, right?

But when you insert records into one table, you can not insert two NULL value on the field with a UNIQE constraint. Also GROUP BY will put all NULL values in same group, ORDER BY will put all NULL values together. Now seems T-SQL treat NULL as a value, right?

Usually I will leave the field to allow NULL instead of NOT NULL and assign a default value. Because in real business, NULL can be a meaningful state, which can be we don’t know, or it will be decided later. Default value is not acceptable in these scenarioes because who knows the default value you use will be occupied by another business meaning later, especially you are getting data from another system. When I need search these NULL values, I will use IS NULL prediction. Or if I need do reporting and it is required to assign some value to the NULL values, I will use ISNULL() function.

So please take care when you are dealing with NULL.

 

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: