# 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](https://docs.oracle.com/cd/B14099_19/web.1012/b15901/mapping003.htm) and [here](https://stackoverflow.com/questions/190296/how-do-you-effectively-model-inheritance-in-a-database)] 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](https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15) and [here](https://www.sqlshack.com/sql-server-common-table-expressions-cte)], 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 DESC`ending) 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:
1. if you don't want a specific row in the results, this would throw out your previous/next join ability
1. 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 `UNION`ing 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!