Alternative to count(*)

Question

Post reply

Alternative to count(*)

pandeesh

Say Hey Kid

Points: 673
More actions
July 29, 2011 at 3:06 am

#245137

in order to find the total no of records present in the table currently, we are using SELECT COUNT(*) FROM table_name;
is there any alternative for this?
Is there any system table which internally maintains this detail for all tables.
if so, we can make use of that and get the record count fastly.
Thanks,
Pandeeswaran

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

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

Vishal.Gajjar SSCrazy Points: 2285 More actions · Answer 1

Try This:

SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count

FROM sys.dm_db_partition_stats st

WHERE OBJECT_NAME(OBJECT_ID) = N'TableName'

Vishal Gajjar
http://SqlAndMe.com

Vishal.Gajjar SSCrazy Points: 2285 More actions · Answer 2

Also, Add another condition as:

"index_id < 2"

Vishal Gajjar
http://SqlAndMe.com

pandeesh Say Hey Kid Points: 673 More actions · Answer 3

Vishal.Gajjar (7/29/2011)
Try This:
SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE OBJECT_NAME(OBJECT_ID) = N'TableName'

Will it give the exact result similar to COUNT(*) or it depends on the last analyzed time?

thanks.

Thanks,
Pandeeswaran

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 4

http://www.sqlservercentral.com/articles/T-SQL/67624/

Heres and article on how to perfrom fast rowcounts

Jayanth Kurup[/url]

Vishal.Gajjar SSCrazy Points: 2285 More actions · Answer 5

it will return accurate results, it is a dynamic management view.

Vishal Gajjar
http://SqlAndMe.com