Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Instead of calling COUNT(*) with a WHERE clause, is there anything one can do to make this work more efficiently on the server? I would like to get this count on complicated joins, just wondering the best way to do this. Using SET NOCOUNT OFF? Because SET NOCOUNT ON is enabled by default in a new stored procedure and is suggested I guess by Microsoft. Thank you.

share|improve this question

SET NOCOUNT has nothing to do with your ability to perform counts or the efficiency of doing so. This setting merely enables the reporting of affected rowcounts to the client (e.g. the n row(s) affected. message in Management Studio). The reason it is recommended to be off is because it is unnecessarily chatty and can break some applications / APIs.

As for getting more efficient counts from complex joins, it's possible that your joins could be made more efficient, but we can't see them. It's also possible that you can pay that cost at a different time by using filtered indexes, indexed views, or pre-aggregating yourself at insert/update time, using a trigger, etc. Sometimes this can pay off but other than a general suggestion there's not much else we'll be able to offer you without a lot more details.

share|improve this answer
Hello Aaron, in regards to your last response as well from question here:… I am wondering the results in your SQL statement, i cannot see how to narrow down to one particular db/table, just a list of objects with a rowcount. Can you please elaborate on this as well? – Wilkins Sep 1 '11 at 19:29
You would add an additional WHERE clause, e.g. FROM sys.dm_db_partition_stats WHERE [object_id] = OBJECT_ID('dbo.tablename') AND ... - and you would run the query in the specific database or use databasename.sys.dm_db_partition_stats... – Aaron Bertrand Sep 1 '11 at 19:37

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.