Synonyms

  • bkubicek

    SSChampion

    Points: 10786

    Comments posted to this topic are about the item Synonyms

  • Robert Sterbal

    SSChampion

    Points: 10987

    How many times does the view for a table:

    select * from table_name

    get improved by using the synonyms?

    Is this standards SQL?

    412-977-3526 call/text

  • Ivanova

    Ten Centuries

    Points: 1306

    In a previous role, I used synonyms to allow us to keep a fraud detection application running 24x7 with regular updates of 3rd party supplied data (known dodgy IP addresses, etc). There were two identical sets of tables, call them setA and setB with each table being referenced by a synonym. The application used the synonym at all times. When it was time to load the data, we cleared down the tables not currently in use, populated them with the latest supplied data, then redefined the synonyms to point to the newly-loaded set of tables. A metadata table kept track of which version of each table was current at any time and supported a completely automated refresh and switch process.

    The main issue I've seen with synonyms is DBA suspicion; those who think synonyms are an unnecessary complication are not motivated to support a database which uses them, and they are apt to blame the synonyms as soon as anything goes wrong.

  • richardmgreen1

    SSCrazy Eights

    Points: 9887

    We use synonyms to point between our test and production 3-rd party databases.
    It helps when we deploy code between our test and production SQL boxes.

    We've found it also saves typing out the full 4-part table name (we use linked servers to access the base data).

    When we get our databases sorted out properly, we can just repoint our synonyms and not have to change anything else.

  • Japie Botma

    SSCrazy

    Points: 2940

    richardmgreen1 - Monday, January 7, 2019 3:10 AM

    We use synonyms to point between our test and production 3-rd party databases.
    It helps when we deploy code between our test and production SQL boxes.

    We've found it also saves typing out the full 4-part table name (we use linked servers to access the base data).

    When we get our databases sorted out properly, we can just repoint our synonyms and not have to change anything else.

    We did about the same thing, but it does cause confusion when you look at code and do not realize that the table is actually on another server.

    5ilverFox
    Consulting DBA / Developer
    South Africa

  • Jeff Moden

    SSC Guru

    Points: 996493

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

  • Jeff Moden

    SSC Guru

    Points: 996493

    Ivanova - Monday, January 7, 2019 1:24 AM

    In a previous role, I used synonyms to allow us to keep a fraud detection application running 24x7 with regular updates of 3rd party supplied data (known dodgy IP addresses, etc). There were two identical sets of tables, call them setA and setB with each table being referenced by a synonym. The application used the synonym at all times. When it was time to load the data, we cleared down the tables not currently in use, populated them with the latest supplied data, then redefined the synonyms to point to the newly-loaded set of tables. A metadata table kept track of which version of each table was current at any time and supported a completely automated refresh and switch process.

    The main issue I've seen with synonyms is DBA suspicion; those who think synonyms are an unnecessary complication are not motivated to support a database which uses them, and they are apt to blame the synonyms as soon as anything goes wrong.

    Heh... no suspicion on the part of this DBA.  In fact, I won't allow for anything more than 2 part naming in any of the databases on boxes that I'm responsible for.

    And the use of "table flopping" as you've described is something I do all the time, as well, to keep tables online while a sister table is being loaded as a replacement.  In fact, I'm the one that finally got people to start doing that when large sets of tables needed to be copied from external sources (IBM Power Systems, in this case).  It's extremely effective and, if there's a failure during the load, you're still "in business" with the old data.

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

  • Rick-153145

    SSCrazy

    Points: 2713

    I have used them for the same reason as Jeff. I actually found the developers were the ones who didn't much like them strangely, couldn't work out why and didn't get much help from them on why either, still made them use them. 🙂

  • below86

    SSChampion

    Points: 11345

    At my prior job we used 3 part naming, our database names were the same on DEv, TEST, QA, PROD.  My current job the database names start with the prefix of the environment, PROD_, TEST_, DEV_...
    We uses synonyms a lot here, as a developer it doesn't bother me. The main issue is that all synonyms aren't named similar, some are just the table name, others have the suffix of '_syn'.

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • Stefan LG

    SSCommitted

    Points: 1936

    Yes, we have used them before to make the transition from a development server to a production server easier.
    In our case, some of the synonyms were referencing databases/tables via a Linked Server.
    If I can remember correctly, is was not possible to run these CREATE scripts unless the Linked Server and the remote databases/tables where physically available? (i.e. some sort of pre-check).

  • Rod at work

    SSC-Dedicated

    Points: 33361

    I've known of synonyms in SQL Server for years, but haven't created them. Definitely can see the usefulness of them, especially when some tables' named have been appended with things like "_dev" or "_test", which I've seen. I can see the usefulness of synonyms, but doubt that I'd use them much. I've just poked around at some of the databases I've got access to, looking for synonyms. I see that synonyms are used, but not heavily.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • GPO

    SSCarpal Tunnel

    Points: 4556

    Synonyms are important for dependency management. As Jeff Moden alluded to, sooner or later your friendly infrastructure maven is going to come a-knocking and say "Hey, good news guys, we're decommissioning server X and from 1 March all the databases that were there, are going to be moved to server Y." Over the years your team has written dozens or even hundreds of dependencies that pull data via linked servers. Depending on how lax your DBAs have been over the years there could be any number of things you don't know a lot about, engaging in a bit of consensual cross server querying.

    If you've been diligent, you have not hard-coded the server names into your SQL. Instead you've created a synonym and put the four-part name in that. So when the time comes to mover over to your shiny new server Y, you simply script out all your synonyms, change them to point to the new server, and run. Simples.

    If you haven't been diligent, help desk is going  to field days of calls for broken views, stored procedures, reports etc etc etc.

    The one gripe I have against synonyms, is that in all versions of SSMS (that I'm aware of) intellisense sees a synonym as a syntax error. It's an annoyance rather than a deal breaker, but I have often wondered whether the way SSMS reads the DBs' metadata could be improved to map the synonym through to the source.

    ...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

  • Thomas Franz

    Hall of Fame

    Points: 3642

    I think a problem with synonymes (as with several other "solutions") is SourceControl. When I use them to refer to database db_prod or db_test (depending on my environement) it will always be a change and potential conflict in SourceControl except I exclude the synonmys from it, what would lead to other problems (because views / procedures break without the synonym)

    God is real, unless declared integer.

  • Jeff Moden

    SSC Guru

    Points: 996493

    t.franz - Thursday, January 10, 2019 12:55 AM

    I think a problem with synonymes (as with several other "solutions") is SourceControl. When I use them to refer to database db_prod or db_test (depending on my environement) it will always be a change and potential conflict in SourceControl except I exclude the synonmys from it, what would lead to other problems (because views / procedures break without the synonym)

    So what's the difference between synonyms and views that use more than 2 part naming?  That's not a question on my part.  It's meant to be thought provoking. 😉

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

Viewing 14 posts - 1 through 14 (of 14 total)

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