SQL Select Row Count With Results

Microsoft SQL Server

When you're working with a large database and have to search that result set with paging, your first thought might be to select all results and then do the manipulation/count on the web server, sure that'd work, but it isn't exactly the most performant approach. Let's take a look at other ways we can produce some easily understood and most importantly, fast code that your customers can rely on.

First thoughts

So we've touched on what might be the easiest option and the simplest to implement, whereby you'd just select everything and then let the web server do the work. It isn't the fastest code but it would provide you the most options when it comes to manipulating the data.

You could also use an offset and fetch next (skip/take), this would be quick and reduce load on your web server too, but hang on, now we've lost our row count.

What's wrong with that?

Nothing's particularly wrong with the first option, providing your queries return relatively small sets of data, or your database just isn't that big, but what about when you're dealing with the opposite situation? What then? Well, everything grinds to a halt as your SQL and Web servers struggle to deal with that tonne of data, and like we've said already, option two is just missing the row count, so now we're unable to calculate paging on the front end.

The answer

What if we ran multiple select statements in our stored procedure and returned the row count as an output parameter? Holy cow I think we've got it.

So getting a row count is simple and probably won't require all the columns and order clause that the main statement does, it's as easy as selecting the count of a single column and setting it to a variable.

DECLARE @RowCount int
SET @RowCount =
		-- Member is live
		(memberStatus = 10)

In the above, we're selecting all live members, nothing complicated for this example but gives you an idea about how to use it.

Now we've got our row count, we'll need to do the full select where we're going return our result set.

	memberID, memberStatus, memberServiceType, memberForename, memberSurname, memberJoinDate, memberTown
FROM T_Tenant
	-- Member is live
	(memberStatus = 10)
		WHEN @SortBy = 1 THEN memberJoinDate
		WHEN @SortBy = 2 THEN memberTown
	-- Within specified page

See that with the two above examples, we've managed to select an entire row count and a subset of the total using an offset and fetch. Now we can offer paging on our front end, whilst only making the web and sql servers have to handle with the number of items totaling our page size.

Finally, just return the row count:

-- Total results found
RETURN @RowCount

Any suggestions or queries? Leave a comment!

Published at


Luke Alderton


Post a comment
Sit tight...
We're adding your comment.
Thank you.
Your comment has been added.
There's been a problem.
Please try again later.
Share with
Latest Comments
By Amit tiwari on How to add a Xamarin Forms Loading Screen/Overlay
3 Oct 2019, 10:29 AM
By Mahmoud Radwan on Xamarin Forms multiple instances of same app open
16 Sep 2019, 21:16 PM
By للل on JQuery Unobtrusive Validation for dynamically created elements
31 Aug 2019, 10:12 AM
By tiky on JQuery Unobtrusive Validation for dynamically created elements
16 Aug 2019, 13:10 PM
By Gerry on Replacing the Xamarin Header/Navigation bar with a custom view/template
11 Jul 2019, 17:12 PM
By steve choi on Xamarin Forms multiple instances of same app open
30 Jun 2019, 09:09 AM
By esrgr on JQuery Unobtrusive Validation for dynamically created elements
11 Jun 2019, 06:01 AM
By annualmars on Raspberry Pi - Running Java app on Raspbian
10 May 2019, 15:48 PM
App Development
Game Development
Web Development