Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2006
    Posts
    57

    Unanswered: Getting number of rows

    Is there a simple way to get the number of rows of a table besides going through and counting all of the rows programmatically?

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Luke101
    Is there a simple way to get the number of rows of a table besides going through and counting all of the rows programmatically?
    Code:
    SELECT rows FROM sysindexes
    WHERE id = OBJECT_ID('table_name') 
    AND indid < 2
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Luke101
    Is there a simple way to get the number of rows of a table besides going through and counting all of the rows programmatically?
    if by "programmatically" you mean retrieving all the rows and returning them to your application program (asp, php, whatever), then the answer is a resounding yes!

    use the COUNT() function:
    Code:
    select count(*) from daTable
    this query returns a single row consisting of a single column containing an integer which is the number of rows in the table

    neat, eh?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by r937
    if by "programmatically" you mean retrieving all the rows and returning them to your application program (asp, php, whatever), then the answer is a resounding yes!

    use the COUNT() function:
    Code:
    select count(*) from daTable
    this query returns a single row consisting of a single column containing an integer which is the number of rows in the table

    neat, eh?
    Just FYI,COUNT() ,when used in any form other than COUNT(*), ignores NULL values.So be cautious about that...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  5. #5
    Join Date
    Sep 2006
    Posts
    57
    ok..cool..i will try both techniques..my sql skills are pretty rusty anyway and need to learn different ways to do things

    thanks everyone

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Selecting from sysindexes is technically faster than using count(*), as if a human would ever notice the difference, but the sysindexes value returned may not be accurate if statistics on the table are not up to date. And running UPDATE STATISTICS on all your tables will take a lot of time, so use the count(*) method if you need accuracy.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    if you have a large number of rows (say a few million) it's definitely better to go to sysindexes or call sp_spaceused (which hits sysindexes), as long as you don't need need the accuracy of count(*). count(*) is a pretty expensive way to get the count if there are many rows.

  8. #8
    Join Date
    May 2002
    Location
    New York
    Posts
    35
    the easiest way:
    sp_spaceused <table name>

  9. #9
    Join Date
    Sep 2006
    Posts
    57
    I have been reviewing this thread here and have been trying to figure out how to use sysindex
    rundra stated that I could use sysindex like this but would I use this command verbatim with exception of the 'table_name'?

    Code:
    SELECT rows FROM sysindexes
    WHERE id = OBJECT_ID('table_name') 
    AND indid < 2

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You could use a cursor

    Why do you need all the counts btw?

    just kidding about the cursor
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you have a large number of rows, but you don't actually need an accurate answer, then you can get away with not using COUNT(*)

    ... but then i have to ask, why do you care what the approximate number is?

    why don't you just print "the answer is... um... very large"

    i'm really curious under what circumstances you don't care about the actual count but do care about some other number that might not be anywhere near the correct answer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Sep 2006
    Posts
    57
    I need the count for my paging system...

    So, if I have a 1000 rows and my page size is 20 then my total pages will be 50. Is there a way that I can get an accurate count using the sysindex?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    an accurate count for a paging algorithm?

    my advice: don't bother

    nobody is gonna page through a paged result set all the way to the end
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by r937
    an accurate count for a paging algorithm?

    my advice: don't bother

    nobody is gonna page through a paged result set all the way to the end
    quite right. and even if you use count(*), it's only accurate at the instant the query is executed.

    some other process can come along behind you and insert/delete, making the "accurate" value you fetched with the count(*) method no longer correct.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •