SQL Server – COUNT(1) Vs COUNT(*) Vs COUNT(1000) | Which one is fastest?

Well I often get this question from interviewers, team mates and my friends. Still many people don’t know the answer to this question. Well they all are same. Let me just create some sample data to prove it.

--

CREATE TABLE testCounts
(
	ID INT
)
GO

INSERT INTO testCounts VALUES ( 100 )
GO 10000

--

In the above statements I have created a table and inserted 10000 rows into it. Now we have to write SQL statements to fetch total number of rows from the table. Some of them are provided below.

--

SELECT COUNT(1) FROM dbo.testCounts

SELECT COUNT(-1) FROM dbo.testCounts

SELECT COUNT(*) FROM dbo.testCounts

SELECT COUNT(100) FROM dbo.testCounts

SELECT COUNT(10001) FROM dbo.testCounts

SELECT COUNT('Pawan') FROM dbo.testCounts

--

All the above statements will give us the same result i.e. 10000 rows. Well you can just put any junk in there still it will give the proper result. Check out the screen shot below for some of the outputs.

Pawan Khowal – Output Results – 10000 Rows

Now comes the main question, which one of these is fastest. Well they all are same in terms of performance. All are going to perform in the same manner. Check out the execution plans for all the queries. All are giving exact same execution plan.

Pawan Khowal – Execution Plans for Counts

I hope now things are clear. That’s all folks; I hope you’ve enjoyed the article and I’ll see you soon with more articles.

Thanks!

Pawan Kumar Khowal

MSBISKills.com