Hello,

I'm querying a table where a lot of dates are stored per row. Each record can have up to 15 nullable dates i.e. productid, dateA, dateB, dateC.....dateO

This leaves me wondering how best to query this in the most performant way - I need to be able to query on a date range - between @dateLow and @dateHigh. The only thing is, with so many date columns per row, it seems a bit cumbersom on poor ol sql server to have to use an OR pedicate to do something like:

where dateA between @dateLow and @dateHigh OR dateB between @dateLow and @dateHigh and.....etc etc

I'm assuming that to check through 15 nullable columns in this manner for each record is not pretty. Is there a more efficient way to do this in terms of actual t-sql, or is my only hope of optimization a covering index or something?

 As you have up to 15 nullable dates :

solution1: do normalization

create a new child  table date_info to store date information , with fields  ( pk , datename,datevalue) , and insert all not null value dates in that table.

then drop these clumns from the original table.

study side effect on application that access that table for that change

solution 2: create a view that union all these fields

select pk,  'date1 '  datename, date1 from mytable  datevalue

 where  date1 is not null

union all

select pk,  'date2 ' , date2 from mytable  

 where  date2 is not null

....

...

- create index on datename, datevalue on the view

I don't think I can normalize any further really, the table I am querying consists of a ProductId column, then the date columns.

I like the idea of using an indexed view to get rid of the nulls though, I like it a lot!

That indexed view is the same as the suggested 'normalized' table, really.