Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Nov 2005
    Posts
    2

    Unanswered: 4 ways to know the record count of a table

    Please tell me the 4 ways to know the record count of a table?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by aravind786
    Please tell me the 4 ways to know the record count of a table?
    I usually print off the contents and then count the number of lines. WARNING - if your table is WIDE you need an A3 printer set to landscape.

    1 down 3 to go.
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2002
    Posts
    272
    select sum(qqq) from
    (select someField as fff, count(isnull(someOtherField, 'pootleflumpisafunnyperson')) as qqq from someTable group by someField)

    should give a fairly accurate approximation of the actual number.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use a theta self-join to assign a ranking number to each row, then inner join this result to a table of integers in an UPDATE statement, setting each integer to NULL if there is a match, and finally, select MIN(integer)-1 from the integers

    this would be so much easier in oracle, where you don't have to use the ranking join, you can use its builtin ROWNUM
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...or cheat off of somebody else taking the same class you are.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Count the number of data blocks occupied by the table and divide by the number of whole rows that each block will hold.

    -- This is all just a Figment of my Imagination --

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    use a theta self-join to assign a ranking number to each row, then inner join this result to a table of integers in an UPDATE statement, setting each integer to NULL if there is a match, and finally, select MIN(integer)-1 from the integers
    That's why I love these forums - you've just slashed our toner expenditure Rudy.
    Your db forums boycot does't extend to NZDFs then huh?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    Your db forums boycot does't extend to NZDFs then huh?
    that is correct

    i am not going to visit regularly, i am no longer going to delete spam, even if you report it, i am no longer going to rename threads to make them easier to search and to prevent others from wasting their time to find out what the brilliantly named "I need help!!!!!!!!!!" thread might be about, and i am not going to open threads that have gone unanswered for a day to see if i can help folks

    however, i might still browse these forums if i am extremely bored, which happens too infrequently for my liking, and maybe, if i feel like it, i might offer a reply, like in a "please do my homework for me" or "i can't find my ass with both hands, what does RTFM mean?" thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, well I'm not taking a test...and I can only think of 2....


    SELECT COUNT(*)
    sp_spaceused
    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.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    come on brett, it's not that hard

    here's another way: if the table has an IDENTITY column, determine the initial seed and increment values from the system seed table joined with the system increment table on the database and table names, then SELECT MAX(id) from the table, and using a bit of arithmetic, combine this with the seed and increment values to determine how many rows there would still be if none have been deleted, then run a query on the identity values to count how many are missing (i.e. actually were deleted), subtract this from the number of rows from the first step, and voila, the number of rows that are currently in the table

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    come on brett, it's not that hard

    here's another way: if the table has an IDENTITY column, determine the initial seed and increment values from the system seed table joined with the system increment table on the database and table names, then SELECT MAX(id) from the table, and using a bit of arithmetic, combine this with the seed and increment values to determine how many rows there would still be if none have been deleted, then run a query on the identity values to count how many are missing (i.e. actually were deleted), subtract this from the number of rows from the first step, and voila, the number of rows that are currently in the table


    You can't be serious......
    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.

  12. #12
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Brett Kaiser
    You can't be serious......
    I think he forgot the [sarcasm] tags...

    Regards,

    hmscott
    Have you hugged your backup today?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a reply with Non-Zero Deviosity Factor requires no sarcasm tags

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Why is it everyone forgets the obvious one.

    Use a packet sniffer, to sniff the results of "select * from table", take the total number of bytes returned, and divide by the rowlength.

  15. #15
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by MCrowley
    Why is it everyone forgets the obvious one.

    Use a packet sniffer, to sniff the results of "select * from table", take the total number of bytes returned, and divide by the rowlength.
    Maybe because you have to multiply the number of framing bits by 2e-1 before you can subtract them from the total bits transferred.

    -- This is all just a Figment of my Imagination --

Posting Permissions

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