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!