Presents your 
SQL SERVER E-NEWSLETTER for October 1, 2002

<------------------------------------------->

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

----------------------------------------

READ MORE ABOUT DEVELOPMENT

SQL BASICS: STRING DATA TYPES
Data type implementations vary from database to database, but a working
knowledge of the SQL specification will always give you a good idea of
what's going on. We break down the basic rules of deploying string data types.
http://cl.com.com/Click?q=38-bLkmIRMtLSMcXStnhYl5UAuHcbcR 

SQL BASICS: SELECT STATEMENT OPTIONS
Learn how to master the SELECT statement to form truly useful data
queries. We explain functions, clauses, and keywords that will make 
your life a lot easier. Check out the article discussion in our forum.
http://cl.com.com/Click?q=4d--URsIKZhQxyQKXadaNf52obN0pnR 

------------------------------------------