Post reply

Computed Column

  • May 28, 2010 at 5:22 am

    #387041

    Hi,

    I am creating a table EmployeeResult. I want to include computed column which will be 1 if EmpAns = RealAns and will be 0 if not.

    CREATE TABLE EmployeeResult

    (ERId INT IDENTITY(1,1),

    QuestionNo TINYINT,

    EmpAns CHAR,

    RealAns CHAR,

    Comparison AS CASE EmpAns, RealAns

    WHEN EmpAns = RealAns THEN 1

    ELSE 0

    END

    PERSISTED)

  • May 28, 2010 at 5:48 am

    #1173844

    you had it almost perfect, just needed to remove the stuff between CASE and WHEN

    CREATE TABLE EmployeeResult

    (ERId INT IDENTITY(1,1),

    QuestionNo TINYINT,

    EmpAns CHAR,

    RealAns CHAR,

    Comparison AS

    CASE

    WHEN EmpAns = RealAns THEN 1

    ELSE 0

    END

    PERSISTED)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 28, 2010 at 6:11 am

    #1173856

    Thanks Lowell.

  • May 29, 2010 at 6:30 am

    #1174228

    Side note:

    It's not often worth persisted expressions like this - this optimiser very frequently chooses to recalculate the value from the base columns instead - particularly if the computed value is part of a useful index, and the base columns are.

    Not a huge consideration here, given the size of the persisted data and the complexity of the expression.

    For complex or otherwise expensive expressions, consider indexing the computed column without persisting it (or adding it as an INCLUDEd column on an existing index). It surprises some people that you can (very often) index a non-persisted computed column, but it is true.

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

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

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