I use SQL 2008.
I have a logical entity (table) say Data with attributes (columns) (key, date, a,b,c,d).
the problem is a and b change values every day and that c and d change values at random dates and very slow. And also they change for each key individually. Say c,d for key = 1 will change today and next month and for key=2 tomorrow and a week later.
So my idea is to split on 2. MainData (key,date,a,b) and TemporalData (key, changeDate, c,d)
However I failed to write an efficient query - how to consume this.
I was thinking of somewhat a 'parametrized' view (not sure if it is possible at all). Say select * from vwData where date = 'xxxxx' and it will return me a,b for that particular day and also c,d which were effective for a particular key and that date.
I need a simple and clear way to select data from this construct. I also need it to be efficient as data will be huge in a year time
(Note: there will be only select where date = @singleDate never date between)
MainData TemporalData 1 20101001 1.0 2.0 1 20101001 2.0 3.0 1 20101002 2.0 3.0 1 20101003 8.0 9.0 1 20101003 4.0 5.0 1 20101004 6.0 3.0 select * from vwData where date = '20101002' 1 20101002 2.0 3.0 2.0 3.0 select * from vwData where date = '20101003' 1 20101003 4.0 5.0 8.0 9.0 select * from vwData where date = '20101004' 1 20101004 6.0 3.0 8.0 9.0