Sunday, July 8, 2007

SQL Alternative Count

Both SQL Query below returns same result but the speed of retrieving data are different.
Copy Below Both Sql Query to Query analyzer to test it. Customers is a Table Name.

1. SELECT rows FROM sysindexes WHERE id = OBJECT_ID('Customers') AND indid <>
You can use sysindexes system table to get number of ROWS. This column contains the total row count for each table in your database. So, you can use the following select statement instead of Below one:

2. SELECT Count(*) FROM Customers
This SQL query performs full table scan to get the row count.

So, you can improve the speed of the SQL query using the Above First SQL instead of Second SQL Query

SQL Alternative Count