Efficient way of storing date ranges

Question

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?

Answer 1

EDIT: My original answer was using an INNER JOIN, but the questioner wanted a LEFT JOIN.

CREATE TABLE Products
  (
  [Code] INT NOT NULL
  , [Value] VARCHAR(30) NOT NULL
  , Begin_Date DATETIME NOT NULL
  , End_Date DATETIME NULL
  )

/*
Products
code    value   begin_date  end_date
10905   13      2005-01-01  2016-12-31
10905   11      2017-01-01  null
*/
INSERT INTO Products ([Code], [Value], Begin_Date, End_Date) VALUES (10905, 13, '2005-01-01', '2016-12-31')
INSERT INTO Products ([Code], [Value], Begin_Date, End_Date) VALUES (10905, 11, '2017-01-01', NULL)

CREATE NONCLUSTERED INDEX SK_ProductDate ON Products ([Code], Begin_Date, End_Date) INCLUDE ([Value])

CREATE TABLE SomeTableWithProductCodes
  (
  [CODE] INT NOT NULL 
  )

 INSERT INTO SomeTableWithProductCodes ([Code]) VALUES (10905)

Here is a prototypical query, with a date predicate. Note that there are more optimal ways to do this in a bulletproof fashion, using a "less than" operator on the upper bound, but that's a different discussion.

SELECT
  P.[Code]
  , P.[Value]
  , P.[Begin_Date]
  , P.[End_Date]
FROM
   SomeTableWithProductCodes ST
   LEFT JOIN Products AS P ON
     ST.[Code] = P.[Code]
     AND '2016-06-30' BETWEEN P.[Begin_Date] AND ISNULL(P.[End_Date], '9999-12-31')

This query will perform an Index Seek on the Product table.

Here is a SQL Fiddle: SQL Fiddle - Products and Dates

Answer 2

You can create an indexed view that contains a row for each code/date in the range.

ProductDate (indexed view)
code    value   date
10905   13      2005-01-01
10905   13      2005-01-02
10905   13      ...
10905   13      2016-12-31
10905   11      2017-01-01
10905   11      2017-01-02
10905   11      ...
10905   11      Today

Like this:

create schema digits
go

create table digits.Ones (digit tinyint not null primary key)
insert into digits.Ones (digit) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)

create table digits.Tens (digit tinyint not null primary key)
insert into digits.Tens (digit) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)

create table digits.Hundreds (digit tinyint not null primary key)
insert into digits.Hundreds (digit) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)

create table digits.Thousands (digit tinyint not null primary key)
insert into digits.Thousands (digit) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)

create table digits.TenThousands (digit tinyint not null primary key)
insert into digits.TenThousands (digit) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)
go

create schema info
go

create table info.Products (code int not null, [value] int not null, begin_date date not null, end_date date null, primary key (code, begin_date))
insert into info.Products (code, [value], begin_date, end_date) values 
(10905, 13, '2005-01-01', '2016-12-31'),
(10905, 11, '2017-01-01', null)

create table info.DateRange ([begin] date not null, [end] date not null, [singleton] bit not null default(1) check ([singleton] = 1))
insert into info.DateRange ([begin], [end]) values ((select min(begin_date) from info.Products), getdate())
go

create view info.ProductDate with schemabinding 
as
select
    p.code,
    p.value,
    dateadd(day, ones.digit + tens.digit*10 + huns.digit*100 + thos.digit*1000 + tthos.digit*10000, dr.[begin]) as [date]
from
    info.DateRange as dr
cross join
    digits.Ones as ones
cross join
    digits.Tens as tens
cross join
    digits.Hundreds as huns
cross join
    digits.Thousands as thos
cross join
    digits.TenThousands as tthos
join
    info.Products as p on
    dateadd(day, ones.digit + tens.digit*10 + huns.digit*100 + thos.digit*1000 + tthos.digit*10000, dr.[begin]) between p.begin_date and isnull(p.end_date, datefromparts(9999, 12, 31))
go

create unique clustered index idx_ProductDate on info.ProductDate ([date], code)
go

select *
from info.ProductDate with (noexpand)
where 
    date = '2014-01-01'

drop view info.ProductDate
drop table info.Products
drop table info.DateRange
drop table digits.Ones
drop table digits.Tens
drop table digits.Hundreds
drop table digits.Thousands
drop table digits.TenThousands
drop schema digits
drop schema info
go

Answer 3

A solution without gaps might be this:

DECLARE @tbl TABLE(ID INT IDENTITY,[start_date] DATE);
INSERT INTO @tbl VALUES({d'2016-10-01'}),({d'2016-09-01'}),({d'2016-08-01'}),({d'2016-07-01'}),({d'2016-06-01'});

SELECT * FROM @tbl;

DECLARE @DateFilter DATE={d'2016-08-13'};

SELECT TOP 1 * 
FROM @tbl
WHERE [start_date]<=@DateFilter
ORDER BY [start_date] DESC

Important: Be sure that there is an (unique) index on start_date

UPDATE: for different products

DECLARE @tbl TABLE(ID INT IDENTITY,ProductID INT,[start_date] DATE);
INSERT INTO @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'};

WITH PartitionedCount AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY [start_date] DESC) AS Nr
          ,*
    FROM @tbl
    WHERE [start_date]<=@DateFilter
)
SELECT *
FROM PartitionedCount
WHERE Nr=1

Answer 4

First you need to create a unique clustered index for (begin_date, end_date, code)

Then SQL engine will be able to do INDEX SEEK.

Additionally, you can also try to create a view for dbo.Products table to join that table with pre-populated dbo.Dates table.

select p.code, p.val, p.begin_date, p.end_date, d.[date]
    from dbo.Product as p
        inner join dbo.dates d on p.begin_date <= d.[date] and d.[date] <= p.end_date

Then in your function, you use that view as "where @date = view.date". The result can be either better or slightly worse... it depends on the actual data.

You also can try to make that view indexed (depends on how often it is being updated).

Alternatively, you can have better performance if you populate dbo.Products table for every date in the [begin_date] .. [end_date] range.

Answer 5

Approach with ROW_NUMBER scans the whole Products table once. It is the best method if you have a lot of product codes in the Products table and few validity ranges for each code.

WITH
CTE_rn
AS
(
    SELECT
        code
        ,value
        ,ROW_NUMBER() OVER (PARTITION BY code ORDER BY begin_date DESC) AS rn
    FROM Products
    WHERE begin_date <= @date
)
SELECT *
FROM
    <some table with product codes> as t
    LEFT JOIN CTE_rn ON CTE_rn.code = t.product_code AND CTE_rn.rn = 1
;

If you have few product codes and a lot of validity ranges for each code in the Products table, then it is better to seek the Products table for each code using OUTER APPLY.

SELECT *
FROM
    <some table with product codes> as t
    OUTER APPLY
    (
        SELECT TOP(1)
            Products.value
        FROM Products
        WHERE
            Products.code = t.product_code
            AND Products.begin_date <= @date
        ORDER BY Products.begin_date DESC
    ) AS A
;

Both variants need unique index on (code, begin_date DESC) include (value).

Note how the queries don't even look at end_date, because they assume that intervals don't have gaps. They will work in SQL Server 2008.