June 24, 2002 at 7:11 am
Is there a more efficient way to get the count from a table? I know I can use
(select si.rows sysindexes si where si.id = object_id('tablename') and si.indid < 2) to replace a basic count selecting everything but I am selecting counts based on criteria in the table and I was just wondering if there was a quiker way.
Thanks,
Eddie
June 24, 2002 at 8:45 am
AFAIK if your count is based on criteria , its better you directly query your table . but remember instead of using count(*) better use somethign like count(1) . i think this should optimize your query.
June 24, 2002 at 9:55 am
count(*) usually pulls the count from sysindexes. There were problems with this being accurate in SQL 65 (possible 70). I think in 2000, this is accurate.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 24, 2002 at 8:30 pm
With a search criteria the count should be coming strictly from the table and not sysindexes as the count must reflect the results of your criteria set. Also of note is COUNT(*) versus COUNT(colx) if you use * you will get all the rows counted from the output, but if you use colx as in the example instance NULL values will be eliminated.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply