Recently I saw a post regarding the count(1) and count(*)
COUNT(*) on a table with a lot of columns and a lot of rows can take a lot of time and memoryCREATE DATABASE [test]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\test.mdf' , SIZE = 512000KB , FILEGROWTH = 102400KB )
LOG ON
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\test_log.ldf' , SIZE = 102400KB , FILEGROWTH = 102400KB )
GO
use [test]
go
1. Heap Table
--Create Heap table
CREATE SEQUENCE dbo.mysequence
START WITH 1
INCREMENT BY 1
GO
select NEXT VALUE FOR dbo.mysequence AS [ID],
a.name, b.create_date,
c.type_desc into mytable
from sys.objects a, sys.objects b, sys.objects c
GO
--Save table page status
CREATE TABLE DBCCResult (
PageFID NVARCHAR(200),
PagePID NVARCHAR(200),
IAMFID NVARCHAR(200),
IAMPID NVARCHAR(200),
ObjectID NVARCHAR(200),
IndexID NVARCHAR(200),
PartitionNumber NVARCHAR(200),
PartitionID NVARCHAR(200),
iam_chain_type NVARCHAR(200),
PageType NVARCHAR(200),
IndexLevel NVARCHAR(200),
NextPageFID NVARCHAR(200),
NextPagePID NVARCHAR(200),
PrevPageFID NVARCHAR(200),
PrevPagePID NVARCHAR(200)
)
GO
INSERT INTO DBCCResult EXEC ('DBCC IND(test,mytable,-1) ')
GO
--with DBCC IND, we can know there are total 4747 pages in table mytable
a) test count(*)
--Clean Cache
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
--test count(*)
SET STATISTICS IO ON
set STATISTICS time on
set STATISTICS profile on
GO
select count(*) from mytable
GO
set STATISTICS profile off
set STATISTICS time off
set STATISTICS io off
GO
--save all buffer pool page
select * into Buffer1
from sys.dm_os_buffer_descriptors where database_id=db_id('test')
GO
--check if all data pages have been read to buffer pool
select count(*)
from Buffer1 b inner join DBCCResult d on b.page_id=d.PagePID
you will see the execution plan is the same as count(*), and so is IO statistics
and you can see 4747 pages read to buffer pool as well.
2. Clustered Index Table
created clustered index on table
ALTER TABLE dbo.mytable ADD CONSTRAINT
PK_mytable PRIMARY KEY CLUSTERED
(
ID
) ON [PRIMARY]
GO
and rerun the test upper, I still got the same result against count(1) and count(*), they both use clustered index scan(table scan), and read the same page into buffer pool
3. Heap + Non-Clustered Index
so based on my testing, there is no performance difference between them, if you see any difference between count(1) and count(*), please let me know , I am interesting on researching it 🙂
