Article from the IT Expert Network forum, author builder
This article explores the 3 issues involved in handling these values in SQL Server: counting, using null table values, and foreign key 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 when they are computed; 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:
Copy Code code as follows:
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.
Copy Code code as follows:
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 Parent (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.
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.
