Table Row Count + Index Row Count

csomberg

SQL 2000

I have a table with 5,100,000 rows.
The table has three indices.

The PK is a clustered index and has 5,000,000 rows - no other
constraints.

The second index has a unique constraint and has 4,950,000 rows.

The third index has no constraints and has 4,950,000 rows.
Why the row count difference ?

Thanks,

Me.

Jul 23 '05 #1

nib

cs******@dwr.com wrote:

SQL 2000

I have a table with 5,100,000 rows.
The table has three indices.

The PK is a clustered index and has 5,000,000 rows - no other
constraints.

The second index has a unique constraint and has 4,950,000 rows.

The third index has no constraints and has 4,950,000 rows.
Why the row count difference ?

Thanks,

Me.

How are you determining how many "rows" are in your indexes?

Zach

Jul 23 '05 #2

csomberg

For the table - it's Count(*).

For the indices its -
SELECT o.name, 'Index Name' = i.name,
i.indid, minlen, dpages, reserved, used, rowcnt,
FROM sysobjects o, sysindexes i
WHERE o.name IN (xxxxx) AND o.id = i.id and rows > 0
ORDER BY o.name, indid

Jul 23 '05 #3

nib

cs******@dwr.com wrote:

For the table - it's Count(*).

For the indices its -
SELECT o.name, 'Index Name' = i.name,
i.indid, minlen, dpages, reserved, used, rowcnt,
FROM sysobjects o, sysindexes i
WHERE o.name IN (xxxxx) AND o.id = i.id and rows > 0
ORDER BY o.name, indid

Have you updated your statistics lately?

Jul 23 '05 #4

Gert-Jan Strik

"Me",

SQL-Server indexes always cover all rows of the table. No rows are ever
skipped. You can use the sysindexes system table to get an estimation of
the number of rows, but you cannot depend on them for an exact number.

HTH,
Gert-Jan
cs******@dwr.com wrote:

SQL 2000

I have a table with 5,100,000 rows.
The table has three indices.

The PK is a clustered index and has 5,000,000 rows - no other
constraints.

The second index has a unique constraint and has 4,950,000 rows.

The third index has no constraints and has 4,950,000 rows.
Why the row count difference ?

Thanks,

Me.

Jul 23 '05 #5

Gang He [MSFT]

Me,

The row count in sysindexes is not accurately maintained in Shiloh. Server
tries the best to keep it accurate but no guarantee. To get an accurate
number of rows from an index with index id=n, use the follow statement
instead:

select count(*) from table with (index=n)
--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
<cs******@dwr.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

SQL 2000

I have a table with 5,100,000 rows.
The table has three indices.

The PK is a clustered index and has 5,000,000 rows - no other
constraints.

The second index has a unique constraint and has 4,950,000 rows.

The third index has no constraints and has 4,950,000 rows.
Why the row count difference ?

Thanks,

Me.

Jul 23 '05 #6