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 =
(
	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

Tags: SQL,MSSQL

Luke Alderton

Comments

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
Tags
Latest Comments
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
By Shahriar H. Razi on Xamarin Forms - Working with threads
7 May 2019, 09:25 AM
Categories
App Development
Event
Game Development
Mapping
Modelling
Programming
Review
Robotics
Tutorial
Web Development