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.
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.
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!
Published at 27 Mar 2019, 11:45 AM