Paging through disparate tables with SQL Server

The Problem

Recently a client requested that they be able to page "left and right" through various different types of results that were loosely related. In this case, each row corresponded to a survey result set, and each table to a different type of survey. The problem is not limited to surveys though. Think of a dashboard for a car repair shop where the mechanic wishes to see various different types of jobs in one "recent history" list; or an upcoming job list for an enterprise that handles many different types of phone queries which are placed into different tables.

How do you go about paging through disparate tables combined in a user interface?

The Schema

This is a contrived example of a dataset held by a customer to illustrate the method when many tables hold different data. Let's assume we have three different survey tables:

New Customer Survey

NewCustomerSurveyId (PK)
Date
WhereDidYouHearAboutUs
Question2
Question3
SatisfactionScore

Shopping Experience Survey

ShoppingExperienceSurveyId
Date
CartId
IsReturningCustomer

Customer Feedback

Date
CustomerFeedbackId
Comments

Now you can see that each example here has a Date, and in some cases if the tables represent "inheritance in the database"[see here and here] you can logically group them with a "parent" table. This suited my case well, so I added a relationship to a base table with some common properties:

Survey

SurveyId
Type
Date

and in each other table I added a SurveyId (FK) and removed the date. If this is not your case, fear not! You can still work around it. See "What if you don't have a parent table?" below.

The Solution

I'm using a View to join the tables together and allow for easy querying in the future. Views can perform exceptionally well, especially with the use of Common Table Expressions [see here and here], which--if used properly--can allow the query optimizer to improve performance compared to joins-on-select statements.

Let's begin by selecting all the possible options:

SELECT
    SurveyId,
    Date
FROM
    Survey

This is the basis of the view, bit we don't have the ability to page to the previous or next items. So we need to join the table with itself, however we need more information to know which survey is "next" in the list and which is the "previous". Enter the ROW_NUMBER! ROW_NUMBER is a fantastic tool that you should learn how to use, as it has many uses. For now we want to give each row a unique row number that represents it's ordering in the list. The power of ROW_NUMBER comes later when we get into partitioning...

SELECT
    SurveyId,
    Date,
    ROW_NUMBER() OVER (ORDER BY Date DESC) AS RowNumber
FROM
    Survey

This will give us the most recent survey (Date DESCending) first, each with a unique row number. You could use the SurveyId instead of ROW_NUMBER, but this would break down in a couple of cases:

if you don't want a specific row in the results, this would throw out your previous/next join ability
if PKs are not monotonically increasing with no gaps. And they're not. Especially once we start ordering.

SELECT
    SurveyId,
    Date,
    ROW_NUMBER() OVER (ORDER BY Date DESC) AS RowNumber
FROM
    Survey
WHERE Type != 'Hidden'

Now let's create a CTE to help us join this table with it's unique filtering and ordering with itself:

WITH OrderedSurveys AS ( -- this name can be anything you like
    SELECT
        SurveyId,
        Date,
        ROW_NUMBER() OVER (ORDER BY Date DESC) AS RowNumber
    FROM
        Survey
    WHERE Type != 'Hidden' -- you may wish to filter out some results
)
SELECT *
FROM OrderedSurveys

You can see the results are the same, we're just providing an alias for the select statement. Now join it to itself to find the previous:

    LEFT OUTER JOIN OrderedSurveys After ON OrderedSurveys.RowNumber - 1 = After.RowNumber

And join it with itself again to find the next:

    LEFT OUTER JOIN OrderedSurveys Before ON OrderedSurveys.RowNumber + 1= Before.RowNumber

Finally work the before and after into the result columns, and give the view a name:

CREATE VIEW SurveyInfo
WITH SCHEMABINDING
AS

WITH OrderedSurveys AS (
    SELECT
        SurveyId,
        Date,
        ROW_NUMBER() OVER (ORDER BY Date DESC) AS RowNumber
    FROM
        Survey
    WHERE Type != 'Hidden' -- you may wish to filter out some results
)
SELECT
    OrderedSurveys.SurveyId,
    OrderedSurveys.Date,
    Before.SurveyId AS PreviousSurveyId,
    Before.Date AS PreviousDate,
    After.SurveyId AS NextSurveyId,
    After.Date AS NextDate
FROM OrderedSurveys
    LEFT OUTER JOIN OrderedSurveys After ON OrderedSurveys.RowNumber - 1 = After.RowNumber
    LEFT OUTER JOIN OrderedSurveys Before ON OrderedSurveys.RowNumber + 1= Before.RowNumber

