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.
Table of Contents
Create SQL Server partitioned views to group multiple tables
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:
- 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.