Hi everybody,
In my program, I use the following statement to get a quick count of the
records in a given table:
SELECT [rows] FROM sysindexes WHERE [indid] = 0 AND [name] = 'table_name'
I don't know if it's just a bug with SQL, but it sometimes happens that when
there are changes to a particular table, there is no entry for that table in
sysindexes. I know this is pretty weird because BOL states that every table
should have an entry in sysindexes. If I drop the DB and then recreate it,
everything's OK but this is, of course, no realistic solution every time I
alter a table.
Does anyone else have this problem? Is there anything I can run to
repopulate sysindexes? Was this solved with some SP?
Any help would be much appreciated.
TIA,
sivrik.> In my program, I use the following statement to get a quick count of the
> records in a given table:
Why not just to SELECT COUNT(*) FROM TableName
or look at sp_spaceused system stored procedure in the BOL
Note: It's not recomended to query system tables instead use
INFORMATION_SCHEMA views. (See examples in the BOL)
"sivrik" <sivrik@.discussions.microsoft.com> wrote in message
news:7BB560F0-ADB7-43CB-B0FA-8BD687F65699@.microsoft.com...
> Hi everybody,
> In my program, I use the following statement to get a quick count of the
> records in a given table:
> SELECT [rows] FROM sysindexes WHERE [indid] = 0 AND [name] = 'table_name'
> I don't know if it's just a bug with SQL, but it sometimes happens that
when
> there are changes to a particular table, there is no entry for that table
in
> sysindexes. I know this is pretty weird because BOL states that every
table
> should have an entry in sysindexes. If I drop the DB and then recreate
it,
> everything's OK but this is, of course, no realistic solution every time I
> alter a table.
> Does anyone else have this problem? Is there anything I can run to
> repopulate sysindexes? Was this solved with some SP?
> Any help would be much appreciated.
> TIA,
> sivrik.|||Uri,
Thanks for the reply. I don't use SELECT COUNT(*) because there can be a
lot of data in a particular table and it takes forever. Thanks for the tip
on sp_spaceused. Is there a way to get it to return only the rowcount? I
need to set the value to a variable. I know this won't work but something
like:
select [rows] from (exec sp_spaceused 'table_name')
Or maybe there's another way to get this value quickly. I took a quick look
at INFORMATION_SCHEMA but couldn't find anything helpful.
Thanks again,
sivrik.
"Uri Dimant" wrote:
> Why not just to SELECT COUNT(*) FROM TableName
> or look at sp_spaceused system stored procedure in the BOL
>
> Note: It's not recomended to query system tables instead use
> INFORMATION_SCHEMA views. (See examples in the BOL)
>
> "sivrik" <sivrik@.discussions.microsoft.com> wrote in message
> news:7BB560F0-ADB7-43CB-B0FA-8BD687F65699@.microsoft.com...
> when
> in
> table
> it,
>
>|||I think your problem is that you are assuming that indid is always the table
.
If the table has a clustered index, then the 0 is changed to 1. 0 for indid
indicates the table has no clustered index and is a heap. When a clustered
index is added the 0 is changed.
select count(*) is the best way to get a quick count of rows. I uses index
information if possible.
"sivrik" wrote:
> Hi everybody,
> In my program, I use the following statement to get a quick count of the
> records in a given table:
> SELECT [rows] FROM sysindexes WHERE [indid] = 0 AND [name] = 'table_name'
> I don't know if it's just a bug with SQL, but it sometimes happens that wh
en
> there are changes to a particular table, there is no entry for that table
in
> sysindexes. I know this is pretty weird because BOL states that every tab
le
> should have an entry in sysindexes. If I drop the DB and then recreate it
,
> everything's OK but this is, of course, no realistic solution every time I
> alter a table.
> Does anyone else have this problem? Is there anything I can run to
> repopulate sysindexes? Was this solved with some SP?
> Any help would be much appreciated.
> TIA,
> sivrik.|||Mike,
Thanks for the reply. I did make that assumption but the problem is that
ALL information for the particular table "disappears" from sysindexes. So
SELECT * FROM sysindexes WHERE [name] = 'Table_Name'
returns absolutely nothing. SELECT COUNT(*) from a table with a lot of data
in it takes forever so that's why I was looking for a quicker way.
sivrik.
"Mike Hanson" wrote:
> I think your problem is that you are assuming that indid is always the tab
le.
> If the table has a clustered index, then the 0 is changed to 1. 0 for ind
id
> indicates the table has no clustered index and is a heap. When a clustere
d
> index is added the 0 is changed.
> select count(*) is the best way to get a quick count of rows. I uses inde
x
> information if possible.
> "sivrik" wrote:
>|||Hi
You will have to create a 'template' table with all columns that output from
sp_spaceused
INSERT INTO #Temp EXEC sp_spaceused 'table name'
SELECT COUNT(rows) FROM #Temp
Another way is to create an indexed view that will produce a more efficient
execution plan.
This example has written by Steve Kass.
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
"sivrik" <sivrik@.discussions.microsoft.com> wrote in message
news:0F09E3E7-2B20-41BD-98E6-0354136D8D2C@.microsoft.com...
> Uri,
> Thanks for the reply. I don't use SELECT COUNT(*) because there can be a
> lot of data in a particular table and it takes forever. Thanks for the
tip
> on sp_spaceused. Is there a way to get it to return only the rowcount? I
> need to set the value to a variable. I know this won't work but something
> like:
> select [rows] from (exec sp_spaceused 'table_name')
> Or maybe there's another way to get this value quickly. I took a quick
look
> at INFORMATION_SCHEMA but couldn't find anything helpful.
> Thanks again,
> sivrik.
> "Uri Dimant" wrote:
>
the
the
'table_name'
that
table
recreate
time I|||Hi Sivrik
I have never heard of information dissappearing from sysindexes before, at
the risk of offending you, are you sure you are running the select within th
e
right database context. I do this all the time in Query Analyzer, forgettin
g
to change the context, perhaps your application connection string is not
setting the correct database context for the query.
I usally find count(*) to be quite fast if a clustered index exists on the
table and statistics are up to date. Usually slow performance of this
function means it is actually having to update the statistics (if automatic
statistics are enabled) or do a manual count, because statistics are out of
date or do not exist.
Mike Hanson
"sivrik" wrote:
> Mike,
> Thanks for the reply. I did make that assumption but the problem is that
> ALL information for the particular table "disappears" from sysindexes. So
> SELECT * FROM sysindexes WHERE [name] = 'Table_Name'
> returns absolutely nothing. SELECT COUNT(*) from a table with a lot of da
ta
> in it takes forever so that's why I was looking for a quicker way.
> sivrik.
>
> "Mike Hanson" wrote:
>|||sivrik try
create table #temp(name varchar(100), rows int, reserved varchar(100), data
varchar(100), index_size varchar(100), unused varchar(100))
insert #temp(name, rows, reserved, data, index_size, unused)
exec sp_spaceused 'your table'
select rows from #temp
Regards
Meir
"sivrik" <sivrik@.discussions.microsoft.com> wrote in message
news:0F09E3E7-2B20-41BD-98E6-0354136D8D2C@.microsoft.com...
> Uri,
> Thanks for the reply. I don't use SELECT COUNT(*) because there can be a
> lot of data in a particular table and it takes forever. Thanks for the
tip
> on sp_spaceused. Is there a way to get it to return only the rowcount? I
> need to set the value to a variable. I know this won't work but something
> like:
> select [rows] from (exec sp_spaceused 'table_name')
> Or maybe there's another way to get this value quickly. I took a quick
look
> at INFORMATION_SCHEMA but couldn't find anything helpful.
> Thanks again,
> sivrik.
> "Uri Dimant" wrote:
>
the
the
'table_name'
that
table
recreate
time I|||No offense taken, I'm sure we've all forgot to swith the DB in query analyze
r
before. =) No, it's not that I'm running it in the wrong context. It's
actually that there is no more information about the table in sysindexes. I
t
seems strange to me too so that's why I was asking.
Thanks,
sivrik.
"Mike Hanson" wrote:
> Hi Sivrik
> I have never heard of information dissappearing from sysindexes before, at
> the risk of offending you, are you sure you are running the select within
the
> right database context. I do this all the time in Query Analyzer, forgett
ing
> to change the context, perhaps your application connection string is not
> setting the correct database context for the query.
> I usally find count(*) to be quite fast if a clustered index exists on the
> table and statistics are up to date. Usually slow performance of this
> function means it is actually having to update the statistics (if automati
c
> statistics are enabled) or do a manual count, because statistics are out o
f
> date or do not exist.
> Mike Hanson
>|||Also you need to be aware that the information you get from spaceused (which
comes from sysindexes ) is not updated with each insert, update, or delete,
and will often be close , but not exact... ( It can be very far off if you
have done a truncate table.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"sivrik" <sivrik@.discussions.microsoft.com> wrote in message
news:B7F509C2-6C9E-4B0B-B091-0A8DFBB8F790@.microsoft.com...
> No offense taken, I'm sure we've all forgot to swith the DB in query
> analyzer
> before. =) No, it's not that I'm running it in the wrong context. It's
> actually that there is no more information about the table in sysindexes.
> It
> seems strange to me too so that's why I was asking.
> Thanks,
> sivrik.
> "Mike Hanson" wrote:
>
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment