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. 5 8813 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
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 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?
"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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Hoang |
last post by:
does anyone know how to dump the table structure and data from a mysql
database? I am connected to the database externally so "SELECT * FROM
database INTO OUTFILE file" doesn't work for me. ...
|
by: mamo74 |
last post by:
Hello.
I am administering a SQL Server (Enterprise Edition on Windows 2003)
from some month and can't understand what is going on in the latest
week (when the db grow a lot).
The DB is around...
|
by: John A Fotheringham |
last post by:
I've probably not given the best title to this topic, but that
reflects my relative "newbie" status.
I have a table that goes essentially
TS DATETIME
jobnumber VARCHAR
jobentry VARCHAR
...
|
by: Mike Leahy |
last post by:
Hello all,
This question is related to updating tables - is there any way to calculate or
update the values in a column in a table to the values in a field produced by
a query result? An...
|
by: db2udbgirl |
last post by:
If I perform a select count(*) from tred.order_delivery query will it
internally perform a full table scan to determine the row count for the
following scenario
case 1: There is a primary key on a...
|
by: Chifo |
last post by:
hello.
i have a problem with a populate html table with data from table here
it's the problem
two querys retrieving data from table, one of querys show me a
colletion of data from 6:00 am to...
|
by: db2admin |
last post by:
Hello,
I always assumed that dropping table will drop everything associated
with it like indexes, references etc.
I just noticed that after dropping table A and recreating it and then
creating...
|
by: bravo |
last post by:
Hi,
select c19,name,c5,count(*) as count,sum(c13) as cost from TableA where c1 like '%' and c5 like '%' and name like 'bravo' and c19 between '2009-01-01 00:00:00' and '2012-01-01 00:00:00'...
|
by: jrod11 |
last post by:
hi,
I found a jquery html table sorting code i have implemented. I am trying to figure out how to edit how many colums there are, but every time i remove code that I think controls how many colums...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |