Create SQL Server partitioned views to group tables having partitions

How to Create SQL Server partitioned views to group data from multiple tables? Find examples of scripts to create views that points to four or more different tables. In this example, each Sales table would contain the sales of a year, a quarter, or a month, to optimize performance.

First, what is a partitioned view? A partitioned view joins horizontally the partitioned data of a set of tables. These tables can be on one or multiple physical servers. SQL Server partitioned views allows to group data from tables and displays it as if it came from a unique source table. A view that joins together data from tables on the same SQL Server instance is called a local partitioned view.

Table of Contents

First create the tables and the partitions

To use the partitioned views, you need underlying tables that are physically partitioned. Here is a tutorial on how to create the partitions and the tables to be used by the views. Adapt the code to every scenario.

Create a SQL Server partition

Create yearly partitioned views with a T-SQL script

This is a code example of a horizontally partitioned view. It is also a local view because all data is stored on the same database server. The underlying tables are called the view’s member tables. However, a widely used model is partitioning by years. This is because past years are rarely, if ever, used, which greatly improves performance when querying the current year’s data. For example:

First, the Year 2019 and past: SALES_2019
Year 2020: SALES_2020
Year 2021: SALES_2021
Finally, the year 2022 and future: SALES_2022

However, this annual partitioning implies managing the insertion of data dynamically on the appropriate year according to the effective date of the data concerned.

CREATE VIEW [dbo].[V_Sales]
AS 
  SELECT [Year], [Month], [CustomerID], [SalesID], [Amount_ET], [Amount_IT]
  FROM [dbo].[Sales_2019]
  UNION ALL 
  SELECT [Year], [Month], [CustomerID], [SalesID], [Amount_ET], [Amount_IT]
  FROM [dbo].[Sales_2020]
  UNION ALL 
  SELECT [Year], [Month], [CustomerID], [SalesID], [Amount_ET], [Amount_IT]
  FROM [dbo].[Sales_2021]
  UNION ALL 
  SELECT [Year], [Month], [CustomerID], [SalesID], [Amount_ET], [Amount_IT]
  FROM [dbo].[Sales_2022]
;
GO

Moreover, example uses four sales tables with each one containing only one year of data. Indeed, the view displays data from year 2019 to 2022. Please note that to ensure the tables data integrity, it’s better to add constraint to each table.

Quarterly horizontal partitioned SQL Server views

Or a partitioning of the sales figures by quarter:

First quarter : SALES_Q1
Second quarter : SALES_Q2
Third quarter : SALES_Q3
Fourth quarter : SALES_Q4

Monthly horizontal partitioning of a SQL Server view

In fact, to go to the end of the concept, it would be enough to create 12 tables with one table per month, and thus partition the sales data monthly. For example:

January: SALES_01
February: SALES_02
March: SALES_03
April: SALES_04
May: SALES_05
June : SALES_06
July : SALES_07
August : SALES_08
September : SALES_09
October : SALES_10
November : SALES_11
December: SALES_12

Paradoxically, note that it is possible to insert data into a SQL Server view. This means that the data will be inserted directly into the view’s source table. Obviously, only the columns present in the view are accessible from the Insert Into command.

To conclude, this simple tutorial shows how to create a partitioned view in SQL Server. The method allows to make performance improvements because the system accesses each table faster than one single table. To go further and improve performance of queries check out the partition section and how to check table size and disk space.

In this way, a query filtered on a particular month will only retrieve data on one table. In addition, here is how to create a table with SQL Server.