I need to store simple data - suppose I have some products with codes as a primary key, some properties and validity ranges. So data could look like this:
Products code value begin_date end_date 10905 13 2005-01-01 2016-12-31 10905 11 2017-01-01 null
Those ranges are not overlapping, so on every date I have a list of unique products and their properties. So to ease the use of it I've created the function:
create function dbo.f_Products ( @date date ) returns table as return ( select from dbo.Products as p where @date >= p.begin_date and @date <= p.end_date )
This is how I'm going to use it:
select * from <some table with product codes> as t left join dbo.f_Products(@date) as p on p.code = t.product_code
This is all fine, but how I can let optimizer know that those rows are unique to have better execution plan?
I did some googling, and found a couple of really nice articles for DDL which prevents storing overlapping ranges in the table:
- Self-maintaining, Contiguous Effective Dates in Temporal Tables
- Storing intervals of time with no overlaps
But even if I try those constraint I see that optimizer cannot understand that resulting recordset will return unique codes.
What I'd like to have is certain approach which gives me basically the same performance as if I stored those products list on certain date and selected it with
date = @date.
I know that some RDMBS (like PostgreSQL) have special data types for this (
Range Types). But SQL Server doesn't have anything like this.
Am I missing something or there're no way to do this properly in SQL Server?