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
SELECT date, sales FROM dbo.Jan2015Sales
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.