whats the efficient query to count table rows in SQL server 2008?

Question

whats the efficient query to count table rows in SQL server 2008

Answer 1

For an accurate answer, the sane way to get the exact nr of rows in a table in a normalized database is to execute:

select count(*) from table;

On a table without any indexes, the database will perform a full table scan. If you have indexed a non-null column, the database may use the (potentially much smaller) index to resolve your query.

If you need a faster solution, you have to keep track of the nr of rows in the table without resorting to counting the rows themselves. For example, you can do this yourself in another table (as part of your carefully designed API or by using triggers etc) or by creating an *indexed view for this purpose.

However, if you only need an aproximation, and it is critical that you get the answer fast, others have shown ways to do that by using the dictionary (John, Martin)

*Disclaimer: I haven't actually used indexed views in SQL server, I'm just reading the documentation and assume they can do the same thing as a materialized view in Oracle.

Answer 2

SELECT COUNT(*) FROM TableName

Does that work for you ?