Count(*)

Question

Count(*)

sqlgreenhand

SSCommitted

Points: 1639
More actions
November 13, 2009 at 9:26 am

#136128

I learn from a site to get the row count of table(s) by NOT using count(*) but instead of using
" SELECT rows FROM sysindexes WHERE id = OBJECT_ID('tablename') AND indid < 2 "
What is the meaning of "indid" ? Can I use the largest number of "rows" instead ?
Thanks.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Francis Yee-483501 (11/13/2009)
I learn from a site to get the row count of table(s) by NOT using count(*) but instead of using
" SELECT rows FROM sysindexes WHERE id = OBJECT_ID('tablename') AND indid < 2 "
What is the meaning of "indid" ? Can I use the largest number of "rows" instead ?
Thanks.

indid --> index id.

The two index ids less than 2 are 0 and 1. These are actually mutually exclusive. Index id 0 is a heap, index id 1 is a clustered index.

Be carefully relying on sysindexes, it is depreciated and may not be supported in fututre versions of SQL Server. it is available for backward compatibility.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Same as Lynn said, with a reference

http://www.mssqltips.com/tip.asp?tip=1044

This article discusses the method you are using. The base idea is to do a rowcount without the table scan.

Here are a couple of alternatives:

Option 1:

SELECT so.[name] as

, CASE WHEN si.indid between 1 and 254

THEN si.[name] ELSE NULL END

AS [Index Name]

, si.indid, rows

FROM sys.sysindexes si

INNER JOIN sysobjects so

ON si.id = so.id

WHERE si.indid < 2

AND so.type = 'U' -- Only User Tables

AND so.[name] != 'dtproperties'

ORDER BY so.[name]

Option 2:

Select OBJECT_NAME(object_id) as TableName,SUM(rows) as NumRows,index_id

From sys.partitions p

Inner Join sys.sysobjects o

on p.object_id = o.id

Where index_id in (0,1)

And o.type = 'U'

Group By object_id,index_id

Order By NumRows Desc

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

In SQL 2005 and above, the row count information is in sys.partitions. Filter on index_id IN (0,1) and sum the row count by object id. You have to sum because if a table or index is partitioned, there'll be more than one row for that index in there.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

CirquedeSQLeil (11/13/2009)
Option 2:
Select OBJECT_NAME(object_id) as TableName,SUM(rows) as NumRows,index_id
From sys.partitions p
Inner Join sys.sysobjects o
on p.object_id = o.id
Where index_id in (0,1)
And o.type = 'U'
Group By object_id,index_id
Order By NumRows Desc

Don't use sysobjects. It's deprecated and will be removed in a future version. In this case you want sys.objects or sys.tables

If you want user tables, this works too

Select OBJECT_NAME(object_id) as TableName,SUM(rows) as NumRows

From sys.partitions p

Where index_id in (0,1)

And OBJECTPROPERTY(object_id, 'IsUserTable') = 1

Group By object_id

Order By NumRows Desc

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Thanks Gail.

With that note, the query would also change (when using sys.objects).

Select OBJECT_NAME(p.object_id) as TableName,SUM(rows) as NumRows,index_id

From sys.partitions p

Inner Join sys.objects o

on p.object_id = o.object_id

Where index_id in (0,1)

And o.type = 'U'

Group By p.object_id,index_id

Order By NumRows Desc

Just as a correction to the previously posted code. Gail's solution works wonderfully too.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlgreenhand SSCommitted Points: 1639 More actions · Answer 6

sqlgreenhand

SSCommitted

Points: 1639

November 13, 2009 at 11:00 am

#1079118

Lot of information, thank you ALL!

sqlgreenhand SSCommitted Points: 1639 More actions · Answer 7

I would like to ask a last question before I close this subject.

Is there any other cases that if I use MAX(rows) for the same tablename I would not get the rowcount ?

THANKS!!!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Francis Yee-483501 (11/13/2009)
Is there any other cases that if I use MAX(rows) for the same tablename I would not get the rowcount ?

Yes. Any partitioned table.

Consider a table with 2 partitions. One partition has 25000 rows, other has 50000. Max'll return the larger one, but that's just the number of rows in the larger partition, not the number of rows in the entire table. To get the rows in the entire table, you'll have to sum the two.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

Francis Yee-483501 (11/13/2009)
I learn from a site to get the row count of table(s) by NOT using count(*) but instead of using
" SELECT rows FROM sysindexes WHERE id = OBJECT_ID('tablename') AND indid < 2 "
What is the meaning of "indid" ? Can I use the largest number of "rows" instead ?
Thanks.

Don't use that method (sysindexes) even in SQL Server 2000 because there is no guarantee that the value you get back is accurate unless you also run DBCC UPDATEUSAGE.

In 2k5, use the sys.Partitions view that Gail spoke of.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlgreenhand SSCommitted Points: 1639 More actions · Answer 10

sqlgreenhand

SSCommitted

Points: 1639

November 14, 2009 at 5:06 pm

#1079427

All right guys, I got it. Thanks !!!:-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Jeff Moden (11/14/2009)
Don't use that method (sysindexes) even in SQL Server 2000 because there is no guarantee that the value you get back is accurate unless you also run DBCC UPDATEUSAGE.
In 2k5, use the sys.Partitions view that Gail spoke of.

Even sys.partitions is not guaranteed to be 100% accurate all the time. It's better than sysindexes, but may still be off sometimes. If you just need a count that's almost right, that's fine. If you need the exact number of rows and it must not ever differ from the number really in the table, use count(*)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

Thanks, Gail. Heh... there goes another myth. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

I can't remember the details, but I think it's a case where it's supposed to be accurate, but not 100% certain to be if there are ongoing changes and open transactions. Way more accurate than sysindexes was though.

It's probably quite good enough for the majority of uses.

All of the bugs that required updateusage to fix are suppose to be fixed by SQL 2005 SP3. (but then they were supposed to be fixed by SQL 2005 RTM too.)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlgreenhand SSCommitted Points: 1639 More actions · Answer 14

As a rookie, I'm back for two more questions -

1. As explained before, indid --> index ID

a. is there only a row count (number) anytime for one "indid" (besides partitioned tables), other(s) is/are zero (and switching between/among types of indexes)

b. if the above is correct then using MAX (rows) would collect the number of rows (non-partitioned tables)

2. An 'INSERT/DELETE' to a table, the procedure is

LOG --> table --> system table for row update !? Without running DBCC xxxxx !?:crazy: