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

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)

About these ads

9 thoughts on “SQL SERVER – Solution – Puzzle – SELECT * vs SELECT COUNT(*)

  1. Hi Pinal,

    Mine answer was also correct with diffeent scenarios. I wrote -

    Both SELECT * and SELECT COUNT(*) requires table name to be read.

    SELECT * gives list of records with all columns from the table.

    SELECT COUNT(*) gives the count of resulting rows. Here, SELECT COUNT(*) is also giving an error for table name but returns a single row contains error message. Hence, SELECT COUNT(*) gives 1 as an output.

    – Shekhar

  2. Pingback: SQL SERVER – Free Online Training on .net and SQL Journey to SQLAuthority

  3. Hi Pinal,
    Is there any possibility to convert table row values into Columns by using SQL Query, If yes, then please could you explain more on that.

    Thanks in advance.
    RaviPola.

  4. hi Pinal,
    if we fire “select count(*) OR select count(‘asd’) OR select count(123)”
    still it reply with 1.
    as well all know * stands for ALL.
    when we fire select * we must specify from where.
    COUNT is an inbuilt aggregate function. and it require 1 argument to execute.
    i think it returns 1(true) bcoz it execute in sql engine.
    it need 1 argument. and it execute successfully.
    it return the query value if we specify from
    else it return function executed successfully (true i.e 1).

  5. I have connect my application SQL database.Based on data application is hanging is it future any other way is there to connect or increase capacity in SQL 2005

  6. something strange i have noticed regarding the count(*) while used with the group by

    it always return 1 if the table is empty

    i have this code

    update tbl1
    set tbl1.eleCount = tbl2.VoteCount
    FROM tblNames tbl1
    INNER JOIN
    (select eleNum, count(*) AS VoteCount
    from tblEntries
    GROUP BY eleNum)tbl2
    ON tbl1.eleNum = tbl2.eleNum

    i use this in after Delete trigger in tblEntires table
    if there is no records at all in the tblEntires all the eleNum records in the tblNames are set to 1vote …. it never update to zero

    how can this be solved?

  7. Pingback: SQL SERVER – Tricks to Replace SELECT * with Column Names – SQL in Sixty Seconds #017 – Video « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #033 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s