I have a 3rd party piece of software which records data samples from industrial equipment. This data logging software stores data in sample data tables with approximately one day's worth of samples for a particular piece of equipment. There are many pieces of equipment being monitored so the database sample tables end up looking like this:


So those 31 tables contain sample data for one piece of equipment over the most recent rolling 31 day period.

There is a set of those tables for each equipment type. This makes it difficult to query for a specific sample value, given a specific date/time.

I was thinking of creating a view which unions all the equipment sample tables into one big view so that I can write a function which returns a particular sample given an equipment name and date/timestamp. Is that a bad way to do this? Can you suggest other or better ways of doing this? I do not have any control over the way the 3rd party data logging/sample tool stores data.

The end result of this is that I want to be able to say: "Give me the sample value recorded for equipment N, nearest (>=) the specified date/time" and get a value back.

This is on SQL Server 2008.

  • Yes, I would say your view approach is probably the best in this case where you have no control over the underlying base tables. Aug 13 '12 at 14:53
  • "approximately one day's" -- from this language, are we to assume that the data is not partitioned into these tables by date? In other words, both tables 1 and 2 may contain data from a single date?
    – Jon Seigel
    Aug 13 '12 at 16:41
  • You could also generate a dynamic query, but since you have a specific number of tables you will get a better performance if you stick to UNION.
    – Mentor
    Aug 13 '12 at 18:51
  • @Jon Seigel - Yes, the table roll over time may fall within a day so the first part of August 20th may be in one table and then it might roll over and add data from August 21 in that same table. Aug 14 '12 at 13:30

Personally, I'd consider using triggers to load a proper single table and query that. Then you have control of partitioning (if needed), defining good indexes etc

Depending on volumes, you could use service broker to decouple the app and your tables.

Basically, anything to avoid massive UNIONs and dynamic SQL...

  • That is an interesting idea. So I would put row insert triggers on all the tables and insert added rows into the consolidated table which can be queried in a more standard way. I think I'm going to give this a try, assuming I have enough storage space to have two copies of all data. Aug 14 '12 at 13:32
  • Since this is a 3rd-party app, check with the vendor if this is allowed first (assuming you continue to have a support contract).
    – Jon Seigel
    Aug 14 '12 at 13:33

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.