I have a third party program that creates and populates a series of tables to hold some historical data:

data_33 -- contains data for 12/01/11 - 12/05/11
data_34 -- contains data for 12/05/11 - 12/14/11
data_35 -- contains data for 12/14/11 - 12/20/11
data_36 -- contains data for 12/20/11 - 12/22/11
data_37 -- contains data for 12/22/11 - 12/23/11
data_38 -- contains data for 12/24/11 - 12/28/11
data_39 -- contains data for 12/28/11 - 01/02/12
data_(you get the idea)

Unfortunately, while these tables are indexed, they are not created with a partitioning scheme or partition function so I'm left to manually iterate through the tables. Now I want to allow the user generation of reports, but I'm unsure of how to best write a stored procedure to query against these tables in an optimized manner.

My first idea was to just UNION ALL all the tables into one gigantic temporary table, but I'm guessing this is going to be costly as a single table has a large number of records and even doing a SELECT * FROM data_33 is taking about 10 minutes to resolve. On top of that, I'll have to maintain this (materialized) view as the software generates new tables.

My second idea was to programmatically UNION ALL everything by grabbing all the tables in the database matching the results of SELECT name FROM sys.tables WHERE name LIKE 'data_%' but without any other limit I'm afraid this would still be extremely slow.

What would be the best way to optimize presenting this mass of data in a somewhat optimized manner?

  • view / materialized view? Apr 30, 2012 at 20:06
  • @EricHiggins Aye. A materialized view. Apr 30, 2012 at 20:11
  • If you have frequently created new tables (which sounds like may be the case if your app is generating them), and if the dataset is big, it may not be worth materializing. So with what you've said I'd do a view or view + material. Other factors (outer joins=likely) may prevent you from going the materialized route. Apr 30, 2012 at 20:14
  • @EricHiggins Outer joins are not likely, but calculated columns are (ie (ABS(value - @rangevalue)) as Delta)). All together I'm looking at ~14 million rows. Apr 30, 2012 at 20:25

1 Answer 1


Before table partitioning was introduced the way of doing the very thing you asked was called Partitioned Views:

A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers, making the data appear as if from one table.

Partitioned Views are not only a UNION ALL over each table. By using appropriate check constraints on the underlying tables partitioning column the optimizer can do some fancy stuff, including partition elimination.

PS. Obviously you can generate the view pragmatically. You can even use Event Notifications to generate it automatically every time your 3rd party tool creates a new table, assuming you're running on SQL Server 2005 or later.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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