• MMartin1 (6/28/2016)


    I am wondering about the three part naming convention and its substitution with a synonym. I envision a scenario where the code reads like

    /* block 1*/

    SELECT ...

    FROM ProductionDB_test.dbo.tableA

    I instead use the synonym for tableA

    /* block 2*/

    SELECT ...

    FROM synTableA

    If I present /* block 1*/ to a live system (assuming no synonym) with this wrong database I would think the statement would fail as it should. I dont think there should be a database named "ProductionDB_test" on a live production system. Else if there is such a database there then what about getting it wrong in the synonym when you have to maintain plenty of synonyms and an error entry sneaks in? Just wondering what prompts security with synonyms on three parts named objects. Thanks for the good article!

    The second block should use the 2 part naming convention. It helps prevent schema accidents and helps a bit (at least it used to back in the day) with performance.

    As for getting synonyms wrong, sure, I agree. It's certainly possible but, once established, synonyms usually don't change and you normally don't restore Dev to Prod to do promotions. As with all else, though, "It Depends". For us, such synonyms have been a life saver because of a sometimes silly but usually effective requirement to name databases with an "underscore environment" extension. This also allows us to have multiple nearly identical databases on Dev boxes so that multiple different tacks on development of large batch processing systems can simultaneously exist without necessarily creating a full environment (other utility, staging, and security databases) for each endeavor.

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