Using HierachyID to store time series data

Question

I have a SQL Server database that holds a large number of time series records -currently about 500 million. Each record has a datetime and a double data value which form the time series, and two other datetimes values. I'm looking at ways to improve the performance of queries without going down the data warehouse route.

Instead of storing the data and time as a DateTime I was looking at the new HierachyID in SQL Server 2008 and wondering if it would be possible to use this to store the date and time. I would imagine that the hierachy would be year, month, day, hour, minute.

Would this provide any performance gains and how would the indexing work?

Answer 1

I doubt it. Plus it would make programming against it much more complex. Is your clustered index on the datetime column? What kind of queries have bad performance? Also since you seem to not need sub-minute precision, you can reduce the size of your index pages by 4bytes per row if you move to SMALLDATETIME. Lower I/O = better performance.