Thanks clubbavich,

You're quite right, I'm un-normalising the data. Bad me!

Each system has a number of sensors (a little as 1, as many as 20), of 2 different types (but with more planned).

Regardless of sensor type, each time we get data, it comes as 4 tinyints showing the various properties at that time. That looks like the plan for the near to mid-term future.

Although we do not have table partitioning in our version of SQL server (it's Web Edition). We could look at making the split by date and not system so our tables would be named tblSensorData2012Q1, tblSensorData2012Q2 etc. and UNION queries that span more than one quarter when retrieving. Once a quarter has finished the index can be defragged and it will effectively becomes used for SELECT queries only. If we get a lot more data coming in, then we could shrink the time-frame of each table.

I did a comparison in table sizes before/after the weekend. We are adding approximately 400,000 rows a day based on current usage, so I'm looking at reaching 500,000,000 records in total over the next year [41% growth] (assuming no new sensors, extra systems or enabling of real-time reporting).

Also realised that there has been no index defragging done... some of those tables are 80%+ (with over 100,000 rows). However that's a task for another day... 🙂