SQL Select Row Count With Results

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 =
(
	SELECT
		COUNT(memberID)
	FROM
		Members
	WHERE
		-- 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.

SELECT
	memberID, memberStatus, memberServiceType, memberForename, memberSurname, memberJoinDate, memberTown
FROM T_Tenant
WHERE
	-- Member is live
	(memberStatus = 10)
ORDER BY
	CASE 
		WHEN @SortBy = 1 THEN memberJoinDate
		WHEN @SortBy = 2 THEN memberTown
	END DESC
	-- Within specified page
	OFFSET @RowStart ROWS
	FETCH NEXT @PageSize ROWS ONLY

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!