How many rows in a table

A very common DBA/developer task is to query how many rows there are in a table. Of course the natural way to do this is to write a query such as

SELECT COUNT(*) FROM MyTable

However, this query will be slow for a very large table, particularly if it only has a clustered index. Why not just ask SQL Server for the stats on the table? If your SQL Server database has the default settings, it will be continually sampling the table and will generally have a very good idea of how many records there are. A very simple way of asking the optimiser for these stats is to write the following query


SELECT * FROM MyTable


and click on the Display Estimated Execution Plan button, which looks a bit like 3 green boxes and a caption. SQL Server will tell you, amongst other things, the number of records it expects to read as you hover over the scan object.

I encourage developers to click on this button, not just to get a quick count, but so that they start to use the showplan more often. In my view, the more often you use showplan and the more familiar you get with the optimiser, the higher performing SQL you will write.

Richard

for real time SQL, OLAP, Data Mining, PerformancePoint, Excel Services see http://RichardLees.com.au/Sites/Demonstrations