Click here to Skip to main content
15,867,308 members
Articles / All Topics

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
17 Jun 2011CPOL4 min read 16.5K   5   1
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.

RGarvao

https://connect.microsoft.com/SQLServer/feedback/details/671475/select-test-where-exists-select

tiberiu utan

http://connect.microsoft.com/SQLServer/feedback/details/338532/count-returns-a-value-1

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…

Koushik

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.

amikolaj

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

SELECT *
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.

SELECT COUNT(*)
Here, COUNT is a Function. So it is not mandetory to provide a table.

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

Naveen

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.

Ramesh

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 (http://blog.SQLAuthority.com)


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

License

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


Written By
Founder http://blog.SQLAuthority.com
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 http://blog.sqlauthority.com. 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.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Kunal Chowdhury «IN»19-Jun-11 19:00
professionalKunal Chowdhury «IN»19-Jun-11 19: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.