Home Forums SQL Server 2008 SQL Server 2008 - General how to differentiate between two tables sharing the same name but belonging to different databases RE: how to differentiate between two tables sharing the same name but belonging to different databases

  • below86 (1/13/2014)


    Almost ever query we write will span more than one database, so that's why I prefer it.:-)

    If your system consists of multiple databases - and there is never the possibility that one or more of those databases grows to a point where it needs to be moved to another server, then 3-part naming makes sense.

    However, if the possibility exists that at some point in time one or more of those databases can be moved to a different server - then using synonyms makes sense.

    In either case, if someone changes the name of an object - drops an object - etc... then both will generate a failure.

    Synonyms are ideal if you have linked servers in your organization. On a test system, you would have linked server 'OtherSystemTest' and on the production system your linked server would be 'OtherSystem'. If you are using 4-part naming in your code - then promoting that code to production *requires* a code change be made as it is being applied.

    Using synonyms in this situation avoids any issues with making code changes between test and production and still allows for code comparisons between the environments.

    Synonyms are also ideal in situations where the names of the databases on a test system are different than the names in production. For example, some sites like to put a post-fix of _test on the database names in their test environment. In that situation, using 3-part naming doesn't work because you cannot promote that code to production without code changes - however, using synonyms means no changes to the code need to be made.

    There are good reasons to use either method - and both are valid.

    Note: I use both methods, but will only use 3-part naming when referencing objects outside the scope of the database where the procedure will live. This allows me to move that procedure to another database if needed - without worrying about accessing data in the 'old' database.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs