• robert.sterbal 56890 - Sunday, January 6, 2019 9:47 PM

    How many times does the view for a table:

    select * from table_name

    get improved by using the synonyms?

    Is this standards SQL?

    There's no performance difference between a Synonym and a Pass-Through-View (PTView for short).

    The reason why I prefer Synonyms for inter-database code is because it keeps things more separate when it comes to views.  I also don't have to update any views if someone makes the mistake of using SELECT * for the PTView.

    As for the reason why I use Synonyms for such things instead of using 3 or 4 part naming is because there is no guarantee that database names and which server a database will be moved to will be the same.  I also have multiple copies of certain databases in Dev and Staging and that means the database MUST have different names.  When the code moves from one environment to another, I don't need to do a thing because the 2 part naming convention in the code recognizes the common named Synonyms in the database where the code is deployed to.  There is no need to find the database name in any of the code and change it.  The Synonyms take care of all of that.

    The same goes for a restore from, say, Production to a lesser environment.  Instead of having to change any code for database name changes due to being in a different environment, I only need to generate the code for the Synonyms, do a couple of Search'n'Replaces, drop all the Synonyms, and use the generated script to rebuild them to point them to the correct databases.

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