SQL : "Synonym Swap" Design Pattern

Frequently, I get asked to explain a database design pattern involving "synonym-swapping."  Though I'm getting better at clarifying this approach, it is still often difficult for the business-side folks to really understand.  I will now attempt to write a post to direct these people so that they do not have to listen to my ramblings.

First, database synonyms are a type of "alias", or alternate name for a table, view, sequence, or other schema objects in a database.  Some people refer to them as "pointers"  because sometimes a synonym might point to one table and at another time it will point to a different table.

Synonyms are great ways of referencing one or more tables in your database.  You can reference one or more tables by a single name. For example, say you had table that contained orders for your company.  Your orders table has millions of rows and needs to be accessed 24-hours a day.  However, what if updating the data in the table takes a couple of hours?   Also, what if maintenance takes a few hours?  Your users do not have the time to wait or suffer through a performance degradation.  What will you do?

1. Create two tables that are exact duplicates of each other ("ORDERS_TABLE_A", "ORDERS_TABLE_B").  Then create two synonyms, "Orders_Staging","Orders."  The "Orders" synonym will ALWAYS point to the table with the latest data.

2. Say "Orders" is currently pointing to "ORDERS_TABLE_A" but the data needs refreshed.  You will update "ORDERS_TABLE_B" then "swap" the synonym to point to "ORDERS_TABLE_B."  The time it takes to toggle a synonym between two tables is almost a split second.   If something goes awry during the data refresh, then the synonyms never get swapped and the user has no interruption of service either way.
Synonyms are a very useful tool as it means that in your application does not have to worry about changing the  table names from "ORDERS_TABLE_A" to "ORDERS_TABLE_B" and vice versa every time you do a refresh.

As you can see Synonyms are very useful tools for using aliases and can help when you have processes that involve building new tables to the side of existing ones for performance reasons.

MSDN Synonyms

An Even Better Explanation