Null value processing policy for SQL Server

Null values are encountered by all database developers and administrators. Therefore, to develop successful applications, you must know how to handle these values. This article shares with you some of the techniques and techniques of null-value processing.

Data integrity is the key to any database system. Regardless of how well the system is planned, the problem of NULL data values always exists. This article explores the 3 issues involved in handling these values in SQL Server: counting, using null table values, and foreign key processing.

Handling Null values with COUNT (*)

Most aggregate functions can eliminate null values at the time of calculation, and the Count function is the exception. Use the Count function for a column that contains a null value, which is eliminated from the calculation. However, if the Count function uses an asterisk, it calculates all rows, regardless of whether there is a null value.

Use the IsNull function if you want the Count function to count all rows (including null values) for a given column. The ISNULL function replaces the null value with a valid value.

In fact, for aggregate functions, the IsNull function is useful if null values can lead to error results. Remember that when you use an asterisk, the Count function calculates all the rows. The following example shows the effect of NULL values in 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