Consider the following tables, and tell me how well this would work for you:

--It's likely this table already exists in your database, but I'm creating it again here as an example

CREATE TABLE systems

(

system_id smallint IDENTITY (1,1),

other_data_about_systems varchar(50) NULL,

CONSTRAINT PK_Systems

PRIMARY KEY CLUSTERED (system_id)

WITH (IGNORE_DUP_KEY = OFF)

)

--This table will store your different sensor types

CREATE TABLE sensortypes

(

sensor_type_id tinyint IDENTITY (1,1),

sensor_type_desc varchar(50) NOT NULL,

CONSTRAINT PK_sensortypes

PRIMARY KEY CLUSTERED (sensor_type_id)

WITH (IGNORE_DUP_KEY = OFF)

)

--This table describes each individual sensor

CREATE TABLE sensors

(

sensor_id int IDENTITY (1,1), /* How you will uniquely identify the sensor.

Alternatively you could create a composite key using this field (as a tinyint) and the system_id fields.

That would make things a bit more complicated to code, but saves a little space space in this example (1 byte per row) */

system_id smallint

REFERENCES systems(system_id),

sensor_type_id tinyint

REFERENCES sensortypes(sensor_type_id),

CONSTRAINT PK_sensors PRIMARY KEY CLUSTERED (sensor_id)

WITH (IGNORE_DUP_KEY = OFF)

)

CREATE TABLE sensor_data_new

(

sensor_id int

REFERENCES sensors(sensor_id),

data_timestamp smalldatetime NOT NULL, /* accurate to 1 minute.

See http://msdn.microsoft.com/en-us/library/ms186724.aspx to choose the best type for your requirements.

I HIGHLY recommend that you store this timestamp information in GMT, so you aren't affected by daylight savings time*/

sensor_datapoint_a tinyint NOT NULL,

sensor_datapoint_b tinyint NOT NULL,

sensor_datapoint_c tinyint NOT NULL,

sensor_datapoint_d tinyint NOT NULL,

/* The assumption here is that future sensor types will continue to use the same format for their data.

If the datapoints are different between sensor types (sensor_datapoint_a represents X for one type of sensor,

and represents Y for another), I would recommend having separate tables for the data for each sensor type.

Not only does that provide a logical form of partitioning, but would also make the table columns more descriptive

(instead of generic names, the column can be named for the specifc datapoint stored). */

PRIMARY KEY PK_sensor_data CLUSTERED (sensor_id, data_timestamp)

/* To reduce fragmentation, it would be better to put the data_timestamp as the first part of the clustered index.

However, given that your queries will be focusing on retrieving data for a specific sensor for a given time, this index above is probably best */

WITH (IGNORE_DUP_KEY = OFF)

)

You can create that sensor_data_new as sensor_data_2012Q1, sensor_data_2012Q2, sensor_data_pre2012 for archival purposes, but keep in mind that would definitely require more manual maintenance. Each and every quarter, you would need to create a new table, move the data over, and update the view.

Another approach would be to do sensor_data_archive (Inserted into monthly, indexes rebuilt directly after. Inserts occur during offpeak time), sensor_data_last_x_months (inserted into weekly/daily, indexes rebuilt/reorganized after inserts, occurs during offpeak time, probably before the archive table), and have sensor_data_new be constantly written to.

With this, you create the tables once, modify the view once, create the agent jobs, and you have no additional manual tasks to perform on a regular basis.

Compared to the current design, it may end up with slightly less overhead per row than what you've got now, but it's most certainly more normalized and easier to query.