Trigger Problem-Urgent

  • GSquared (2/9/2009)


    GilaMonster (2/9/2009)


    Vijaya Kadiyala (2/9/2009)


    What if the table already exists, so in this case we have to use Update Statement right!!

    Why?

    Alter Table Add .... and add the computed column to the existing table. Since it's computed, the column doesn't actually store data and can't be updated. It's only calculated when the data is retrieved

    I took his question as meaning the table already exists and there is already a column in it for that data.

    Yes, the column can be dropped and rebuilt, but you have to make darn sure that there is no code trying to update that column if you're changing from data storage to calculated. That can be complex in some environments, where not all the database code is in procs.

    I think that in a case that you drop a none computed column and replace it with a computed column, you can use an "instead of trigger" in order to make sure that no one will get a runtime error because he tried to update the new computed column.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • [font="Verdana"]It's usually not too hard to do a code search within the database and applications to find whether a column is referenced.

    I agree that the use of an instead of trigger should allow any "un-caught" code to still work as expected.[/font]

  • Bruce W Cassidy (2/9/2009)


    [font="Verdana"]You can also persist the calculated column, in which case it will only be calculated the once. Bits are good choices for persistance, since they take up soooo much space (not). 😀

    And of course you can index calculated columns. So all in all, the calculated column is a far better solution.

    [/font]

    Heh... dang it... ya beat me on the "persisted" option.

    Shifting gears a bit... not all Calculated Columns can be indexed... the formula for the calculated column must be deterministic in order to add an index to it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • You could always use a view.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Adi Cohn (2/9/2009)[hrI think that in a case that you drop a none computed column and replace it with a computed column, you can use an "instead of trigger" in order to make sure that no one will get a runtime error because he tried to update the new computed column.

    That's a good tip.

    Of course, one would have to ask, in this particular example, why the Expired flag was being updated from several places that would warrant having the trigger, but it could be very usefull for other calculated columns.

    Derek

  • Bruce W Cassidy (2/9/2009)


    [font="Verdana"]You can also persist the calculated column, in which case it will only be calculated the once. Bits are good choices for persistance, since they take up soooo much space (not). 😀

    And of course you can index calculated columns. So all in all, the calculated column is a far better solution.

    [/font]

    Since the column is calculated against getdate, there's no point in persisting it, since it will have to be recalculated at runtime every time it's queried.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (2/10/2009)


    Bruce W Cassidy (2/9/2009)


    [font="Verdana"]You can also persist the calculated column, in which case it will only be calculated the once. Bits are good choices for persistance, since they take up soooo much space (not). 😀

    And of course you can index calculated columns. So all in all, the calculated column is a far better solution.

    [/font]

    Since the column is calculated against getdate, there's no point in persisting it, since it will have to be recalculated at runtime every time it's queried.

    Since the column is calculated against getdate, it cannot be persisted. For a calculated column to be persisted it must, among some other requirements, reference only deterministic, precise functions. Getdate is not deterministic.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • [font="Verdana"]Sigh. There's always a critic. 😛

    Quite right, of course. A calculated column with getdate() in it will not persistable.[/font]

Viewing 8 posts - 16 through 22 (of 22 total)

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