The COUNT Function & NULLs

I'm still a bit under the weather from this cold so I'm going with a short and simple refresher this week.

We all at one time or another have used the SQL COUNT function to look at how many rows of something exists. Some of us have learned the hard way that you have to be careful when using this function. At the very least I hope to give you something to think about.

Let's create a table in our TestDB with this script.

CREATE TABLE [dbo].[tblTestCount]
(
   [id] [int] IDENTITY(1,1) NOT NULL,
   [AllowNulls] [int] NULL,
   [NoNulls] [int] NOT NULL
) ON [PRIMARY]
GO

Now lets put some data into the table.

INSERT INTO tblTestCount ([AllowNulls], [NoNulls])
VALUES (1,1)
,(1,0)
,(null,5)
,(5,4)
,(null,4)
,(3,3)
,(8,10)

Here are the queries we're going to run against the table. Before you run it, write down what you think the results for each query will be. 

SELECT COUNT(1) AS [Query1] FROM [dbo].[tblTestCount]
SELECT COUNT(*) AS [Query2] FROM [dbo].[tblTestCount]
SELECT COUNT([id]) AS [Query3] FROM [dbo].[tblTestCount]
SELECT COUNT([AllowNulls]) AS [Query4] FROM [dbo].[tblTestCount]
SELECT COUNT([NoNulls]) AS [Query5] FROM [dbo].[tblTestCount]
SELECT COUNT(DISTINCT [AllowNulls]) AS [Query6] FROM [dbo].[tblTestCount]

SELECT COUNT(DISTINCT [NoNulls]) AS [Query7] FROM [dbo].[tblTestCount]

SELECT COUNT(DISTINCT *) AS [Query8] FROM [dbo].[tblTestCount]

Did you get them correct? What if anything can you take away from this? Next week, removing duplicate data.