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

  • Thank you Kumar Harsh ….!

    Reply
  • Hi,
    I have a question :

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

    Thank you,

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

      Reply
  • Sanjay Monpara
    June 6, 2012 11:12 am

    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)

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

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

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

    Reply
  • Select * returns the records from the specific object whereas select count(*) used to returns some calculation purpose so it returns 1.

    Reply

Leave a Reply

Menu