Returning all rows from multiple partitions of a partitioned table

Question

I have a table that is manually partitioned into multiple filegroups and spread out over multiple tables by month. My partition scheme is using a date column. What I would like to do is something like the following:

SELECT a
, b
, c
FROM<table>
WHERE date between '2015-08-01' AND SYSDATETIME()

I am wondering if there is a way to do it without the need to use a UNION ALL on all the tables?

Answer 1

As long as you have the appropriate check constraints on the date column on each of the underlying tables, you can create a partitioned view:

CREATE VIEW dbo.2015Sales
AS
SELECT date, sales FROM dbo.Jan2015Sales
UNION ALL
SELECT date, sales FROM dbo.Feb2015Sales
...

This would allow the UNION ALL to be hidden behind the view, and SQL Server can eliminate irrelevant tables (based on your check constraints) from the query plan if the query explicitly specifies a date range.

However, partitioned tables, in which all of the data would be in one table and no UNION ALL would be needed, is the more common approach.

That said, there are a lot of potential performance implications for either approach, so it's best to test on your specific data and workload. For example, partitioned views frequently yield query plans with extraneous Concatenation operators, a situation that may reduce the accuracy of the Cardinality Estimator (especially in SQL 2012 and earlier). And partitioned tables, while very useful and probably what you want, do come with a couple potential downsides as well.