none
Counting the number of rows RRS feed

Answers

  • Select count(*) from TableName


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Thursday, May 9, 2013 2:40 PM
    Moderator
  • A better query is

    SELECT COUNT(1) FROM <TABLENAME>


    Thursday, May 9, 2013 2:54 PM

All replies

  • Select count(*) from TableName


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Thursday, May 9, 2013 2:40 PM
    Moderator
  • A better query is

    SELECT COUNT(1) FROM <TABLENAME>


    Thursday, May 9, 2013 2:54 PM
  • A better query is

    SELECT COUNT(1) FROM <TABLENAME>



    both are same.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Thursday, May 9, 2013 4:37 PM
    Moderator
  • 99% of the time, you'd go with 'select count(*) from table'... 

    there may be the odd occasion you want a quick 'estimate' of rows, but even a select count(*)... takes a long time.  You could use:

    SELECT CAST(OBJECT_SCHEMA_NAME(id) AS VARCHAR(40)) + '.' + CAST(OBJECT_NAME(id) AS VARCHAR(40)) AS 'Table',
           ROWS AS 'Rows'
    FROM sys.sysindexes
    WHERE OBJECT_NAME(id) not like 'sys%' and indid = 1
    ORDER BY 'Table';
    go
    I can't remember where I got this from, so apologies to whoever I've cited.



    Thanks, Andrew


    Thursday, May 9, 2013 4:46 PM
  • try this,

    sp_spaceused test_hunt

    it also gives the no.of rows in table


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Shah Bhavik Thursday, May 9, 2013 5:19 PM
    Thursday, May 9, 2013 5:16 PM
  • There's a performance hit with *
    Thursday, May 9, 2013 5:20 PM
  • There's a performance hit with *

    Can you demonstrate that?

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Thursday, May 9, 2013 5:20 PM
    Moderator
  • Of course this is not the first time that this is mentioned. Usually, the argument for this claim of a "performance hit" is that (for some unknown reason) the optimizer would have to find out what all these columns are.

    But it is all conjecture. The optimizer knows that COUNT(*) is a cardinality search, and has no relation to any actual column. The query plan won't show any expanding as result of COUNT(*).

    So far, no one has been able to provide a script that demonstrates any performance benefit of using COUNT(1) over COUNT(*) on SQL Server. At least not since I started with SQL Server in the 90s. But I am always open to something new... if you can provide it.


    Gert-Jan

    Thursday, May 9, 2013 7:23 PM