Data Management

Strategies for approaching null values with SQL Server

Dealing with null values is a fact of life for every database developer. Take advantage of these tips to properly deal with them in SQL Server for your next project.

By Kaye Garten

Data integrity is a critical aspect of any database system. No matter how well a system is planned, the issue of null data values is always present. Let's examine the three aspects of dealing with these values in SQL Server: counting, using null table values, and dealing with foreign keys.

Handle null values with COUNT(*)
Most aggregate functions eliminate null values in calculations; one exception is the COUNT function. When using the COUNT function against a column containing null values, the null values will be eliminated from the calculation. However, if the COUNT function uses an asterisk, it will calculate all rows regardless of null values being present.

If you want the COUNT function to count all rows of a given column, including the null values, use the ISNULL function. The ISNULL function can replace the null value with a valid value.

In fact, the ISNULL function is very valuable for aggregate functions where null values affect the results in an erroneous fashion. Remember that when using an asterisk, the COUNT function will calculate all rows. The following is sample code that illustrates the impact of null values in the AVG and COUNT aggregate functions:
 
SET NOCOUNT ON
GO
CREATE TABLE xCount
(pkey1 INT IDENTITY NOT NULL
    CONSTRAINT pk_xCount PRIMARY KEY,
Col1 int NULL)
GO
INSERT xCount (Col1) VALUES (10)
GO
INSERT xCount (Col1) VALUES (15)
GO
INSERT xCount (Col1) VALUES (20)
GO
INSERT xCount (Col1) VALUES (NULL)
GO
SELECT AVG(Col1) AvgWithoutIsNullFunctionOnCol1,
AVG(ISNULL(Col1,0)) AvgWithIsNullFunctionOnCol1,
COUNT(Col1) NoIsNullFunctionOnCol1 ,
COUNT(ISNULL(Col1,0)) UsingIsNullFunctionOnCol1,
Count(*) UsingAsterisk
FROM xCount
GO
DROP TABLE xCount
GO


OUTPUT:
AvgWOIsNullFnctnCol1 AvgWIsNullFnctnCol1 WOIsNullFnctnCol1 WIsNullFnctnCol1 UsingAsterisk
-- -------------------- ------------------- ----------------- ---------------- -----------
--          15                 11                3              4            4


Learn an appropriate use of null table values
There's a situation in SQL Server where Declarative Referential Integrity (DRI) is not enforced because nulls are allowed in the table referencing the parent table. Even though the parent table doesn't contain null values, the child table may contain null values in the column that references the parent table’s primary or unique constraint.

This may be appropriate when the value from the parent table is currently unknown. For example, the parent table may be an address table, and the child table may contain contact information. The contact address, which is to be passed to the parent table, may be temporarily unknown for any number of reasons. This is a time-dependent issue where null values may be appropriate.

For example, in the following code, the parent table is created and two values are inserted.
 
SET NOCOUNT ON
GO
CREATE TABLE Parent
(pkey1 INT IDENTITY NOT NULL
    CONSTRAINT pkParent PRIMARY KEY,
col1 INT NULL)
GO
INSERT Parent (col1) VALUES (284)
GO
INSERT Parent (col1) VALUES (326)
GO

 

In the code below, the child table is created, and a null value is inserted into the column referencing the parent table.
 
CREATE TABLE Child
(pkey1 INT IDENTITY
CONSTRAINT pkChild PRIMARY KEY,
Parentpkey1 INT NULL
CONSTRAINT fkChildParent FOREIGN KEY
REFERENCES Parent(pkey1),
col1 INT NULL)
GO
INSERT Child (Parentpkey1, col1) VALUES (null,2)
GO

 

However, in the following code, the values are selected from both the parent and child tables. Even though the parent table doesn't contain null values, the child table will allow a null value for the column that references the parent table.

The tables are then dropped to clean up the database objects for this demonstration.

 
SELECT * FROM Child
GO
SELECT * FROM Parent
GO
DROP TABLE Child, Parent
GO

 

Check for valid data in nullable foreign keys
When you have two columns that comprise the primary key, and a child table inherits the primary keys as nullable foreign keys, you may have bad data. You can insert a valid value into one of the foreign key columns and null into the other foreign key column. Then, you can add a table-check constraint that checks for valid data in the nullable foreign keys.

This anomaly may occur for any multicolumn foreign key. So you will need to add a check constraint to test for the anomaly. Initially, the check constraint will check for nullable values in all columns, which comprise the foreign key. The check constraint will also check for non-nullable values within these columns. If both checks pass, the anomaly should be circumvented.

The following is a sample script that illustrates such an anomaly and the check constraint that corrects it.
 
SET NOCOUNT ON
GO
CREATE TABLE parent (pkey1 INT IDENTITY NOT NULL, pkey2 INT NOT NULL, col1 INT NULL,
CONSTRAINT pk_parent PRIMARY KEY NONCLUSTERED ( pkey1, pkey2))
GO
INSERT parent (pkey2) VALUES ( 2 )
INSERT parent (pkey2) VALUES ( 85 )
INSERT parent (pkey2) VALUES ( 41 )
INSERT parent (pkey2) VALUES ( 11 )
GO
SELECT * FROM parent
GO
CREATE TABLE child (cpkey1 INT IDENTITY NOT NULL
CONSTRAINT pk_child PRIMARY KEY NONCLUSTERED (cpkey1),
pkey1 INT NULL, pkey2 INT NULL, col1 INT NULL,
CONSTRAINT fk_parent_child FOREIGN KEY (pkey1, pkey2)
REFERENCES parent (pkey1, pkey2))
GO
INSERT child (pkey1, pkey2) VALUES ( null, 85 )
GO
SELECT * FROM child
GO
DELETE child
GO
ALTER TABLE child WITH NOCHECK
ADD CONSTRAINT ck_fk_parent_child CHECK
((pkey1 IS NOT NULL AND pkey2 IS NOT NULL) OR
(pkey1 IS NULL AND pkey2 IS NULL) )
GO
INSERT child (pkey1, pkey2) VALUES ( null, 11 )
GO
DROP TABLE child, parent
GO

 

Null values are a fact of life for every database developer and administrator, so knowing how to deal with these values is imperative for a successful application. In this article, I’ve shared with you a few tips and techniques for dealing with nulls in your data.
0 comments

Editor's Picks