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.
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.