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

Share with
Tags
Latest Comments
By Mark Gentry on Windows Server 2019 - Change product key does nothing
20 Aug 2021, 03:30 AM
By Cathy on In-Place Upgrade for a Windows Server domain controller
31 Jul 2021, 18:28 PM
By Mr. Greymatter on Raspberry Pi - Running Java app on Raspbian
16 Feb 2021, 07:35 AM
By Mikko Seittenranta on Xamarin Forms multiple instances of same app open
16 Feb 2021, 04:34 AM
By Andrew on Auto/Custom height on Xamarin Forms WebView for Android and iOS
22 Jan 2021, 22:15 PM
By Nick on Raspberry Pi - Running Java app on Raspbian
14 Oct 2020, 19:37 PM
By Ivan on Fixed: Value cannot be null Parameter Name: source
15 Sep 2020, 19:47 PM
By Anand on Raspberry Pi - Bluetooth using Bluecove on Raspbian
7 Sep 2020, 16:53 PM
Categories
App Development
Event
Game Development
Mapping
Modelling
Programming
Review
Robotics
Tutorial
Web Development