[quote=465401:@Norman Palardy]when record count is not supported you end up doing the query twice
select count(*) from … to get the count
select … to get the actual data
some may suggest doing it once & just copying all the data locally but with a large # of records that may not be practical[/quote]
Norman’s suggestion is a good one. And not unlike what I often do, if I have to display the results to the UI.
After getting the count from the first query, I then determine whether pagination is required (to manage more results that can practically be displayed, as Norm says). So, based on the total count, the second query is dynamically modified to only retrieve say the first 50 rows to display to the user. Subsequent queries than step through and grab the next 50, and so on. From the total count and bit of math, you can predetermine how many pages of 50 there will be.
This way your application is not overloaded with an unmanageable number of records. SQL Server pagination can be achieved either with cursors or T-SQL Window pagination functions.
Pulling back all the results then counting them is especially problematic in web applications, where browsers and not meant to display 10’s of thousand of rows in an HTML table. Plus you retain all that data in memory, at least temporarily, while you count the total. You gain more in performance with two queries, when you know the potential results are in the thousands or more.
Also, when it comes to SQL Server SELECT statements, check out the use of WITH (NOLOCK) to speed up your queries. But be careful where you use NOLOCK, this modifier is not a magic bullet for all types of SELECT queries.