Create partitioned view in SQL Server

How to create a partitioned view with SQL Server? This script is a simple example to create a view using partitions. Indeed, it points to three different tables. For example, each Sales table would contain the sales of a month or even a quarter, to optimize performance.

Create a partitioned view to aggregate data from multiple tables in SQL Server ?

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 servers.

The view groups the data and displays it as if it came from a unique table. A view that joins together data from tables on the same SQL Server instance is called a local partitioned view.

Please find a very simple T-SQL example of a partitioned view creation. The tables below contain the year, the month, the customer id, the sales transaction id and the amount with and without taxes.

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 tables.

To conclude, this simple 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 create a table partition.