sysindexes issue
-
Confused slightly what can be the reason that query :
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('MyTable') AND indid < 2returns a non zero value (60) even when the table is empty...Thanks -
I think this is only as good as the last statistics update. It's not updated with every table insert or delete (thank goodness! Imagine the contention.).
And then again, I might be wrong ...
David Webb -
Thanks. That means I need pretty often update statustics to keep this data correct?!
-
From BOL I found that indid=1 points to cluster index and indid=0- to table. When I've got number of table records (rowcnt) using indid=0 it always correct. But there is another question- why does not every table exist in sysindexes, I mean only part of DB tables? Thanks
-
check this out, it'll return a row
.create table a
(b int not null)
select * from dbo.SysIndexes where id = object_id('a')
drop table a
What tables do you have that have no referrence in sysindexes?
-
Not exactly, it means that you should not rely on that value being correct. In fact, the general advice regarding anything with system tables is that you should not depend on them.
-
A table that does not have a clustered index is called a heap, and it will show up in sysindexes with indid = 0. If a table does have a clustered index created on it it shows up with indid = 1. The leaf nodes in a clustered index is actually the data pages, so therefore the clustered index is really the table (with an associated index).
-
Oh, and also, rowqcnt is no different from rows. The latter column is only there for backwards compatibility, but both of them have the same problem with not always being accurate. So you cannot rely on any of them to always give you the correct number of rows in a table.
-
Thanks, guys. It makes sense now. The reason that I started to dig into sysindexes table is, as I read somewhere, query
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('MyTable') AND indid < 2
returns results much faster then standard count(*). But it seems to me now that you can’t realy rely on this query.
-
It's great if you only need an approximate number. (let's say you want to monitor the tables' growth on the whole db as a monthly task without doing select count(*) on each table which could take hours/days on a huge db.
However, if you need accuracy you have no choice but to go with count(*).
-
Oh yes, you are absolutely correct in that. Whenever I just need to get a feel for the number of rows in a table that is the wuery to run. But as you've seen now, if you are going to use it for instance in an application to actually base some decision on, you need to use a COUNT(*) instead.
-
Chris, thanks a lot for your explanations. But I am just curios. From EM=> Taskpad =>TableInfo we can get number of rows in each database table pretty quickly. I have a doubt that Sql server uses Count(*) to do this calculation. How does server manage this? Thanks
-
With sysindexes
. -
Remi, but if EM uses sysindexes for counting number of rows it should initially do reindex (update statistics) for table where cluster index was created to get correct results? Is this
logical? Thanks -
It should... but it would take too long, so every once in a while you get a bad number in there.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply