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?
Best Answer
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: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.