Monday, February 9, 2009

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


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


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.


for real time SQL, OLAP, Data Mining, PerformancePoint, Excel Services see

1 comment:

Darren Gosbell said...

sp_spaceused 'myTable' is another easy way to quickly get the estimated number of rows for a table