SQL Server's null value processing policy

Data integrity is any DatabaseThe focus of the system. No matter how well the system is planned, the problem of NULL data values always exists. This article discusses SQLThree problems involved in processing these values in server: Count, use empty table values, and foreign key processing.

Use count (*) to process null values

Most Aggregate functions can eliminate null values During computation. The count function is an exception. Use the count function for a column containing null values. The null values are eliminated from the calculation. But if the count function uses an asterisk, it calculates all rows, regardless of whether there is a null value.

If you want the count function to count all rows (including null values) in a given column, use the isnull function. The isnull function replaces a null value with a valid value.

In fact, for a collection function, if a null value may cause incorrect results, the isnull function is very useful. Remember that when an asterisk is used, the count function calculates all rows. The following example demonstrates the impact of null values on AVG and count 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

Proper use of empty table values

SQL Server may encounter a special situation: in a table that references the parent table, because null values are not allowed, the "declare reference integrity" (DRI) may not be forced. Even if the parent table does not contain null values, null values may be included in columns that reference the primary key constraint or unique constraint of the parent table.

If the value from the parent table is unknown, no problem will occur. For example, the parent table may be an address table, and the child table may contain contact information. For many reasons, you may not know the contact address to be sent to the parent table. This is a time-based issue, where 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 a sub-table and inserts a null value in the column that references the parent table.

Create Table child (Pkey1 int identityconstraint pkchild Primary Key, parentpkey1 int nullconstraint fkchildparent Foreign keyreferences parent (pkey1), col1 int null) Goinsert child (parentpkey1, col1) values (null, 2) Go

However, in the following code, select a value from both the parent table and the child table. Although the parent table does not contain null values, a null value is allowed in the column referenced by the child table.

Discard all tables and clear the database objects used in this demonstration.

Select * From childgoselect * From parentgodrop Table child, parentgo

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

If the primary key is composed of two columns, and a sub-Table inherits the primary key as a foreign key that can be null, the data may be incorrect. A valid value can be inserted in one foreign key column, but a null value can be inserted in another foreign key column. Then, you can add a data table check constraint to check the data validity in an empty foreign key.

Any foreign key with multiple columns may encounter the same problem. Therefore, you need to add a check constraint to detect exceptions. Initially, the check constraint checks the values that can be null in all columns that constitute the foreign key. Check constraints also check the values in these columns that cannot be empty. If both checks are passed, the problem is solved.

The following sample script shows this exception and how to use check constraints to correct it.

NULL values are required by all database developers and administrators. Therefore, to develop a successful application, you must know how to handle these values. This article shares with you some tips and techniques for processing null values.
Reprinted: http://www.51testing.com/html/96/n-142796.html