Case or LOOKUP Table on Common but simplistic values

  • NOTE: I get that this may be too broad a question to answer and if so just say that, I get it.

    I do a LOT of the below and sometimes in the same table.   I was just curious if there is any recommendation on whether or not a Lookup Table is a good idea for simple items like this or is it best to just do like below?  Obviously the amount of data and is on can hint which way in terms of speed but with speed/performance aside what is the recommended mind set for dealing with this or is the answer more of  a "Dealers Choice" meaning whatever the person responsible for maintaining the code likes to do?

    I have a number of lookup tables but I've never considered doing what is basically a table for Yes/No On/Off Active/Inactive like values.

    Case WHEN T1.Col1 = 0 THEN 'FALSE/OFF/INACTIVE' ELSE 'TRUE/ON/ACTIVE' END AS 'bIsTrue/On/Active'

    Kindest Regards,

    Just say No to Facebook!
  • if it's only one or two values, then I probably wouldn't use a lookup table. It would be simpler to just use a CASE statement, but if there are lots of values you need to match then absolutely I'd use a lookup table.

  • I'd flip the check around: check first for = 1 as 'True' else 'False'.  That way NULL will show as false, if a NULL happens to slip in there.

    Use the CASE if you prefer, but put the CASEs into a view so that they're only coded one time and are then consistent for everyone.  You don't want to have to code that over and over, as mistakes are bound to creep in.

    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".

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply