Professional Web Applications Themes

Count number of records - Microsoft SQL / MS SQL Server

Hi, I'm using SQL Server 2000. When I return all rows from a table it doesn't give me a count of the number of records, like in Access for instance. Is there a way of quickly finding out how many records there are in a table? thanks. Raul....

  1. #1

    Default Count number of records

    Hi,
    I'm using SQL Server 2000. When I return all rows from a table
    it doesn't give me a count of the number of records, like in
    Access for instance.
    Is there a way of quickly finding out how many records there
    are in a table?
    thanks.
    Raul.


    Raul Guest

  2. #2

    Default Re: Count number of records

    Hi Ray,

    SELECT COUNT(columnname) _will_ not include NULLs, all aggregate functions
    (SUM, AVG etc) disregard NULLs. COUNT(*) is not really an aggregate
    function, and as someone (I believe it was Anith Sen) remarked the other
    day, COUNT(*) and COUNT(columnname) are totally separate functions.

    There can be a performance difference between COUNT(*) and COUNT(columnname)
    depending on the indexes that exist on the table. COUNT(*) or
    COUNT(columnname) for a NOT NULL column can be satisfied by scanning the
    smallest index available, where COUNT(columnname) for a NULLable column can
    only be satisfied by scanning an index that includes that column, or failing
    that a table scan.

    Have a look at the execution plans for the following queries:
    USE Northwind
    SELECT COUNT(*) FROM products
    SELECT COUNT(productname) FROM products
    SELECT COUNT(categoryid) FROM products
    SELECT COUNT(unitsinstock) FROM products

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Ray Higdon" <com> wrote in message
    news:phx.gbl... 
    believe 
    >
    >[/ref]


    Jacco Guest

  3. #3

    Default RE: Count number of records

    Hi Raul,

    One way is to use count(*)

    select count(*) from table
    go

    Sincerely,
    Yih-Yoon Lee [Microsoft]
    Microsoft SQL Server Support

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

    Yih-Yoon Guest

Similar Threads

  1. display records with a 0 count value
    By jhurst in forum MySQL
    Replies: 2
    Last Post: October 16th, 07:54 PM
  2. ASP Group Same Records and Give Total Count
    By gotcha in forum ASP Database
    Replies: 1
    Last Post: August 6th, 08:50 PM
  3. Get count of child records
    By Ricardo in forum IBM DB2
    Replies: 2
    Last Post: September 25th, 06:09 AM
  4. Count related sub datasheet records
    By Bradley in forum Microsoft Access
    Replies: 3
    Last Post: August 14th, 02:23 AM
  5. newbie : trying to count records in mysql table
    By jim in forum PHP Development
    Replies: 1
    Last Post: July 11th, 02:25 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139