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

SQL SERVER - Puzzle - SELECT * vs SELECT COUNT(*) selectstar-1 Earlier this weekend I have presented at Bangalore User Group on the subject of SQL Server Tips and Tricks. During the presentation I have asked a question to attendees. It was very interesting to see that I have received various different answer to my question. Here is the same puzzle for you and I would like to see what your answer to this question.

Question: SELECT * gives error when executed alone, but SELECT COUNT (*) does not. Why?

SQL SERVER - Puzzle - SELECT * vs SELECT COUNT(*) selectstar
Select * – resulting Error
SQL SERVER - Puzzle - SELECT * vs SELECT COUNT(*) selectcountstar
Select count * – NOT resulting Error

Please leave your answer as a comment over here. If you prefer, you can blog post about this on your blog and put a link here. I will publish valid answer with due credit in future blog posts. Remember one should not use SELECT * in the production environment as there are many different disadvantages of the SELECT *. The primary disadvantage is that it retrieves the data most of the time which users are not using normally in the production display. For example, not all the time we need to retrieve last updated time or even GUID if they exists on the table. The purpose of this blog post was for demonstration purpose and just for fun.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL Azure – SQL Azure Throttling and Decoding Reason Codes
Next Post
SQL SERVER – BI Quiz – Troubleshooting Cube Performance

Related Posts

76 Comments. Leave new

  • Dera All,

    Select * is returning the error….
    Select Count(*) it is showing result it as 1 if we are not mentioned the table or if the table does not having the records..
    Select Count(1) is also same as above…
    select Count(‘Some text’) this is also returning the same as above two…
    My quiestion is What is the use of ‘*’ in this….
    ‘*’ means it will return all the row in the table as of my knowledge… but in this select count(‘some text’) the text is acting like a *

    Anybody Plz clarify this……

    Reply
  • abhimanyukumarvatsa
    June 7, 2011 10:14 am

    I think, SGA (System Global Area) has no definition for * when we use this alone.

    Reply
  • COUNT is function thats why its returning value on SELECT

    Reply
  • I guess, SELECT COUNT(*) is working like other scalar functions which we can use in select statement. Like SELECT LEFT(‘SQL Authority’, 3) etc. If we write SELECT COUNT(‘SQL’) it will return 1 too. I guess when FROM clause is missing, the COUNT function is counting the number of parameter or I can say what is has inside the () and every time it will return 1.
    SELECT * always expect a table/set. That’s why it is getting error.

    Reply
  • Hi ,
    I agree with Rodrigo Ribeiro .. count(*) is just counting the rows . It just counts how may rows returned . For aggregation functions there is no need to specify the table name , only argument is sufficient .
    But select “*” requires table name to return the data ..

    But i am surprised how this
    select ‘blah’ where exists (select *) is working , could you please also explain this ..

    Reply
  • Hey, pinal, save us time and give the answer :)

    Reply
  • 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.

    Thanks,
    Ramesh Vagh,
    Software Engineer

    Reply
  • Dan Zimmerman
    June 10, 2011 11:46 pm

    here’s a simple view…

    the select * command returns all columns from all rows in the table.

    if any data value in any row and/or column is corrupt or has a problem an error can be returned.

    there is also a higher probability of a hardware problem from returning all the data from the table as select * does.

    the select count(*) command only returns the count of rows in the table.

    as the select count(*) command does not return any actual data from the table, data corruption will likely not impact the select (*) command.

    have a nice weekend :-)

    Reply
  • Sachin Nandanwar
    June 12, 2011 9:52 pm

    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.

    Reply
  • Jaymin Soneji
    June 14, 2011 9:39 am

    Answer is very simple.

    as when you are trying to Execute “Select *”, you must specify table name in from Clause. because * awaits for Table to give outputs of Columns and Data. when you are trying to execute Select *, this * means what ?

    You have to specify query or Table to take output. so you will get an error when you will try to execute only “Select * ”

    now, the 2nd question is for : Select Count(*), it suppose to give you Count of Rows, it means if you try to Execute Select Count(1) or Select Count(*) or Select Count(Any Variable). then the output will be 1. same for all queries.

    Reply
  • When you say “SELECT *” you are writing a partial command: “SELECT these fields” without saving where they should come from. “*” is a wildcard which expands to “all fields” so you need to specify the table to read from.

    When you say “SELECT COUNT(*)” you are a different command: a SELECT that returns a single item, the COUNT, of the number of items. Because there is no list, the result comes out at 1 – the count itself.

    Reply
  • In My view,
    According to C++ ‘*'(asterick) is De reference operator which means “point to” the variable whose reference they store. it requires a reference. so here i mean in SQL we refer a
    And Count() is behaves like a function it requires 1 argument.
    For ex: select count(0) in place of count(*) it returns a value.

    Reply
  • In My view,
    According to C++ ‘*’(asterick) is De reference operator which means “point to” the variable whose reference they store. it requires a reference. so here i mean in SQL we refer a
    And Count() is behaves like a function it requires 1 argument.
    For ex: select count(0) in place of count(*) it returns a value.

    Reply
  • In My view,
    According to C++ ‘*’(asterick) is De reference operator which means “point to” the variable whose reference they store. it requires a reference. so here i mean in SQL we refer a tablename
    And Count() is behaves like a function it requires 1 argument.
    For ex: select count(0) in place of count(*) it returns a value.

    Reply
  • COUNT always return a integer value because it return number of rows

    Reply
  • RAJESH KUMAR JAISWAL
    June 22, 2011 6:34 pm

    hi,

    plz anybody reply me,

    i got an error of 14262 in log shipping configuration

    actually what i done is iam just started using sql server 2005, i configured the logshipping by using two instances of sql 2005 , instead of disabling the logshipping i deleted the jobs then again when i went to configure logshipping it is not possible i got an error of 14262.(email removed)

    Reply
  • Select Count(*) is an aggregate function used to detrmine the number of times the argument occurs, it will return 1 as default.

    Select * will throw an error because * is a wild character and not a function and function returns a value.

    Reply
  • Hi Pinal,

    i am new to sql server and was browsing for the solution.
    found a link in msdn

    so do you agree with those comments??

    Reply
  • Hi All,

    I have one question :)
    Please execute the following Queries…!

    SELECT COUNT(NULL) — this will generate error

    GO

    DECLARE @A INT
    SET @A=NULL
    SELECT COUNT(@A) ––this will not generate error why??

    Reply
  • I think count function expect table or variable.
    Select count(null)– Here count is not able to identify table or variable

    Second example do not give error.
    also count do not count null values.It shows 0 for it.
    in your example,if values of @A is 0 or above then its count is 1.
    See one more example,

    declare @t table(a varchar(10))
    insert into @t values (null)
    insert into @t values (”)
    insert into @t values (‘abc’)
    Select COUNT(a) from @t –Output is 2 because count() do not count null values

    If we write,
    Select count(*) from @t–Output is 3,because here we are telling count() to provide number of rows.

    Reply

Leave a Reply

Menu