By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,180 Members | 1,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,180 IT Pros & Developers. It's quick & easy.

Table Row Count + Index Row Count

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
"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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.