Luis Cazares (10/16/2012)
This might work, but an index might help even more (if you don't have one). I won't give my advice on the index because I'm learning as well about the best way to design them.:-D Maybe a more experienced one can help you.
[/code]
So far as the OP has said there are no primary keys. So I would be inclined to put a primary key (clustered) for the rate table on (from_date, country), and the same for the record table on (date, country), or maybe country should be first for one or both of those. But there are people with more knowledge of index choice than I have, who might suggest something different. And actually, I think, we would need to know a bit more about the shape of the data - how often to rates change, for example, what's the distribution of numbers of record entry per country per day, what's the distribution of numbers of days covered in a query, how often does "old" data get looked at (and what does "old" mean), and more, before we could be really sure what indexes would be useful.