sql - How can SELECT COUNT(*) different from count of all records in the table? -

so have table:

create table table_name (     column_1   char(12)    not null,     column_2   char(2)     not null,     column_3   char(1)     not null,     column_4   int         not null,     column_5   char(2)     not null,     column_6   money       not null,     column_7   smallint    not null,     column_8   varchar(10) not null,     column_9   smallint    not null,     column_10  datetime    not null     primary key (column_1, column_2, column_3) ) 

select count(*) returns different value select distinct count(*). how can possible?

i tried

select count(*) (     select         column_1,         column_2,         column_3,         column_4,         column_5,         column_6,         column_7,         column_8,         column_9,         column_10      table_name     ) tmp 

which returned same count distinct query.

i'm little tired, hope i'm not missing obvious, can't see how primary key , fields being not null, there can different total count count of unique records.

btw, on sybase ase 15.

the discrepancy hundred or records out of half million. i'm seeing problem in several other tables, chose 1 example.

edit:

i should mention sake of completeness discovered problem when writing simple job copy table remote database. application recording number of read/write operations, failed qa because number of records in source database differed number of records in target database. both values obtained via count(*); count returned target (oracle 10g) same number of read/write operations recorded app. fields on source table defined not null , primary key defined, @ loss explain how application losing tiny number of records.

this when started using alternate queries listed above, both of agreed apps read/write count, count() value returned target. in other words, value did not match count() on source database.

in databases support it, count(*) doesn't retrieve records , count them -- instead fetches metadata field tracks number of rows (or approximate number of rows) presently stored in table. on other hand, when requires working actual data, dbms going fetch rows anyway, , count them expect to.

of course, it's reasonable expect that, regardless of how it's implemented, result of count(*) same more complex equivalent query. suggest then, (maybe) table's metadata corrupted somehow. (i'd 1 bet -- i'm not familiar sybase specifically, dbms have way force rebuild table metrics... might worth try here).

another possible explanation database's internal table row counter not designed 100% accurate. (this second possibility pure educated speculation... don't know whether true of sybase's row counter or not, might worth further investigation).

good luck!