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]
(
@TableName sysname = null
)
returns bigint
as
begin

-- Example:
-- select dbo.fn_UTL_GetRowCount ('tblAccounts')
-- select top 10 name, dbo.fn_UTL_GetRowCount (name) from sys.tables

declare @out bigint
select @out = i.rowcnt
from sys.sysindexes i
where id = object_id(@TableName)
and (i.indid = 0 or i.indid = 1)
return @out

end

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 )

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.