How to create fast database queries

Archive for June 30th, 2009

Overlapping ranges: SQL Server

Comments enabled. I *really* need your comment

From Stack Overflow:

I have an event table that specifies a date range with start_date and end_date fields.

I have another date range, specified in code, that defines the current week as week_start and week_end.

I'd like to query all events for the week.

This query needs to find all events that overlap the week.

The condition for overlapping ranges is well known and quite simple:

start_date < @week_end AND end_date > @week_start

However, in SQL more simple doesn't always mean more efficient, and the following condition, despite being more complex, can yield better performance:

(start_date > @week_start AND start_date < @week_end)
(@week_start BETWEEN start_date AND end_date)

Let's create a sample table and see how so:
Read the rest of this entry »

Written by Quassnoi

June 30th, 2009 at 11:00 pm

Posted in SQL Server