Post reply

The Mysterious Case of the Missing Default Value

  • March 26, 2017 at 9:19 am

    #1934988

    sqldoubleg - Sunday, March 26, 2017 3:33 AM

    Jeff Moden - Saturday, March 25, 2017 7:44 PM

    @Raul,

    Didn't see this article when it was first published.  Job well done!  Thanks for taking the time to write it up!

    djrubin - Friday, March 24, 2017 10:11 AM

    Good stuff but why not go old school and just add a one-to-one table that has the extra column. App folks don't make all the rules. Once a table has that many records, adding columns always puts me in the mindset of adding tables to avoid just this issue. App selections utilize a view to "see" the whole "table" while their code needs to be modified anyway for the new data, so make them update/insert this additional table with PK/FK. 

    Just my two cents 🙂

    Application code doesn't need to be changed if you use 'instead of' triggers for DML operations on the view 🙂

    Cheers!

    EXACTLY!  The table is renamed to something else and the view is given the table name.  The "Instead of" triggers are used to make it all work seamlessly for Inserts, Updates, and Deletes as if it were a single table rather than a multi-table view and it all requires precisely ZERO changes to the application.  Being a "pass-through" view, Selects enjoy all of the benefits of the underlying indexes on the tables, as well.

    --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)

Viewing post 31 (of 30 total)

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