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:

, b
, c
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?


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.

| improve this answer | |
  • If I remember correctly Kimberly Tripp talks about using both and how they were both useful under different circumstances in her MCM video on partitioning. In fact I believe she even talked about using them in combination occasionally. – Kenneth Fisher Nov 30 '15 at 21:02
  • What if I were to use a with (Index= <index>) would that help me to improve the performance? SQL should use the index with out me specifying it correct? – mqbk Nov 30 '15 at 21:17
  • @KennethFisher Thanks for the info, Kim is a great speaker but I haven't seen that particular talk. Interestingly, we actually do use both in combination in production, essentially to "partition" by two dimensions at once. It can be very useful and has generally worked well, but we've had enough cardinality estimation problems that we might consider removing the partitioned view from the equation if we were doing it all over again. – Geoff Patterson Nov 30 '15 at 21:27
  • @mqbk No, you shouldn't need such a hint. SQL Server may consider all available indexes, and you generally should not be using a hint to force a particular index except in rare circumstances. One notable exception is the NOEXPAND hint for indexed views, in which case it can be very helpful (and mandatory if not on Enterprise Edition) to give SQL Server the hint. – Geoff Patterson Nov 30 '15 at 21:28
  • I was attempting to handle two issue at once. Managing a large table and increase performance. I may have to try another approach. – mqbk Dec 1 '15 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.