June 4, 2012 at 1:41 pm
I'm not a fan of columns with multiple data values in them, or columns I need to do bitwise operations on. With the current design it's trivial to find what applies on monday (alone or with any other day), with a bitmask I'd need to do work on the column to figure that out.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2012 at 1:49 pm
My best (educated) guess based on what you've posted so far is:
A filtered index on ISAPPLIEDFORSPECIFICDAYS = 1, including the daily columns.
That index may or may not help -- you'd have to try it to be sure.
For example:
CREATE NONCLUSTERED INDEX <index_name>
ON <schema_name>.<table_name> (ISAPPLIEDFORSPECIFICDAYS)
INCLUDE (ISSUNDAY, ISMONDAY, ISTUESDAY, ISWEDNESDAY,
ISTHURSDAY, ISFRIDAY, ISSATURDAY)
WHERE
ISAPPLIEDFORSPECIFICDAYS = 1 AND
--don't include row unless at least one day is active
1 IN (ISSUNDAY, ISMONDAY, ISTUESDAY, ISWEDNESDAY,
ISTHURSDAY, ISFRIDAY, ISSATURDAY)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 4, 2012 at 2:12 pm
GilaMonster (6/4/2012)
I'm not a fan of columns with multiple data values in them, or columns I need to do bitwise operations on. With the current design it's trivial to find what applies on monday (alone or with any other day), with a bitmask I'd need to do work on the column to figure that out.
Different styles and opinions, thanks for your input Gail.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 4, 2012 at 2:20 pm
Perry Whittle (6/4/2012)
GilaMonster (6/4/2012)
I'm not a fan of columns with multiple data values in them, or columns I need to do bitwise operations on. With the current design it's trivial to find what applies on monday (alone or with any other day), with a bitmask I'd need to do work on the column to figure that out.Different styles and opinions, thanks for your input Gail.
Yup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply