In 2005 Microsoft introduced the synonym functionality for SQL Server. While Synonyms are not something SQL developers are likely to use on a daily basis, they are very powerful and useful, despite being relatively simple. They are essentially a placeholder that can be used as a static value in a SQL statement that points towards another object. While static redirects are a core competency, the ability to modify the base object (what the synonym “points” to) is a huge part of what makes them flexible and worth implementing in the right scenarios. This article will cover what a synonym is, how they can be leveraged during an ETL (Extract, Transform, and Load) process, and finally will give code examples of how they function, and how you can implement them.
Microsoft defines SQL Server synonyms as functionality that “Provides an alternative name for another database object, (referred to as the base object) that can exist on a local or remote server”. Synonyms “provide a layer of abstraction that protects a client application from changes made to the name or location of the base object”. Base objects can include most tables, procedures, and functions – with a few exceptions. For database developers, this means that synonyms allow us to create a static placeholder that can be used in code, that will point towards an object that we can then change as needed – without updating the assets that have hardcoded the synonym. This can be useful in many circumstances, but we will be discussing the use specifically in an ETL setting.
A common data warehousing task is obtaining and loading incremental data. Often this process is referred to as ETL. Synonyms can be a useful tool to aid in the automation of ETL processes. A common tactic is to standardize the control flow of the process and reuse it in several similar scenarios. For example, let us say that we have a few data sources that need to have data extracted from them. While the queries and extracted data will be different, a good ETL design will standardize the naming of the assets, the methods that handle the execution of the query (i.e. handler), and what is done with the data once it is extracted.
In the coding examples below, we will look at how one job can create a batch of tables that can each be used to store data from a specific source, on a specific day. We will use synonyms to load data to this table, even though the load procedure does not know the specific underlying table it is populating. The translate step can also use this synonym to translate data from a different table each day, without ever changing the translate code. The focus of this scheme is to keep extraction as simple as possible, while containing the bulk of the logic in the translate step. If this were to be done without synonyms, a considerable amount of dynamic SQL would be required. While there is a time and place for dynamic SQL, the consensus is to only use it when safe to do so, and when it is absolutely necessary. Even though synonyms are not a complete replacement for dynamic SQL, they can replace it in situations where a table or database may need to change based on context. Part of what makes synonyms so flexible is that the binding between a synonym and its base object is by name only. Meaning the base object can be modified, dropped, or replaced by another object that has the same name as the original base object. Since the synonym is not evaluated until called, it can be created, or replaced up until the point that the referencing statements are executed – even if, for example, the synonym is defined in the same procedure that is referencing it.
In this section, we will look at how synonyms are created, deleted, and modified, as well as an example of how they can be used in an ETL process.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE dbo.ExampleTable (ID INT) INSERT INTO dbo.ExampleTable VALUES (1),(2),(3),(4) CREATE TABLE dbo.ExampleTable2 (ID VARCHAR(1)) INSERT INTO dbo.ExampleTable2 VALUES ('A'),('B'),('C'),('D') |
Next, create a synonym to reference the first example table:
1 |
CREATE SYNONYM dbo.ExampleSynonym FOR dbo.ExampleTable |
If you query either the table, or synonym, the results are identical.
1 2 3 |
SELECT * FROM dbo.ExampleSynonym SELECT * FROM dbo.ExampleTable |
If you modify the data, both will reflect the change:
1 2 3 4 5 |
DELETE FROM dbo.ExampleSynonym WHERE ID = 4 SELECT * FROM dbo.ExampleSynonym SELECT * FROM dbo.ExampleTable |
A very useful feature of synonyms is that the underlying information is stored in the database, and can be referenced. Take a look at the synonym you just created by querying the sys.synonym table:
1 |
SELECT * FROM sys.synonyms WHERE [name] = 'ExampleSynonym' |
Here you can see the synonym name, and the base object it references. Another important column is the schema. Identically named synonyms can be created with different schemas. If you do not specify a schema during creation, the default database schema will be used. It is considered a good practice to explicitly supply a schema during creation, as to avoid creating it in an unexpected schema.
Synonyms cannot be directly modified, so to do so we need to drop and recreate them.
1 2 3 |
DROP SYNONYM dbo.ExampleSynonym CREATE SYNONYM dbo.ExampleSynonym FOR dbo.ExampleTable2 |
Run the below queries to see this change:
1 2 3 |
SELECT * FROM sys.synonyms WHERE [name] = 'ExampleSynonym' SELECT * FROM dbo.ExampleSynonym |
Many actions you can perform on an object, you can do with a synonym – i.e. Inserts, Updates, and Deletes. However others may reference the synonym itself instead of the underlying object. For example, you cannot drop a base object by referencing its synonym:
1 |
DROP TABLE dbo.ExampleSynonym |
The OBJECT_ID function will also return the OBJECT_ID for the synonym, not the base object.
1 2 3 |
SELECT OBJECT_ID('dbo.ExampleSynonym') SELECT OBJECT_ID('dbo.ExampleTable') |
In these situations, the sys.synonyms table can be used to get the information needed to process those actions:
1 2 3 4 5 6 7 8 9 |
DECLARE @DelStatement VARCHAR(100) = 'Drop Table ' + ( SELECT base_object_name FROM sys.synonyms WHERE [name] = 'ExampleSynonym' ) SELECT @DelStatement --EXEC(@DelStatement) --UnComment to Drop Table |
Before we move on to the next section, these commands can be used to clean up the example assets created so far:
1 2 3 4 5 6 7 8 9 |
IF OBJECT_ID('dbo.ExampleTable2') IS NOT NULL DROP TABLE dbo.ExampleTable2 IF OBJECT_ID('dbo.ExampleTable') IS NOT NULL DROP TABLE dbo.ExampleTable DROP SYNONYM dbo.ExampleSynonym |
The next example moves beyond the basic commands and shows how synonyms can be applied to manage incremental data loads in an ETL process. As mentioned above, in this example scenario, a table is created every day for each data source.
First create some template tables. This would only need to be done once, when setting up the data source the first time. The thought is that a simple query would be performed on a data source, with little to no transformation being done.
1 2 3 4 5 6 7 8 9 10 11 |
F OBJECT_ID('TableA_TEMPLATE') IS NULL CREATE TABLE TableA_TEMPLATE (ID INT, Col1 INT) IF OBJECT_ID('TableB_TEMPLATE') IS NULL CREATE TABLE TableB_TEMPLATE (ID INT, Col2 VARCHAR(255)) IF OBJECT_ID('TableC_TEMPLATE') IS NULL CREATE TABLE TableC_TEMPLATE (ID INT, Col3 BINARY) |
The code below will create/execute the following actions for each of the tables in the pfx cursor.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
--Creates variables that will be used to create tables and synonyms DECLARE @SynonymDrop_Current VARCHAR(255) DECLARE @SynonymCreate_Current VARCHAR(255) DECLARE @NewTableName VARCHAR(255) DECLARE @CreateTable VARCHAR(255) DECLARE @Postfix VARCHAR(8) = FORMAT(GETDATE(), 'yyyyMMdd') DECLARE @Prefix VARCHAR(25) --Creates Cursor for each table prefix that will be processed DECLARE Pfx CURSOR FAST_FORWARD FOR SELECT 'TableA' UNION SELECT 'TableB' UNION SELECT 'TableC' OPEN Pfx FETCH NEXT FROM Pfx INTO @Prefix WHILE @@FETCH_STATUS = 0 BEGIN --Creates commands to be executed SET @SynonymDrop_Current = 'DROP SYNONYM IF EXISTS dbo.' + @Prefix + 'Current' SET @SynonymCreate_Current = 'CREATE SYNONYM dbo.' + @Prefix + 'Current FOR [UBBstage].[dbo].' + @Prefix + @Postfix SET @NewTableName = '[dbo].' + @Prefix + @Postfix SET @CreateTable = 'SELECT * INTO ' + @NewTableName + ' FROM [dbo].' + @Prefix +'_TEMPLATE' --Drops the table that will be created if it already exists (i.e. if this has been run more than once in a day) IF OBJECT_ID(@NewTableName) IS NOT NULL EXEC ('DROP TABLE ' + @NewTableName) --View Commands that will be executed SELECT @CreateTable SELECT @SynonymDrop_Current SELECT @SynonymCreate_Current ----Uncomment to execute commands --EXEC (@CreateTable) --EXEC (@SynonymDrop_Current) --EXEC (@SynonymCreate_Current ) FETCH NEXT FROM Pfx INTO @Prefix END CLOSE Pfx; DEALLOCATE PFX; |
As a result – using TableA as an example – a table would be created named dbo.TableA20210129, and a synonym dbo.TableACurrent would be created to reference it. The same thing would happen for TableB and TableC.
If this were to be used in an ETL process, this could be run to prepare for a daily data load. An initial extraction procedure could then be written like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
INSERT INTO dbo.TableACurrent SELECT * FROM db.dbo.SourceTable A view or procedure could then be written to transform the data like: SELECT ID , COUNT(*) AS ExampleTransformation , SUM(Col1) AS ExampleTransformation2 FROM dbo.TableACurrent GROUP BY ID |
This would be repeatable and scalable for each data source that is needed. The source queries can be kept simple, and all logic will be in the transform step, which is important for troubleshooting issues as the data store is populated. Since each table holds a relatively small amount of data (one day), they will have acceptable performance in most cases without indexing or trimming down to the bear minimum rows/columns during the source table query. The table will of course need to be managed, as many will be created if done daily, but this can be done on interval, and can be flexible to individual requirements (i.e. back it up into a tall table or delete it if source data is persisted elsewhere).
To clean up the assets created in this section, run the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
IF OBJECT_ID('dbo.TableA_TEMPLATE') IS NOT NULL DROP TABLE dbo.TableA_TEMPLATE IF OBJECT_ID('dbo.TableB_TEMPLATE') IS NOT NULL DROP TABLE dbo.TableB_TEMPLATE IF OBJECT_ID('dbo.TableC_TEMPLATE') IS NOT NULL DROP TABLE dbo.TableC_TEMPLATE DECLARE @DropStarementA VARCHAR(500) = 'IF OBJECT_ID(''dbo.TableA' + FORMAT(GETDATE(), 'yyyyMMdd')+''') IS NOT NULL DROP TABLE dbo.TableA' + FORMAT(GETDATE(), 'yyyyMMdd') DECLARE @DropStarementB VARCHAR(500) = 'IF OBJECT_ID(''dbo.TableB' + FORMAT(GETDATE(), 'yyyyMMdd')+''') IS NOT NULL DROP TABLE dbo.TableB' + FORMAT(GETDATE(), 'yyyyMMdd') DECLARE @DropStarementC VARCHAR(500) = 'IF OBJECT_ID(''dbo.TableC' + FORMAT(GETDATE(), 'yyyyMMdd')+''') IS NOT NULL DROP TABLE dbo.TableC' + FORMAT(GETDATE(), 'yyyyMMdd') EXEC (@DropStarementA) EXEC (@DropStarementB) EXEC (@DropStarementC) DROP SYNONYM IF EXISTS dbo.TableACurrent DROP SYNONYM IF EXISTS dbo.TableBCurrent DROP SYNONYM IF EXISTS dbo.TableCCurrent |
These have been just a few examples of how synonyms can be used. Here are a few others:
There are of course many other areas where synonyms can be used, so this list is not exhaustive. Any time a layer of abstraction would be beneficial, synonyms may be of use. This was written as an introduction and is not a definitive guide. Here is some further reading if you are interested in the complexities of the functionality:
https://en.wikipedia.org/wiki/Synonym_(database)