sql - Efficient way of storing date ranges -

i need store simple data - suppose have products codes primary key, properties , validity ranges. data this:

products code    value   begin_date  end_date 10905   13      2005-01-01  2016-12-31 10905   11      2016-01-01  null 

those ranges not overlapping, on every date have list of unique products , properties. ease use of i've created function:

create function dbo.f_products (     @date date ) returns table return (     select     dbo.products p             @date >= p.begin_date ,         @date <= p.end_date ) 

this how i'm going use it:

select     * <some table product codes> t     left join dbo.f_products(@date) p on         p.code = t.product_code 

this fine, how can let optimizer know rows unique have better execution plan?

i did googling, , found couple of nice articles ddl prevents storing overlapping ranges in table:

• self-maintaining, contiguous effective dates in temporal tables
• storing intervals of time no overlaps

but if try constraint see optimizer cannot understand resulting recordset return unique codes.

what i'd have approach gives me same performance if stored products list on date , selected date = @date.

i know rdmbs (like postgresql) have special data types (range types). sql server doesn't have this.

am missing or there're no way in sql server?

a solution without gaps might this:

declare @tbl table(id int identity,[start_date] date); insert @tbl values({d'2016-10-01'}),({d'2016-09-01'}),({d'2016-08-01'}),({d'2016-07-01'}),({d'2016-06-01'});  select * @tbl;  declare @datefilter date={d'2016-08-13'};  select top 1 *  @tbl [start_date]<=@datefilter order [start_date] desc 

important: sure there (unique) index on start_date

update: different products

declare @tbl table(id int identity,productid int,[start_date] date); insert @tbl values --product 1 (1,{d'2016-10-01'}),(1,{d'2016-09-01'}),(1,{d'2016-08-01'}),(1,{d'2016-07-01'}),(1,{d'2016-06-01'}) --product 1 ,(2,{d'2016-10-17'}),(2,{d'2016-09-16'}),(2,{d'2016-08-15'}),(2,{d'2016-07-10'}),(2,{d'2016-06-11'});  declare @datefilter date={d'2016-08-13'};  partitionedcount (     select row_number() over(partition productid order [start_date] desc) nr           ,*     @tbl     [start_date]<=@datefilter ) select * partitionedcount nr=1