Click here to Skip to main content
13,297,321 members (65,226 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


5 bookmarked
Posted 17 Jun 2011

SQL SERVER – Solution – Puzzle – SELECT * vs SELECT COUNT(*)

, 17 Jun 2011
Rate this:
Please Sign up or sign in to vote.
Earlier I have published Puzzle Why SELECT * throws an error but SELECT COUNT(*) does not. This question have received many interesting comments. Let us go over few of the answers, which are valid.

Earlier I have published Puzzle Why SELECT * throws an error but SELECT COUNT(*) does not.

This question have received many interesting comments. Let us go over few of the answers, which are valid. Before I start the same, let me acknowledge Rob Farley who has not only answered correctly very first but also started interesting conversation in the same thread.

The usual question will be what is the right answer. I would like to point to official Microsoft Connect Items which discusses the same.


tiberiu utan

Rob Farley

count(*) is about counting rows, not a particular column. It doesn’t even look to see what columns are available, it’ll just count the rows, which in the case of a missing FROM clause, is 1.

“select *” is designed to return columns, and therefore barfs if there are none available.

Even more odd is this one:

select ‘blah’ where exists (select *)

You might be surprised at the results…


The engine performs a “Constant scan” for Count(*) where as in the case of “SELECT *” the engine is trying to perform either Index/Cluster/Table scans.


When you query ‘select * from sometable’, SQL replaces * with the current schema of that table. With out a source for the schema, SQL throws an error.

so when you query ‘select count(*)’, you are counting the one row. * is just a constant to SQL here. Check out the execution plan. Like the description states – ‘Scan an internal table of constants.’

You could do ‘select COUNT(‘my name is adam and this is my answer’)’ and get the same answer.

Netra Acharya

Here, * represents all columns from a table. So it always looks for a table (As we know, there should be FROM clause before specifying table name). So, it throws an error whenever this condition is not satisfied.

Here, COUNT is a Function. So it is not mandetory to provide a table.

Check it out this:
SET @cnt = COUNT(*)
SET @cnt = COUNT(‘x’)


Select 1 / Select ‘*’ will return 1/* as expected.

Select Count(1)/Count(*) will return the count of result set of select statement.

Count(1)/Count(*) will have one 1/* for each row in the result set of select statement.

Select 1 or Select ‘*’ result set will contain only 1 result. so count is 1.

Where as “Select *” is a sysntax which expects the table or equauivalent to table (table functions, etc..). It is like compilation error for that query.


Hi Friends,
Count is an aggregate function and it expects the rows (list of records) for a specified single column or whole rows for *.

So, when we use ‘select *’ it definitely give and error because ‘*’ is meant to have all the fields but there is not any table and without table it can only raise an error.

So, in the case of ‘Select Count(*)’, there will be an error as a record in the count function so you will get the result as ’1′.

Try using : Select COUNT(‘RAMESH’) and think there is an error ‘Must specify table to select from.’ in place of ‘RAMESH’

Pinal : If i am wrong then please clarify this.

Sachin Nandanwar

Any aggregate function expects a constant or a column name as an expression.
DO NOT be confused with * in an aggregate function.The aggregate function does not treat it as a column name or a set of column names but a constant value, as * is a key word in SQL.

You can replace any value instead of * for the COUNT function.Ex

Select COUNT(5) will result as 1.

The error resulting from select * is obvious it expects an object where it can extract the result set.

I sincerely thank you all for wonderful conversation, I personally enjoyed it and I am sure all of you have the same feeling.

Reference: Pinal Dave (

Filed under: CodeProject, Pinal Dave, PostADay, Readers Contribution, Readers Question, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

India India
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 2200 articles on the subject on his blog at Along with 8+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is co-author of two SQL Server books - SQL Server Programming, SQL Wait Stats and SQL Server Interview Questions and Answers. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.

You may also be interested in...


Comments and Discussions

GeneralMy vote of 5 Pin
Kunal_Chowdhury19-Jun-11 20:00
mvpKunal_Chowdhury19-Jun-11 20:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171207.1 | Last Updated 17 Jun 2011
Article Copyright 2011 by pinaldave
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid