SQL Server includes the COUNT function for counting a table's rows - however, it can be slow. Although querying the "rows" column from the sysindexes table is faster, it is not always accurate (e.g., if a bulk load process has recently taken place). In addition, you would not be able to use this method in conjunction with a GROUP BY...HAVING construct, as you would if you were using the COUNT function.
Here is a better way: it involves adding an extra column to your tables and giving it a default value of 1. You then use the SUM function on that column to obtain the row count (if you need to exclude rows that have a NULL value for any given column(s), you can add a WHERE clause specifying "WHERE IS NOT NULL").
In my tests on a table with 9999 rows, a statement using "SELECT SUM(counter) FROM test" took only 7 ms to run (compared to 11 ms when "SELECT COUNT(*)" was used). And since SUM is an aggregate function, you can use it with GROUP BY...HAVING, just as you would with COUNT.
Creating a PDF from a Stored Procedure in SQL Server
A short but interesting article, the author has figured out a way to create a PDF from a stored procedure without using a third party library.
2019-09-20 (first published: 2003-08-26)
73,186 reads