Count / SysIndex

Hi,
I have a problem in that when I perform a "Select Count(*) from table1" I
receive a different value than "SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table1') AND indid < 2".
Is this to be expected? Can anyone help?
Thanks,
JoeJoe
The system sysindex table can contain let say it incorrect info about rows
count .
Run update statistcs and see if result is correct.
If you want to know how many rows your table has , then rin select count(*)
from table
Also look at mu example
CREATE TABLE TT
(
COL INT NULL
)
INSERT INTO TT VALUES (NULL)
INSERT INTO TT VALUES (1)
SELECT COUNT(COL) FROM TT
What do you think how many rows will return this statement?
"Joe Doherty" <joeydocherty2404@.hotmail.com> wrote in message
news:OPTPY9rzDHA.3116@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a problem in that when I perform a "Select Count(*) from table1" I
> receive a different value than "SELECT rows FROM sysindexes WHERE id => OBJECT_ID('table1') AND indid < 2".
> Is this to be expected? Can anyone help?
> Thanks,
> Joe
>|||Sysindexes is only updated at checkpoint. Never trust systindexes as it is
an approximation since SQL 7.0.
This was done for performance reasons.
Cheers
--
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"Joe Doherty" <joeydocherty2404@.hotmail.com> wrote in message
news:OPTPY9rzDHA.3116@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a problem in that when I perform a "Select Count(*) from table1" I
> receive a different value than "SELECT rows FROM sysindexes WHERE id => OBJECT_ID('table1') AND indid < 2".
> Is this to be expected? Can anyone help?
> Thanks,
> Joe
>|||Hi Joe,
run "DBCC UPDATEUSAGE ... WITH COUNT_ROWS" on the database.
This will report and corrects inaccuracies in the sysindexes table, which may
result in incorrect values returned by sysindexes table.
But it is always safer to use count(*) rather than leaning on sysindexes
table.
-- Vishal