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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4246 Visits: 1082

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 Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67673 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.
Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16800 Visits: 3403
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
www.simple-talk.com
Dinakar Nethi-176633
Dinakar Nethi-176633
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: 1109 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
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

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

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

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

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

Group: General Forum Members
Points: 2728 Visits: 453

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
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

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

Group: General Forum Members
Points: 2728 Visits: 453

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