different results with select count(*)

Hi,
Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
quad processor...
Table with (4) indexes: fields a,b,c,d
Select count(a) from table
Select count(b) from table
Select count(c) from table
Select count(d) from table
Select count(id) from table
generates different results (!)
We have dropped and rebuilt the indexes.
The table in question has approx. 2 million rows. The
count(id) query is treturning 11 million rows.
Thoughts?
Thanks in advance,
DanHi,
The count will change based on the number of null values inside the table.
Select count(colmn) will count only the not null values inside the table.
To have the full record count use
select count(*) from table_name
Thanks
Hari
MCDBA
"dan" <djlucarelli@.pa1call.org> wrote in message
news:43e001c4732c$1a133c40$a301280a@.phx.gbl...
> Hi,
> Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
> quad processor...
> Table with (4) indexes: fields a,b,c,d
> Select count(a) from table
> Select count(b) from table
> Select count(c) from table
> Select count(d) from table
> Select count(id) from table
> generates different results (!)
> We have dropped and rebuilt the indexes.
> The table in question has approx. 2 million rows. The
> count(id) query is treturning 11 million rows.
> Thoughts?
> Thanks in advance,
> Dan|||> Thoughts?
Yes, don't allow NULLs, or use SELECT COUNT(*)
http://www.aspfaq.com/
(Reverse address to reply.)|||In addition to the other responses, you may have misconceptions about
how SQL-Server processes a query.
If NULLs are disallowed in the columns id, a, b, c and d, then the
queries
Select count(a) from table
Select count(b) from table
Select count(c) from table
Select count(d) from table
Select count(id) from table
will probably all be satisfied with the same query plan. The smallest
index will be used to count the total number of rows. It is highly
unlikely that the query "select count(a) from table" will use the index
on a, and the query "select count(b) from table" the index on b...
If the columns allow and contain NULLs: see the other responses.
Gert-Jan
dan wrote:
> Hi,
> Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
> quad processor...
> Table with (4) indexes: fields a,b,c,d
> Select count(a) from table
> Select count(b) from table
> Select count(c) from table
> Select count(d) from table
> Select count(id) from table
> generates different results (!)
> We have dropped and rebuilt the indexes.
> The table in question has approx. 2 million rows. The
> count(id) query is treturning 11 million rows.
> Thoughts?
> Thanks in advance,
> Dan
(Please reply only to the newsgroup)