COUNT Function and NULL Values in SQL Server

Hello to everyone,

In this article, I will give information about the COUNT function and NULL values in SQL Server.

Let me briefly explain what I mean by the COUNT function and NULL values.

As you know, the COUNT function gives the total number of records in the specified field or table.

I will continue with the example below.

--Creating the table

CREATE TABLE TabloValues
(
    ID INT PRIMARY KEY IDENTITY(1, 1),
    Names NVARCHAR(50)
);


--Adding data to the table

INSERT INTO dbo.TabloValues
SELECT 'ROSE' UNION ALL
SELECT NULL UNION ALL
SELECT 'DAISY' UNION ALL
SELECT NULL UNION ALL
SELECT 'ANNA' UNION ALL
SELECT 'JULIA' UNION ALL
SELECT 'JULLY' 


--Examining the COUNT Function Usage

SELECT
COUNT(Names) AS CounterValue_1
,COUNT(*) AS CounterValue_2
,COUNT(1) AS CounterValue_3
FROM TabloValues;

 

When you run the above code block, you will see the following result.

COUNT Function and NULL Values in SQL Server

As you can see, I created a table and then entered values ​​into it. Now let’s examine the SELECT query I wrote to examine the COUNT function.

When I said COUNT(Name) , it brought us the number of records without taking into account NULL values. Briefly, he counted 5 of 7 records for us. It gave us the result by deducting the number of 2 NULL records.

When I said COUNT(1) and COUNT(*) , it brought us the number of 7 records by taking into account NULL values. COUNT(1) and COUNT(*) are interchangeable constructs. Both give the same result. Both do not ignore NULL values ​​and both return the row count of a given table. Of course, there is one point you should pay attention to. COUNT(*) should be used with caution in tables with a large number of columns due to performance issues.

I wanted to explain this difference in terms of being useful, many of you may or may not know.

Good luck to everyone in business and life.

323 Views

Yavuz Selim Kart

I try to explain what I know in software and database. I am still improving myself by doing research on many programming languages. Apart from these, I am also interested in Graphic Design and Wordpress. I also have knowledge about SEO and Social media management. In short, I am a determined person who likes to work hard.

You may also like...

Don`t copy text!