Null value processing policy for SQL Server

server| Policy data integrity is a key point for any database system to ensure. 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

Use empty table values appropriately

A special case may occur with SQL Server: declaring referential integrity (DRI) may not be enforced in a table referencing the parent table because null values are not allowed. Even if the parent table does not contain null values, it may also contain null values in a column that refers to the parent table's primary KEY constraint or a unique constraint.

If the value from the parent table is currently unknown, there is no problem. For example, a parent table might be an address table, and a child table might contain contact information. For many reasons, you may not know the contact address to pass to the parent table for a while. This is a time based problem in which null values may be appropriate.

As shown in the following example, we create a parent table and insert two values into it.

SET NOCOUNT on
Gocreate TABLE Parent (pkey1 int IDENTITY not NULL CONSTRAINT pkparent PRIMARY key,col1 INT NULL) Goinsert parent (col1) VALUES (284) Goinsert Parent (col1) VALUES (326) go

The following code creates the child table and inserts a null value into the column referencing the parent table.

CREATE TABLE Child
(pkey1 int identityconstraint pkchild PRIMARY key,parentpkey1 int. nullconstraint fkchildparent FOREIGN keyreferences Pare NT (pkey1), col1 INT NULL) Goinsert (Parentpkey1, col1) VALUES (null,2) go

However, in the following code, you choose values from both the parent and child tables. Although the parent table does not contain null values, a null value is allowed in the column in which the child table refers to the parent table.

Then discard all tables and clear the database objects used by the demo.

SELECT * FROM Childgoselect * to Parentgodrop TABLE Child, Parentgo

Check the validity of the data in a foreign key that can be empty

If the primary key is composed of two columns, and a child table inherits the primary key as a foreign key that can be null, the wrong data may be obtained. You can insert a valid value in a foreign key column, but insert a null value in another foreign key column. You can then add a datasheet check constraint to check the validity of the data in an empty foreign key.

Any multiple-column foreign key may experience the same problem. So you need to add a check constraint to detect the exception. Initially, the check constraint checks for potentially empty values in all columns that make up the foreign key. Check constraints also check for values that cannot be empty in these columns. If two checks are passed, the problem is solved.

The following demo script shows such an exception and how to correct it with a check constraint.

     SET NOCOUNT ongocreate TABLE parent (pkey1 int IDENTITY not NULL, Pkey2 int
NOT NULL, C Ol1 INT null,constraint pk_parent PRIMARY KEY nonclustered (pkey1, Pkey2)) Goinsert parent (Pkey2) VALUES (2) INSERT pare NT (Pkey2) values ($) Insert parent (Pkey2) values () Insert parent (Pkey2) VALUES (one) goselect * from Parentgocrea TE TABLE Child (cpkey1 int IDENTITY not nullconstraint pk_child PRIMARY KEY nonclustered (cpkey1), Pkey1 int NULL, Pkey2 in T NULL, col1 INT null,constraint fk_parent_child FOREIGN KEY (pkey1, Pkey2) REFERENCES parent (Pkey1, Pkey2)) Goinsert Child (Pkey1, Pkey2) VALUES (NULL, goselect) * from Childgodelete childgoalter TABLE child with Nocheckadd CONSTRAINT ck_fk_parent_child CH ECK (Pkey1 is not null) OR (Pkey1 are null and Pkey2 is null)) Goinsert child (Pkey1, Pkey2) VALUES ( NULL, one) godrop TABLE child, Parentgo

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.