Now you will have a view which will give you the ordered surveys, along with a per-row previous and next ID for paging.

What if you don't have a parent table?

In my case it worked well to create a parent or base table with common properties, but that may not always be possible. You can still do the same by UNIONing the disparate tables together:

CREATE VIEW SurveyInfo
WITH SCHEMABINDING
AS

WITH AllSurveys AS (
    SELECT
        NewCustomerSurveyId AS SurveyId,
        Date,
        'NewCustomerSurvey' AS Type
    FROM NewCustomerSurvey
    WHERE WhereDidYouHearAboutUs != 'Mars' -- you can still do arbitrary filtering with this method

    UNION ALL

    SELECT
        ShoppingExperienceSurveyId AS SurveyId,
        Date,
        'ShoppingExperienceSurvey' AS Type
    FROM ShoppingExperienceSurvey

    UNION ALL

    SELECT
        CustomerFeedbackId AS SurveyId,
        Date,
        'CustomerFeedback' AS Type
    FROM CustomerFeedback
),
OrderedSurveys AS (
    SELECT
        SurveyId,
        Date,
        Type,
        ROW_NUMBER() OVER (ORDER BY Date DESC, Type) AS RowNumber
    FROM
        AllSurveys
)
SELECT
    OrderedSurveys.SurveyId,
    OrderedSurveys.Date,
    OrderedSurveys.Type,
    Before.SurveyId As PreviousSurveyId,
    Before.Date As PreviousDate,
    After.SurveyId As NextSurveyId,
    After.Date As NextDate
FROM OrderedSurveys
    LEFT OUTER JOIN OrderedSurveys After ON OrderedSurveys.RowNumber - 1 = After.RowNumber
    LEFT OUTER JOIN OrderedSurveys Before ON OrderedSurveys.RowNumber + 1= Before.RowNumber

You may noticed I included a Type column. This is to aid with retrieving the actual survey later, since SurveyId is now no longer unique. I also recommend adding the type to the ROW_NUMBER ordering to make the paging deterministic.

Conditional partitioning: mult-tenanted or multi-departmental systems

You may have a case where not all users should see all results, but paging should still work per-user. So for example user 1 should be able to page through the surveys 1 -> 3 -> 5 -> 6, but user 2 should only be able to page through surveys 2 -> 4 -> 7 -> 11. To achieve this we will again turn to ROW_NUMBER. Presuming each table had a CustomerId column, you would modify your row number statement as follows:

    ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY Date DESC, Type) AS RowNumber

Instead of CustomerId you could use DepartmentId or any other partitioning scheme appropriate to you. Just remember to include it in the final result so you can filter the rows appropriately (you don't want one customer seeing another customer's data!)

Uses: show the latest by default, but allow paging to the previous (and next)

I use this union-method of joining disparate tables into one on an overview page. By default the latest survey is loaded, but the user can page back and forth and load other surveys, or choose from a date picker to see which surveys are available.

The order of operations is as follows:

Load the latest survey details to show by default

SELECT TOP 1 *
FROM SurveyInfo
WHERE CustomerId = 1 -- always use parameterised queries, this is just a sample of what to do for a specific customer

This may return to the client:

SurveyId: 567
Date: 2021-09-14
Type: 'NewCustomerSurvey'
PreviousSurveyId: 3
PreviousDate: 2021-09-11
NextSurveyId: NULL
NextDate: NULL

Load the details results

Once this is returned to the client, the UI uses the type and ID to load the detailed results:

SELECT *
FROM NewCustomerSurvey
WHERE NewCustomerSurveyId = 567

Paging back

When the user clicks the previous button, the survey info is loaded again with the previous ID:

SELECT TOP 1 *
FROM SurveyInfo
WHERE CustomerId = 1 AND SurveyId = 560 -- always use parameterised queries, this is just a sample of what to do for a specific customer

You may also need to return the Type in your result set to do this if your IDs are not unique

This may return to the client:

SurveyId: 3
Date: 2021-09-11
Type: 'ShoppingExperienceSurvey'
PreviousSurveyId: NULL
PreviousDate: NULL
NextSurveyId: 567
NextDate: 2021-09-14

Once this is returned to the client, the UI uses the type and ID to load the detailed results:

SELECT *
FROM ShoppingExperienceSurvey
WHERE ShoppingExperienceSurveyId = 3

Where to from here?

The possibilities are endless! Let me know in the comments below if you have tried this method, have any issues with it or can show any improvements!