Generic COUNT(*)

Question

Generic COUNT(*)

stevanuz

Old Hand

Points: 363
More actions
October 20, 2010 at 12:56 am

#225290

Comments posted to this topic are about the item Generic COUNT(*)

Viewing 7 posts - 1 through 6 (of 6 total)

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

Victor Shahar Mr or Mrs. 500 Points: 533 More actions · Answer 1

Hi,

Assuming that you use the auto statistics create & open and have all the statistics updated well, i think that it will be better to read from the statistics and not performing a real counting of the rows which is a litle bit physically i would say.

See my code and try it for huge tables and see the difference in time and I/O's.

ALTER function [dbo].[fn_UTL_GetRowCount]

(

@TableNamesysname = null

)

returns bigint

as

begin

-- Example:

-- select dbo.fn_UTL_GetRowCount ('tblAccounts')

-- select top 10 name, dbo.fn_UTL_GetRowCount (name) fromsys.tables

declare@outbigint

select@out = i.rowcnt

fromsys.sysindexes i

whereid = object_id(@TableName)

and(i.indid = 0 or i.indid = 1)

return@out

end

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

Victor Shahar (10/20/2010)
Hi,
Assuming that you use the auto statistics create & open and have all the statistics updated well, i think that it will be better to read from the statistics and not performing a real counting of the rows which is a litle bit physically i would say.
See my code and try it for huge tables and see the difference in time and I/O's.
ALTER function [dbo].[fn_UTL_GetRowCount]
(
@TableNamesysname = null
)
returns bigint
as
begin
-- Example:
-- select dbo.fn_UTL_GetRowCount ('tblAccounts')
-- select top 10 name, dbo.fn_UTL_GetRowCount (name) fromsys.tables
declare@outbigint
select@out = i.rowcnt
fromsys.sysindexes i
whereid = object_id(@TableName)
and(i.indid = 0 or i.indid = 1)
return@out
end

The sys.sysindexes System Table has been included as a view for backward compatibility. It might be removed in a future version of Microsoft SQL Server. Check the link below

http://msdn.microsoft.com/en-us/library/ms190283.aspx

So it is advisable to use the sys.dm_db_partition_stats System Table and the query would be similar

DECLARE @strTableName VARCHAR(100)

SET @strTableName = 'mstEmployees'

SELECT SUM(row_count)

FROM sys.dm_db_partition_stats

WHERE object_name( object_id ) = @strTableName

AND index_id IN ( 0, 1 )

You can also have a look at the discussion that followed the QOTD for 19th October 2010

http://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

JohnFx SSC Enthusiast Points: 155 More actions · Answer 3

Is the point of this approach simply to turn an ad-hoc SQL command into a stored procedure? If so, I really don't see the benefit of an ad-hoc dynamic query implemented in a proc over an ad-hoc query in the data tier. That is, why not just build the COUNT query dynamically in the data tier for the relevant table?

This approach just seems like like a convoluted way to do something that adds complexity with no obvious benefit.

stevanuz Old Hand Points: 363 More actions · Answer 4

thanks all for the comments.

the use of the stored procedure 4 this solution might seem not so obvious but i do personally prefer sp instead of adhoc launched from data access since its easier to maintain; so i believe it's not abt right or wrong or overcomplicate things but about preferences.

JohnFx SSC Enthusiast Points: 155 More actions · Answer 5

stevanuz (10/22/2010)
thanks all for the comments.
the use of the stored procedure 4 this solution might seem not so obvious but i do personally prefer sp instead of ad-hoc launched from data access since its easier to maintain; so i believe it's not about right or wrong or over-complicate things but about preferences.

I think this may be an example of taking a good idea (encapsulating logic in a stored proc) and applying it without consideration of whether it is appropriate in the specific circumstance. If you want to encapsulate the logic, I'd suggest creating a centralized count method in your code that builds the SQL. You take on a lot of baggage using dynamic SQL that could create unintended side effects (for example permission and scope issues) with no benefit on the other end of that trade-off.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 6

akljfhnlaflkj

SSC Guru

Points: 76202

May 18, 2016 at 7:09 am

#1879852

Thanks for the script.