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

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Accommodating Null Values in SQL Server : Page 2

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




Application Security Testing: An Integral Part of DevOps

Finding Nulls Before They Do Damage

Any expression that encounters a null can return bad data. For instance, Figure 1 shows the results of comparing a null to a null. This statement has the potential to return no records, depending on your ANSI_NULLS setting.

Figure 1. Results of Null-to-Null Comparison: By ANSI standards, any comparison that includes a null returns null.
The ANSI_NULLS option is off (False) by default. That means that any null-to-null comparison returns True instead of null. When this option is on (True), any null-to-null comparison returns null. This particular default violates ANSI standards, but it prevents errors that might otherwise occur. How you set this option is a matter of preference, but knowing how it determines null evaluation is important.

The following statement turns the option on so you can see the potential for trouble:

USE AdventureWorks SET ANSI_NULLS ON SELECT EmployeeID FROM HumanResources.Employee WHERE ManagerID = NULL

If you peruse the records, you'll find one record with a null value in the ManagerID column. However, executing the preceding statement returns an empty result set—in other words, it doesn't return the existing record. Even worse, instead of a traditional error, you probably wouldn't catch the "error" exemplified by the empty result set.

Figure 2. IS NULL to Find Null Values: Regardless of the ANSI_NULLS setting, IS NULL catches null values.
The correct way to find null values, regardless of this option's setting, is to use IS NULL, as follows:

USE AdventureWorks SELECT EmployeeID FROM HumanResources.Employee WHERE ManagerID IS NULL

As you can see in Figure 2, you don't need to know the ANSI_NULLS option's setting. In addition, you guarantee that the statement will work correctly should someone reset that option.

Using IS NULL, you can easily find null values in a specific column, but searching several or all columns is a bit more complicated. At first, you might consider a complex statement using numerous OR clauses, but that isn't necessary. Simply concatenate fields using the following syntax:

SELECT list|* FROM table WHERE nulcol1 + nulcol2 + ... IS NULL

You can mix and match the columns or concatenate them all. Figure 3 shows the results of concatenating three fields:

USE AdventureWorks SELECT * FROM Person.Contact WHERE Suffix + EmailAddress + Phone IS NULL

Figure 3. Results of Concatenating Three Fields: You can mix and match columns or concatenate them all.
You still have to explicitly reference each column that you're checking, but this method saves a bit of typing. Don't try to concatenate columns that aren't nullable, as doing so will return an error. This method works only with nullable fields.

While this behavior can come in handy, it also can return unexpected results. For instance, the following statement concatenates first, last, and middle names:

Use AdventureWorks SELECT FirstName + ' ' + MiddleName + ' ' + LastName FROM Person.Contact

What Figure 4 shows might be a surprise: The ANSI_NULLS setting makes no difference in this case. If any name value is null, the resulting value is null. As shown in Figure 5, you can avoid this problem using ISNULL() as follows:

Use AdventureWorks SELECT FirstName + ' ' + ISNULL(MiddleName, '') + LastName FROM Person.Contact

Figure 4. ANSI_NULLS Setting Makes No Difference: Concatenating can return null values.
Figure 5. ISNULL() to Catch Nulls: Use ISNULL() to catch null values.

You don't have to wrap every column in ISNULL(); if column properties require a value, there's no possibility of a null. (FirstName and LastName aren't nullable.) The ISNULL() function replaces any null value with a specified replacement value using the following syntax, where checkexpression is the value or column you're checking for null values and replacementvalue is the value you're using to replace nulls:

ISNULL(checkexpression, replacementvalue)

In this case, the only nullable column in the example is MiddleName. Be aware that records with no middle name value will end up with two space characters between the first and last names.

Figure 6. ISNULL() Function Displays "None": Give users more meaningful information when they encounter null values.
SQL Server's CONCATENATE_NULL_YIELDS_NULL setting will affect the results of any expression that concatenates null values. The default is on (True), which means concatenating a null returns null. When turned off (False), SQL Server ignores the null, returning the concatenated results of the other non-null values. Similarly to the earlier IS NULL example, ISNULL() eliminates unexpected results should someone reset this option.

Even when a null value does no damage, it doesn't provide much value to users. They would benefit from more meaningful data. When viewing data, users might guess at what NULL means, but do you really want them to? Instead of displaying NULL (Figures 3 and Figure 4), the ISNULL() function in the following statement displays "None" as shown in Figure 6:

USE AdventureWorks SELECT Contact, ISNULL(Suffix, 'None') FROM Person.Contact

That, the user understands.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date