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:

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 

Comments

Popular posts from this blog

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

asp.net - Problems sending emails from forum -