440,180 Members | 1,016 Online
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
5 Replies

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