PL/SQL

Question

PL/SQL

anil1078

SSC Rookie

Points: 31
More actions
July 8, 2008 at 1:53 am

#190242

how can we count no of records from the table with out using select count(*) from table tablename

Viewing 14 posts - 1 through 13 (of 13 total)

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

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 1

anil1078 (7/8/2008)
how can we count no of records from the table with out using select count(*) from table tablename

Well, you could do something contrived and daft?? 🙂 like:

SELECT MAX(x.nr)

FROM ( SELECT row_number() OVER ( ORDER BY object_id ) AS nr

FROM sys.objects ) AS x

The purpose of count is to count the number of rows. Why do you want to use something else?

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

Do you have a specific reason you cannot simply select the count?

vaseem-773819 Ten Centuries Points: 1015 More actions · Answer 3

Hi,

Why dont u simply put like

select sum(1) from table

Vaseem

Mike Nowill SSCrazy Points: 2699 More actions · Answer 4

You can also right click on the table and go to Properties and then Storage and it has a row count there. I think you need be updating the table statistics for this to be truly accurate (assumption) however.

-Mike

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 5

Here's a function I wrote partially based on something posted, iirc, here.

/*

Author: Tomm Carr

Date: 2008-02-22

This function will return the number of rows in a table, the same result

as executing a "Select Count(*) from tableName" except that this is much

more efficient in that it doesn't have to scan the entire table.

Note: the value returned could be off by a few records, especially if

there has been some recent insert and/or delete activity against the

table. If absolute accuracy is important, be sure to execute the

following command first (it cannot be executed from within this function):

DBCC Updateusage( '', @TableName ) [WITH NO_INFOMSGS]

For large tables, this will still be MUCH faster than "Select Count(*)"

*/

ALTER function [dbo].[GetRowCount](

@TableName sysname

)

returns int

as begin

declare @Result int;

Select @Result = rows

from sysindexes

where id = object_id( @TableName, 'U' )

and indid < 2;

return IsNull( @Result , 0 );

end

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

John Rowan SSC Guru Points: 56440 More actions · Answer 6

This sounds suspiciously like homework to me. Whatever the reason for this strange requirement, a number of the solutions listed already will work.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 7

Could be homework I suppose, but strange it is not. It can take a couple of minutes for "Count(*)" to return a result for a Very Large Table, say 10 million records or more and scanning one of these tables just to get a record count is a terribly inefficient use of resources.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

John Rowan SSC Guru Points: 56440 More actions · Answer 8

I suppose more background on why the OP wanted this would have been helpful. I agree that scanning the table to get the row count on very large tables is not a great idea, buy you don't typically do a SELECT COUNT(*) without some sort of filter. I would guess that if they need the whole table counts, it is for some sort of maintenance routine (or homework).

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

SqlUser-529296 SSCarpal Tunnel Points: 4072 More actions · Answer 9

Hi Anil,

try this ....

SELECT o.name AS "Table Name", i.rowcnt AS "Row Count"

FROM sysobjects o, sysindexes i

WHERE i.id = o.id

AND indid IN(0,1)

AND xtype = 'u'

AND o.name <> 'sysdiagrams'

ORDER BY i.rowcnt DESC

---

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 10

Hi Anil,

You can also use this code..

select rows as Total from sysindexes
where id = object_id('Temp') and indid < 2

Cheers!

Sandy.

--

Kimberly.Hahn SSC-Addicted Points: 470 More actions · Answer 11

The values for the rowcnt in sysindexes is not always accurate - you have to make sure that stats are up to date to use that value. I have seen it cause problems in code when it is assumed that the value there is always exactly the row count of the table.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 12

Kimberly.Hahn (7/15/2008)
The values for the rowcnt in sysindexes is not always accurate...

Yes, that's true. If you'll look in the comments of the function I submitted on the previous page, you'll find the solution.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 13

Kimberly.Hahn (7/15/2008)
--------------------------------------------------------------------------------
The values for the rowcnt in sysindexes is not always accurate...

Ya, its correct...but updatestats make it possible if not updated..

you can also use other method too.

Cheers!

Sandy.

--