Getting the Row Count From SQL Server

Over the past several days I have been playing with ASP.NET and SQL Server. Part of the way I learn is by making something useful for me to use on future websites. One problem I encountered was how to quickly and easily get the row count the SQL Server database. ASP.NET isn't as easy as ColdFusion which does all that for you. It turns out, getting the row count is surprisingly easy. Here is a generic example of the SQL code.

SELECT COUNT (*) 
FROM (

  /* The original query goes here, this is just a simple example of a query */
  SELECT *
  FROM   MyTable
  WHERE MyTable.index >= 1
  /* End original query */

) sub;

That will return the row count of the query. Of course, the database is not locked and the row count can change between the time you get the row count and the time you fetch the data from the database. These are two different database calls. If the database content may possibly change quickly, you will need to employ another solution.

Posted by Wade Burchette at 9:56 AM - Categories: ASP