SQL Server Partitioned view

What is partitioned view in SQL Server?

In SQL Server, a partitioned view is a virtual table that combines the results of multiple SELECT statements against different tables or partitions into a single, logical view. Each SELECT statement represents a partition of the view, and the combined result appears as a single unified view. Partitioned views are primarily used for horizontal partitioning, allowing you to distribute data across multiple tables based on a predefined criterion. 

Example of multiple tables

CREATE VIEW PartitionedView AS
SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2
UNION ALL
...
SELECT * FROM TableN;

Example of table with different partitioning criteria

The SELECT statements in a partitioned view should include a WHERE clause that specifies the criteria for partitioning the data. Each SELECT statement corresponds to a partition based on this criterion.

CREATE VIEW DatePartitionedView AS
SELECT * FROM SalesData WHERE SaleDate < '2020-01-01'

UNION ALL

SELECT * FROM SalesData WHERE SaleDate >= '2020-01-01';

When should we use partitioned view?

Partitioned views are useful when dealing with large datasets that can be logically divided into partitions. Each partition can be stored in a separate table, and the partitioned view provides a seamless way to query the entire dataset.

Partitioned views can simplify certain administrative tasks, such as archiving or purging old data. We can create new tables to store data for a specific period and modify the partitioned view to include the new partition. Partitioned view is useful for management of temporal or spatial data.

What is local partitioned view in SQL Server?

Local partitioned view is created by joining different tables from same server.

What is distributed partitioned view in SQL Server?

Distributed view is created by joining different tables from different servers.