Storing large number of sensor data records

Question

I need to create a database that saves sensor data that will be queried to generate reports later on (Display a graph and AVG/MAX/MIN values for a given timeframe).

The data points look like this:

CREATE TABLE [dbo].[Table_1](
    [time] [datetime] NOT NULL,
    [sensor] [int] NOT NULL,
    [value] [decimal](18, 0) NULL
)

Data can be added in intervals ranging from seconds to minutes (depending on the sensor).

Should I worry about my Database growing too big when several years of data accumulate (The DB will run on a MS SQL Server 2008 workgroup edition)?

Answer 1

There are specialized historian databases, such as OSISoft's PI Historian that handle this type of data a lot better than a relational database. With PI you can configure a compression deviation for each data point, such that the data will not be archived unless it changes by at least that compression deviation. When you query for the historical data for a given point, you can ask PI to do interpolation of what the value would have been at the specified time even though your time period is between the archived values.

It's capable of a whole lot more, but you will have to explore that on your own because I don't intend on becoming an OSISoft salesman. However, this is definitely the way you want to go for storing large quantities of sensor data.