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.

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 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:

  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.


*