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

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 question 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?

Select * - resulting Error

Select * - resulting Error

Select count * - NOT resulting Error

Select count * - NOT resulting Error

Please leave your answer as 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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

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

  1. 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…

  2. I think that SQL Server consider the COUNT(*) because, we can do “SELECT 1″. But, when we do “SELECT *” the engine try replace * by column names of a table … but what the table ? My opinion is this.

    []’s

  3. 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.

  4. COUNT() is a scalar syetem defined function as we know any scalar function can be called using SELECT as like SELECT COUNT(*), SELECT GETDATE() etc.

    But, SELECT * expects a table or table type userdefined function as we intend to fetch columns from table / table type userdefined function (* here refers to all columns )

    • (ajay)
      COUNT(*) is not a scalar expression! is an aggregate function.

      (Rob Farley)
      select ‘blah’ where exists (select *)

      I surely didn’t expect that result!!! can you explaint it?

  5. 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.

  6. COUNT statement just evaluates the number of rows returned by select and it counts the rows with null or error values…I guess that is the case.

  7. The Difference between Select * and Select Count(*) is that Select * always followed by a object and it returns the records from that object where as Select Count(*) is used to do some kind of calculation in tha table/object. As Select Count(*) takes null values in considerartion so it returns 1

  8. 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.

  9. “Select *” will return all columns from a table (or a result set), if no table specified, error happens.

    “Select count(*)” will return the number of a table (or result set), if no table specified, it will scan an internal table of constants which has 1 row ( I got this from Actual Execution Plan). No sure why about this?

  10. “Select *” looks for columns but no from clause mentioned it gives error
    whereas “Select count(*)” just counts the rows including NULL row, it does not look for From clause.

  11. Select * requires column names to be specified for a table….no table name means no column names ..so it will give error…

    Select count(*) does nothing but returns the count of rows from temporary resultset which has single cell (like DUAL in oracle) …means only 1 row and thus returns 1

  12. Shekhar check this,

    SELECT COUNT(*)
    if @@Error0
    print ‘Error’
    else
    print ‘No Error’

    SELECT COUNT(*) –it simply return one @@rowcount

    Declare @t table (id int)
    Select count(cn) from
    (Select count(*) cn from @t)t4

  13. (Koushik)

    The Constant Scan may be the answer. As i read, when you have a Constant Scan:

    “You’ll see situations where the query has to create a row to hold it’s data before it can access data from tables”

    if this is the case it would explain the result ‘1’ to the query Select count(*)

  14. How we call Scalar function

    Select [dbo].[fnName]
    Tabular function
    Select * from [dbo].[fnName]

    I think same reason.

  15. Hello,

    The statement is a syntax error, because it can’t be parse by the compiler because the statement “*” is recognize only follow by a table name(or in the case you want to select from a table in multijoin select statement). Instead “COUNT(*)” can be parse because COUNT can recognize * like parameter and when the compiler try to parse the instruction select count(*) everything is ok.

    An intersting observation is that you can create a function call schemaName.* but you never can call it, because the compiler don’t find any table called “Schemaname” in your Select statement. (In case that you call your table SchemaName or have an alias will select the rows from the table:

    select dbo.*
    from dbo.TableName as dbo

  16. When we execute
    Select *
    SQL CLR engine try to find FROM and table name to select records but not get so return Error.

    When we execute
    Select Count(*) it return 1
    Here Count is an agreegate function
    * is a character which user select all column
    if CLR engine dont get any column then it count as simple character and return 1 based on agrregate maths criteria.
    If we execute
    SELECT COUNT(100) or any character like SELECT COUNT(‘100′) it return 1 based on agrregate calculation.

  17. 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:
    Collapse

    “SELECT * FROM myTable”

    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.

  18. SELECT COUNT (*)

    Here * Specifies that all rows should be counted to return the total number of rows in a table. COUNT(*) takes no parameters and cannot be used with DISTINCT. COUNT(*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT(*) returns the number of rows in a specified table without getting rid of duplicates. It counts each row separately. This includes rows that contain null values.

    Also, COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.

    COUNT(ALL {expression}) evaluates expression for each row in a group and returns the number of nonnull values.

    COUNT(DISTINCT {expression}) evaluates expression for each row in a group and returns the number of unique, nonnull values.

    For return values greater than 2^31-1, COUNT produces an error.

    {expression} is an expression of any type except text, image, or ntext. Aggregate functions and subqueries are not permitted.

    *Referenced MSDN.*

    Regards,
    Harsh Baid
    .NET Developer
    Hidden Brains Infotech Pvt Ltd.

  19. For me its simple Count is a method and it takes an argument, so select count(*) has given output. Select * is throwing sytaxtical error as it need a tablename and the from keyword to compelte the select query.

  20. I agree with amikolaj.

    * is replaced with the COLUMN-list of the TABLE. If there is no TABLE, there is nothing to replace.

    COUNT(*) means COUNT everything in the output. The * is not replaced. Hence, there is no error.

  21. 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

  22. 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 mandatory to provide a table.

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

  23. 1. (Select *)

    Select Retrieves rows from the database & enables to
    select one or many rows or columns from one or many
    tables in SQL Server.

    here Select * lacks the basic structure of from and table name so give error .

    2. Select Count(*)

    Count is an aggregate function that returns the number of items in a group.count always return an integer data value

    Expression is any type except text, image, or ntext. Aggregate functions and subqueries are not permitted

    so when we queried Select Count(*) it returns the integer value.

  24. Select *- Here After SELECT clause * is neither an expression nor a column name associated to table/view etc. so SELECT clause fails to identify *, hence error.

    Where as

    Select Count(*) – If we see the general syntax of COUNT(*) is :COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } ) , COUNT(*) does not require an expression parameter because, by definition, it does not use information about any particular column (from MSDN) so it is valid expression to SELECT cluse and hence query analyzer will understand it and hence give the result as 1 without error.

  25. Both select * and select count(*) requires a table name. However select count(*) does not throw an error. This is because count is an aggregate function and it will always return a value!!

  26. I am agree with Sharath

    Actually select * and select count(*) requires a table name.
    select count(*) does not give an error because aggregate function always return a value.

    This is my opinion.

    • Aggregate functions always return a value but why, in this case, 1 instead of 0 ???

      Regardless that this sintax doesn’t result in a error the behavior is not expected and, in my opinion, doesn’t make any sence.

      if you run
      SELECT COUNT(*) FROM TableName WHERE 1 1
      the result is 0
      if you run
      SELECT COUNT(*)
      the result is 0!

      we can discuss why SELECT COUNT(*) doesn’t give an error but the result of the script isn’t logical

  27. Select * requires a table name, when parsing the statement it results in an error.
    SELECT COUNT(*) is like using SELECT GETDATE(), it is executing a function, hence there is no error

  28. Count (param) is a aggregate function in T-Sql similar to
    sum( param), Max(param) etc….

    None of the aggregate functions requires table (or From clause)

    * (star) is an operand in sql which means :
    1. Multiplication operand
    2. dataset’s All columns (when used individually in select query)
    3. join operand (=* or *= etc…)

    so if * has to be used in a query it should server any one purpose above.

    In “select * ” query none of the mentioned purposes are server hence error.

    In “select count(*)” , * acts like param to aggregate function. This aggregate function does not require any tables in the list hence it retuns some results.

  29. Hi Pinal,
    thanks for the observation.

    my opinion is here:

    Select count(*) ,

    select count(0) ,

    select count(1) , and

    select count(”) all returns value [1]. because count() will always take not NULL expression.

    and if we try to pass any NULL exp. then it will return [0].
    Run the following

    see:

    select cast(null as int) output [NULL]

    test:

    select count(cast(null as int)) output [0]

    so the my assumption is that because of “asterisk *” is special keyword or wildcard character in SQL and treating it as not NULL expression just like other values with the combination of count() function.

    just look at this:

    select count($) output[1]

    output is 1 again and $ sign is also treated as not NULL expression with combination.

    and if we run

    Select *
    output :Msg 263, Level 16, State 1, Line 1
    Must specify table to select from.

    where select * is expecting a combination of [from table name].

  30. “Select * ” gives error message b’cas for the “select ” command its compulsory to have column_name(for all columns; we use *) and table_name with From.
    B’cas it’s syntax is prepared that.
    But in case of “Select Count(*)”, Count is an aggregate function which requires a paramater which is not null then it gives 1 and not gives error message.

  31. Select Count(*) is an aggregate function used to detrmine the number of times the argument occurs, hence 1.

    Try Select Count(20) or any other argument and the result would be 1.
    When used in conjunction with a table or view it checks for the ocuurances within the object. My two bits

  32. Pingback: SQL SERVER – Puzzle – Statistics are not Updated but are Created Once Journey to SQLAuthority

  33. select 1 — output– 1
    select 0 — output– 0
    select ‘a’ — output– a

    where as

    select * –here * is not included in ‘ ‘ for char type so it will through a message

    and

    select count(1)–1
    select count(2)–1

    select count(*)–1 here *, 1 , 2 are items so COUNT(*) returns the number of items in a group

  34. Without FROM clause SELECT works to evaluate the expression. As per the SQL Server standard ‘*’ is used to list all the columns of the table/view/function(tablular). If table is absent in the SELECT clause then definitely it will raise an error. While executing COUNT(*) without FROM Clause COUNT function evaluates the input parameter and work accordingly. In other word I would say aggregate function COUNT takes care its input parameters based on its limit.

  35. select count(*) with no table uses a “constant scan”. The constant scan produces a single row for the aggregate function to populate since no other rows exist. Since there is no table, the only row is the row produced by the constant scan so you get a count of 1. You can see this if you look at the execution plan.

  36. following up on my previous post, I believe a constant scan will be used to generate a record anytime sql server needs a record to exist before it can evaluate the entirety of a tsql statement. This is why the statement

    select ‘blah’ where exists (select *)

    also produces a record when select * by itself does not.

  37. Hi,

    Both Select * and Select count(*) will create temporary datasets to display the results to the user. Select * will populate the dataset with the error message, since it expects the table name and columns, the error message is displayed to the user where as Select count(*) will count the number of rows in the dataset, which is always 1 for this case. The one row, one column which has the error message in it.
    Hence Select * returns the error and Select Count(*) returns 1.

    Thanks,
    Rajshekhar

  38. 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.

  39. 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……

  40. Pingback: SQL SERVER – Question to You – When to use Function and When to use Stored Procedure Journey to SQLAuthority

  41. Pingback: SQL SERVER – Three Puzzling Questions – Need Your Answer Journey to SQLAuthority

  42. Pingback: SQL SERVER – Three Puzzling Questions – Need Your Answer Journey to SQLAuthority

  43. 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.

  44. 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 ..

  45. 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

  46. 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 :-)

  47. 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.

  48. 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.

  49. 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.

  50. Pingback: SQL SERVER – Solution – Puzzle – SELECT * vs SELECT COUNT(*) Journey to SQLAuthority

  51. Pingback: SQL SERVER – Solution – Puzzle – SELECT * vs SELECT COUNT(*) « Journey to SQLAuthority

  52. 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.

  53. 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.

  54. 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.

  55. 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)

  56. 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.

  57. 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??

  58. 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.

  59. Hi,
    I have a question :

    what is the different between COUNT(*) and COUNT(1) which is better regarding performance and optimization ?

    Thank you,

    • There are a few things you can put in COUNT().

      COUNT(*)
      COUNT(1)
      COUNT(Column)
      COUNT(DISTINCT Column)

      COUNT(DISTINCT Column) looks for distinct values in the column, so it has a bunch of extra work to do.
      COUNT(Column) looks for non-null values in that column, so it still has to read through it, unless the column doesn’t allow null, in which case it really just has to think about how many rows there are.
      So COUNT(1) sounds very similar to COUNT(Column) when Column doesn’t allow nulls – but SQL sees it’s a constant and handles it as such, just counting rows.
      And COUNT(*) is a recognised pattern which is understood as “just count rows”.

      So COUNT(1) and COUNT(*) should be regarded as the same. I prefer COUNT(*) as it’s considered a recognised pattern. I never both using COUNT(1) – especialy since if you’re doing something like SUM(CASE…END), then you might have values of 1, 0, -1, whatever, being produced by your CASE statement, and so when I see COUNT(1), I have to start wondering whether it’s actually what I want or not. Plus, the old “ORDER BY 1″ gives meaning to the 1, and I don’t want any kind of confusion around that either.

      Hope this helps,

      Rob

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

  61. when we are using count(*) function with select statement then Sql Server internally using dummy table which have one column (like oracle’s Dual table)

  62. When you give Select Count(*) it scans an internal table of constants with 1 row, so it returns result as 1. Check the Execution plan.

  63. Select Count(*) is treating it self as SELECT 1. Means Select . While SELECT * is incomplete syntax It is looking for the table or some object which is equivalent to table.

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

  65. Count(*) is an aggregate system defined function so it won’t throw error. select * is partial statement so it will throw error.

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