The best way I've found for using COUNT(MyCol) is to use either the identity column or the primary key column. That way there are no NULL results to worry about.

SELECT COUNT(NameID) FROM CountTestSET ANSI_NULLS ON;

SET QUOTED_IDENTIFIER ON;

IF EXISTS (SELECT object_id

FROM sys.objects

WHERE object_id = OBJECT_ID(N'[dbo].[CountTest]')

AND type in (N'U')

)

DROP TABLE dbo.[CountTest];

CREATE TABLE dbo.[CountTest]([NameID] INT NOT NULL IDENTITY(1,1),

[Name] [nvarchar](max)

);

INSERT INTO dbo.[CountTest] ([Name]) VALUES('Sally');

INSERT INTO dbo.[CountTest] ([Name]) VALUES(NULL);

INSERT INTO dbo.[CountTest] ([Name]) VALUES('Mary');

INSERT INTO dbo.[CountTest] ([Name]) VALUES('Jane');

INSERT INTO dbo.[CountTest] ([Name]) VALUES(NULL);

INSERT INTO dbo.[CountTest] ([Name]) VALUES('Bob');

INSERT INTO dbo.[CountTest] ([Name]) VALUES('Tom');

INSERT INTO dbo.[CountTest] ([Name]) VALUES(NULL);

SELECT COUNT(NameID) FROM dbo.CountTest; --Gives count of 8

SELECT COUNT(Name) FROM dbo.CountTest; --Gives count of 5

DROP TABLE dbo.CountTest;