Create a partitioned view in SQL Server to group tables

How do you create a partitioned view in SQL Server and group data from multiple tables? This script is a simple example of creating a view that points to three different tables. In this example, each Sales table would contain the sales of a month or a quarter, 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 servers.

Create a partitioned view in SQL Server 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.

Create a SQL Server partitioned view to group data from multiple tables

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.

Creating a yearly partitioned view with a T-SQL script

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 partitioning of an MS SQL view

Or a partitioning of the sales figures by quarter:

  1. First quarter : SALES_Q1
  2. Second quarter : SALES_Q2
  3. Third quarter : SALES_Q3
  4. 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:

  1. January: SALES_01
  2. February: SALES_02
  3. March: SALES_03
  4. April: SALES_04
  5. May: SALES_05
  6. June : SALES_06
  7. July : SALES_07
  8. August : SALES_08
  9. September : SALES_09
  10. October : SALES_10
  11. November : SALES_11
  12. 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.

Be the first to comment

Leave a Reply

Your email address will not be published.


*