Understanding NULL values in SQL Server

A NULL indicates that a value for an entry is not available. This makes a null different from a zero or an empty or blank value. You'll find NULLs valuable when you need to know if a value is either unknown or missing. Many database designers (but not all) eschew the use of NULLs and recommend that you use default values in your tables. It's common practice to substitute the character string "NA" or "N/A" when a value is not available.

NULLS have implications when it comes to grouping, sorting and in particular statistical analysis; but NULLS can still be very useful. One place you can't have NULLS are in primary keys (each value must be unique), or where an identity property for a column must contain a valid value.

SQL Server inserts a NULL into any column in a new row where you don't set the value for that column, and where a default value isn't assigned. For any column with a default value, a NULL is replaced by that default value. You can also set the default value to NULL by using the NULL keyword.

You can set default values in the Enterprise Manager's New Table and Design Table views; and there you can allow or disallow NULLS. If you set a default value for a column, then that default value is always used when there isn't a value specified for entry. Thus, if you allow NULLs, then you will only see NULLs when there is no entry and no default value set, or when you specifically enter the NULL value. For a column where NULLs are disallowed, no data

    Requires Free Membership to View

entry or entering a NULL value will create an error condition.

If you use a NULL as part of a query, how NULL values are handled is a function of the SQL-92 ANSI standard. Any NULLs used in a comparison such as =, <, or > will evaluate to FALSE. If the ANSI NULLs option is OFF, then SQL Server will make this comparison, and if a value in both columns is NULL, then the comparison will evaluate to TRUE.

Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics.

This was first published in March 2005

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.