Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SELECT COUNT(*) or COUNT(1) Performance or Benefits? Expand / Collapse
Author
Message
Posted Tuesday, May 10, 2005 9:00 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 4:23 AM
Points: 663, Visits: 962

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
Post #181098
Posted Tuesday, May 10, 2005 9:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
* 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.
Post #181102
Posted Tuesday, May 10, 2005 10:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 2,923, Visits: 1,874
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
Post #181123
Posted Tuesday, May 10, 2005 10:36 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, December 15, 2014 5:06 PM
Points: 265, Visits: 166
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.
******************
Post #181126
Posted Tuesday, May 10, 2005 11:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, January 25, 2010 5:28 PM
Points: 1,132, Visits: 42
Darn good question!!! 




I wasn't born stupid - I had to study.
Post #181142
Posted Tuesday, May 10, 2005 11:41 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
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
Post #181147
Posted Tuesday, May 10, 2005 12:34 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, 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/
Post #181163
Posted Wednesday, May 11, 2005 8:49 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:18 AM
Points: 531, Visits: 433

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]))

 

 

 




Post #181477
Posted Wednesday, May 11, 2005 9:12 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, 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/
Post #181494
Posted Wednesday, May 11, 2005 9:21 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:18 AM
Points: 531, Visits: 433

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

 

 




Post #181500
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse