title: How can SELECT COUNT(*) different from count of all records in the table?
body: <p>So I have a table:</p>
  <pre><code>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)
  )
  </code></pre>
  <p><code>SELECT COUNT(*)</code> returns a different value than <code>SELECT DISTINCT COUNT(*)</code>. How can this be possible?</p>
  <p>I also tried</p>
  <pre><code>SELECT COUNT(*) FROM (
      SELECT
          COLUMN_1,
          COLUMN_2,
          COLUMN_3,
          COLUMN_4,
          COLUMN_5,
          COLUMN_6,
          COLUMN_7,
          COLUMN_8,
          COLUMN_9,
          COLUMN_10
       FROM TABLE_NAME
      ) TMP
  </code></pre>
  <p>which returned the same count as the distinct query.</p>
  <p>I can't see how with a primary key and all fields being NOT NULL, there can be a different total count than the count of unique records.</p>
  <p>BTW, this is on Sybase ASE 15.</p>
  <p>The discrepancy is a hundred or so records out of a half million. I'm also seeing this problem in several other tables, but chose just one for the example.</p>
  <h2>Edit</h2>
  <p>I should mention for the sake of completeness that I discovered this problem when writing a simple job to completely copy this table to a remote database. My application was recording a certain number of read/write operations, but failed QA because the number of records in the source database differed from the number of records in the target database. Both values were obtained via <code>COUNT(*)</code>; the count returned from the target (Oracle 10g) was the same as the number of read/write operations recorded by my app. As all fields on the source table are defined NOT NULL and a primary key is defined, I was at a loss to explain how my application was losing a tiny number of records.</p>
  <p>This is when I started using the alternate queries listed above, both of which agreed with my apps read/write count, as well as the <code>COUNT(*)</code> value returned from the target. In other words, the only value that did not match was the <code>COUNT(*)</code> on the source database.</p>