Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SELECT COUNT(*) or COUNT(1) Performance or Benefits?


SELECT COUNT(*) or COUNT(1) Performance or Benefits?

Author
Message
Express12
Express12
Say Hey Kid
Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)

Group: General Forum Members
Points: 693 Visits: 1021

What are the advantages of using COUNT(*) -or- COUNT(1) in the following examples:

declare @Address1 int
select @Address1 = count(1) from dbo.Customers where ID < 0
select @Address1 as Address1count

declare @Address1 int
select @Address1 = count(*) from dbo.Customers where ID < 0
select @Address1 as Address1count

 



BT
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
* has the advantage that sql server will determine which is the best way to find the count(*). This will allow the server to do an index seek/scan without actually fetching the data on the hd. count(1|colname) doesn't always offer this possibility.

I know somebody else will have a more complete explanation of this process.
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3668 Visits: 3113
COUNT(*) or count(1) returns a count of all records including NULLS.

COUNT(expression) returns a count of all non-null records.

The first two don't seem to have any performance difference or differences in the execution plan they generate.

LinkedIn Profile

Newbie on www.simple-talk.com
Dinakar Nethi-176633
Dinakar Nethi-176633
SSC Veteran
SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)

Group: General Forum Members
Points: 273 Visits: 188
I think Remi is right in that when you use COUNT(*) the optimizer chooses which index to use and returns the count faster as compared to COUNT(1) which need not necessarily be an indexed column.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************
Farrell Keough
Farrell Keough
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 42
Darn good question!!! 

I wasn't born stupid - I had to study.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
From books online on msdn :

count(*)

Specifies that all rows should be counted to return the total number of rows in a table. COUNT(*) takes no parameters and cannot be used with DISTINCT. COUNT(*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT(*) returns the number of rows in a specified table without eliminating duplicates. It counts each row separately, including rows that contain null values.

And from a post on Oracle (ok it's not sql server but the same logic applies).
Count(*) returns the total row count
count(1) returns all rows where 1 is not null >> simplified version it returns all rows.
So Oracle is optimized to use count(*) instead of count(number) because count(1) wouldn't offer any performance gain. And I can only assume that sql server will use the same logic. I've also tried many different variation of count() but unless the specified column allows nulls the plan is always the same : index scan. With a non-indexed nullable column : clustered index scan.

You can run this and see for yourself :


Select count(autoval) from dbo.SysColumns --cis
Select count(*) from dbo.SysColumns --is
Select count(1) from dbo.SysColumns --is
Select count(id) from dbo.SysColumns --is
Chris Hedgate
Chris Hedgate
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1497 Visits: 7
My first article at SQL Server Central discussed this: Advice on using COUNT( )

As David says, COUNT(*) or COUNT(1) (or COUNT(9917456) or whatever) will be treated exactly the same way by the optimizer. They both simply say "count the number of rows in the set", so the optimizer is free to do that exactly as it whishes. The fastest way to count the number of rows in a table is to do a scan on the most narrow non-clustered index of the table, so that is that plan that will be chosen if possible.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
peterhe
peterhe
Mr or Mrs. 500
Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)

Group: General Forum Members
Points: 534 Visits: 448

It's upto what you are going to achieve. If the first column is NOT NULL, COUNT(*) and COUNT(1) have the same result. If it's NULLable, the result may be different.

USE northwind

GO

SET NOCOUNT ON

SET ANSI_WARNINGS ON
select COUNT(*) FROM dbo.Customers
select COUNT(CustomerID) FROM dbo.Customers
select COUNT(Region) FROM dbo.Customers
select COUNT(FAX) FROM dbo.Customers

Result:

           
-----------
91

           
-----------
91

           
-----------
31

Warning: Null value is eliminated by an aggregate or other SET operation.
           
-----------
69

Warning: Null value is eliminated by an aggregate or other SET operation.

Their execution plans are slightly different. The last one uses clustered index scan and others use index scan on my machine.

select COUNT(*) FROM dbo.Customers

StmtText                                                                   
---------------------------------------------------------------------------
  |--Compute Scalar(DEFINE[Expr1002]=Convert([Expr1003])))
       |--Stream Aggregate(DEFINE[Expr1003]=Count(*)))
            |--Index Scan(OBJECT[Northwind].[dbo].[Customers].[Region]))

select COUNT(CustomerID) FROM dbo.Customers

StmtText                                                                   
---------------------------------------------------------------------------
  |--Compute Scalar(DEFINE[Expr1002]=Convert([Expr1003])))
       |--Stream Aggregate(DEFINE[Expr1003]=Count(*)))
            |--Index Scan(OBJECT[Northwind].[dbo].[Customers].[Region]))

select COUNT(Region) FROM dbo.Customers

StmtText                                                                        
--------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE[Expr1002]=Convert([Expr1003])))
       |--Stream Aggregate(DEFINE[Expr1003]=COUNT_BIG([Customers].[Region])))
            |--Index Scan(OBJECT[Northwind].[dbo].[Customers].[Region]))

select COUNT(FAX) FROM dbo.Customers

StmtText                                                                                   
-------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE[Expr1002]=Convert([Expr1003])))
       |--Stream Aggregate(DEFINE[Expr1003]=COUNT_BIG([Customers].[Fax])))
            |--Clustered Index Scan(OBJECT[Northwind].[dbo].[Customers].[PK_Customers]))

 

 

 





Chris Hedgate
Chris Hedgate
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1497 Visits: 7
>If the first column is NOT NULL, COUNT(*) and COUNT(1) have the same result. If it's NULLable, the result may be different.

Could you elaborate on that please?

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
peterhe
peterhe
Mr or Mrs. 500
Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)

Group: General Forum Members
Points: 534 Visits: 448

Maybe I didn't make me clear. What I mean is that if the Nth column can have NULL value in the table, COUNT(*) and COUNT(N) may have different value.

In the example I given above,

the CustomerID is NOT NULL in the table; Fax and Region can be  NULL in the table and some records REALLY have null values for the them. You can see the four COUNT() give different results.

The table is defined as:

CREATE TABLE [dbo].[Customers] (
 [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Customers] WITH NOCHECK ADD
 CONSTRAINT [PK_Customers] PRIMARY KEY  CLUSTERED
 (
  [CustomerID]
   ON [PRIMARY]
GO

 CREATE  INDEX [City] ON [dbo].[Customers]([City]) ON [PRIMARY]
GO

 CREATE  INDEX [CompanyName] ON [dbo].[Customers]([CompanyName]) ON [PRIMARY]
GO

 CREATE  INDEX [PostalCode] ON [dbo].[Customers]([PostalCode]) ON [PRIMARY]
GO

 CREATE  INDEX [Region] ON [dbo].[Customers]([Region]) ON [PRIMARY]
GO

 

 





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search