Home Forums Article Discussions Article Discussions by Author Discuss content posted by Luis Martin Using Computed Columns RE: Using Computed Columns

  • February 12, 2019 at 12:54 pm

    #2021841

    drew.allen - Tuesday, February 12, 2019 12:26 PM

    thisisfutile - Tuesday, February 12, 2019 11:57 AM

    We have a database that is filled with tables utilizing an integer column for things like 'Status'.  For example, Customer t has a Status column and 0-3 are the integers used in this column.  It also has a StatusComputed column that's computed as (case [Status] when 0 then 'Open' when 1 then 'Hold' when 2 then 'Closed' when 3 then 'Prospect' else 'Unknown' end).  Without doing any testing I'd have to guess that the query optimizer would perform better with a query that has WHERE Status = 0 rather than WHERE StatusComputed = 'Open'.  Does anyone have any opinions?

    I prefer using lookup tables for this.  It's much easier to add new rows to a lookup table than it is to modify an existing table if you need to add another status, especially if all code changes need to go through a change control process.  (Adding a row isn't usually considered a code change, so it doesn't need to go through the change control process.)

    Drew

    I'm actually not aware of what a lookup table is.  Are you referring to a normalized table?  Like status is the key field in the Customer table and they reference a lookup table called "CustomerStatus"?

    For reference, the DB I'm referring to is maintained by our software developer.  It's their design and the ERP software references the Status column (0-3) and displays Open, Hold, Closed, or Prospect based on its C# code.  They decided to put the computed columns in for us customers simply for reference.  No more need to write a case statement in the select clause, instead just select the StatusComputed column.

    I'm interested to know more about lookup tables though.  I searched it and my first hit was a Joe Celko article.  Since he's not for the faint of heart (and since I fainted) I figured I'd let you respond to point me in the right direction.  😀