RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Accommodating Null Values in SQL Server : Page 3

The key to working with null values properly is to accommodate them consistently. Learn a few tricks that will help you do just that.


How Aggregates Evaluate Null

Because nulls have no value, the aggregate functions SUM(), AVG(), MIN(), and MAX() ignore them. Using best practices, you should set a default value, such as 0, to avoid the problem of nulls in your calculations. However, keep in mind that aggregates will evaluate 0 and that might not be what you want. SQL Server lends a bit of help in this regard. When eliminating null values, SQL Server can warn you if you simply turn on the following option:
However, the SET ANSI_WARNINGS ON setting also affects how SQL Server handles division by zero, arithmetic overflow, and character/binary data truncation, so be sure you learn its full potential. The important distinction is that adding null to any value returns null and the aggregate functions circumvent that standard by eliminating nulls.

If you want to evaluate all values, you must account for those values at the table level by forcing a default value. If that's not possible, use COALESCE() to return a value other than null in the expression. For example, the first AVG() function shown in Figure 7 doesn't evaluate null values. However, the second one does—technically, the second one is evaluating 0's, not nulls. Neither statement is better than the other. Whether a function ignores nulls or not depends upon your objective.

Figure 7. First AVG() Function Doesn't Evaluate Null Values: Control whether aggregates evaluate null values.

The COUNT() function is a bit different from the other four aggregates because it has two forms:

  • COUNT(column): Returns the count of rows in a table, less the null values in column
  • COUNT(*): Returns the count of rows in a table

As you can see in Figure 8, the following forms return the same results:

SELECT COUNT(*) — Count(column) AS NullCount

FROM tbl
However, if you specify the column as follows, the statement returns 0:
Figure 8. COUNT(column) Eliminates All Nulls Before Evaluating: Depending on the form you use, COUNT() will ignore or evaluate nulls.
SELECT COUNT(column) AS NullCount
FROM tbl
That's because COUNT(column) eliminates all the nulls before it evaluates anything. Consequently, there are no null values to count.

If You Allow Them, You Must Handle Them

If even one column in your database is nullable, you have to consider how a null value will affect your data. Knowing how SQL Server evaluates nulls and how to handle them will prevent those nulls from working their way into your data and returning errors.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date