SQL Clone
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
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: 1478 Visits: 1058

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
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29307 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
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7762 Visits: 3288
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-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

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

I wasn't born stupid - I had to study.
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29307 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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2349 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
SSC Eights!
SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)

Group: General Forum Members
Points: 936 Visits: 451

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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2349 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
SSC Eights!
SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)

Group: General Forum Members
Points: 936 Visits: 451

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