SQL Server Synonyms

We all remember the English lesson we had in grammar school where we learned about synonyms. Just in case you don’t remember, a synonym is a noun that has a similar meaning as other words; just like intelligent has the same meaning as smart. SQL Server allows you to create a synonym so you can define an alternate name for an object that already exists. Synonyms function like aliases on columns or tables. However, a synonym is created as an object in a database, instead of being temporally defined at runtime. In this article, I will explain how to create and use synonyms to help develop and manage TSQL code.

Why would you need or want a synonym?

When a synonym is defined, it references an object in a database, known as a base object. The base object can be in the same database where the synonym is defined, but it can also reside in a different database on the same server, or even on a different instance altogether. There are lots of situations where a synonym might provide some benefits. Let me list a few:

• When there is a need to coordinate an object rename over time because there are 100’s or even 1000’s of references in code to the object that is being renamed.
• To provide a layer of abstraction to the actual base object.
• When an application wants to reference an object as if it appears to be in the current database when, in reality, it resides in a different database or instance.
• When backwards compatibility is needed to an old legacy object.
• When there is a need to provide a security layer for protecting the base object.
• When an object needs to be moved to another database, or instance without affecting existing code.
• To simplify naming standards for lengthy, or confusing object names.
• To eliminate issues with cross database and server dependencies in downstream environments such as development, test, and quality assurance as part of a continuous integration build process.

Syntax for creating Synonyms

The syntax for creating a synonym, as found in the Microsoft documentation, is shown in Listing 1.

Listing 1. The SYNONYM syntax

-- SQL Server Syntax    
CREATE SYNONYM [ schema_name_1. ] synonym_name FOR <object>    
<object> :: =  
{  
    [ server_name.[ database_name ] . [ schema_name_2 ]. object_name   
  | database_name . [ schema_name_2 ].| schema_name_2. ] object_name  
}
-- Azure SQL Database Syntax  
CREATE SYNONYM [ schema_name_1. ] synonym_name FOR < object >   
< object > :: =  
{  
    [database_name. [ schema_name_2 ].| schema_name_2. ] object_name  
}

A synonym is simple to create. It can reference an object using one, two or three-part naming for both SQL Server and Azure SQL Database. But only SQL Server supports four-part names for referencing base objects that reside a different instance of SQL Server.

Synonyms can be created that reference the following types of objects:

Assembly (CLR) stored procedure	SQL inline-tabled-valued function
Assembly (CLR) scalar function	SQL table-valued function
Assembly (CLR) table-valued function	SQL stored procedure
Assembly (CLR) aggregate functions
Assembly (CLR) aggregate functions	View
Replication-filter-procedure	Table (User-Defined, including local and global temporary tables)
SQL scalar function

Creating, using, and managing synonyms

To better understand how to create, use and manage synonyms, I’ll go through a few examples. The examples will show you how to use synonyms that support different business situations, as well as how to manage synonyms.

Coordinating the renaming of an object

Once a database object has been created, and lots of application code has been written that references the object, it becomes a nightmare to rename the object. The nightmare comes from the amount of effort and coordination work required to make the name change without the application failing. If just one place is missed when coordinating the rename, the outcome could be disastrous. This is where a synonym can help minimize the risk associated with renaming a base object.

Suppose there is an application that has been built for a company that employs plumbers. Each plumber who has ever worked for the company is stored in a table named dbo.Plumber. The company has written a lot of code around this table to manage their business and their plumbers. Then one day, the company decides to expand its business to include electrical work. This expansion of the business now requires modifying their existing application code to support the new line of work. Additionally, they plan to write a lot of new code to support their expanding business. One of the modifications they want to make is to change the name of the dbo.Plumber table to dbo.Employee as soon as possible. This way, they can start using this new table name for any new code they plan to write, while they coordinate the name change for all the existing code.

By using a synonym, they can make the name change now, and ensure all the existing code doesn’t fail. Then as time allows, they can slowly change the old code to use the new table. Once all the legacy code has been changed to use the new name, then the synonym can be dropped. To rename the dbo.Plumber table and create the synonym, a DBA could execute the code in Listing 2.

Listing 2. Renaming base object and creating synonym to support a name change

EXEC sp_rename 'dbo.Plumber', 'Employee';
CREATE SYNONYM dbo.Plumber
   FOR dbo.Employee;

The code in Listing 2 first renames the exiting dbo.Plumber table to dbo.Employee and then creates the synonym to support the renaming effort. This code will run very quickly to minimize the risk of any errors occurring between the time the dbo.Plumber table is renamed and the dbo.Plumber synonym is created.

Creating a synonym for security purposes

In this example, assume that a company has a security policy that does not allow developers to update production data. They have this policy so developers don’t mistakenly update production data when they thought they were working in development. However, there are situations, like bad data issues, where developers need to update a production table. When this situation occurs, a synonym can be created, with appropriate permissions, to allow developers to update production so they can fix the data issue.

Suppose there was a data issue in the dbo.Article table that needed to be resolved by issuing SELECT, INSERT, UPDATE and/or DELETE statements in the production environment. To provide the developers update access to resolve this issue, a synonym with appropriate permissions could be created. To accomplish this the code in Listing 3 could be run.

Listing 3. Code to create synonyms and permissions.

CREATE SYNONYM dbo.Dev_Article
   FOR dbo.Article;
GRANT SELECT, INSERT, UPDATE, DELETE 
   on dbo.Dev_Article TO Developer_Group;

In Listing 3, the synonym dbo.Dev_Article was created to point to the based table named dbo.Article. Once this synonym is created the Developer_Group was granted SELECT, INSERT, UPDATE, or DELETE permissions on the new dbo.Dev_Article synonym. By doing this, the programmers in the Developer_group will be able to browse and update the actual table, dbo.Article using the synonym dbo.Dev_Article. Once the data issues are resolved the synonym can be dropped.

Creating a synonym in another database

For this example, suppose there is a company that wants to create a mirrored database in a development environment so that parallel development efforts can take place. By parallel development, I mean one group of developers can work and write TSQL code in one database, while another group of developers can do development work in a different database. The only problem is the company DBA says there isn’t enough disk space to copy all the tables from the existing development database (CurrentDB) to the new mirrored development database (NewDB). In this situation, the DBA decides he can establish the new mirrored database by creating synonyms in the NewDB database to point to each of the big tables that can’t be mirrored due to lack of disk space. The code in Listing 4 shows how to create a synonym in the NewDB databases that points to one of those big tables that can’t be mirrored.

Listing 4. Creating a synonym that references table in another database

USE NewDB;
Go
CREATE SYNONYM dbo.BigTable
   FOR [CurrentDB].dbo.BigTable;
GRANT SELECT, INSERT, UPDATE, DELETE on dbo.BigTable TO Developer_Group2;
GO

After creating this synonym in the NewDB database, all the developers in Developer_Group2 will be able to write SELECT, INSERT, UPDATE and DELETE statements against the dbo.BigTable as if it resides in the NewDB database. Keep in mind when the developers executed code against the dbo.BigTable in the NewDB database the commands will actually be run against the dbo.BigTable in the CurrentDB database.

This also comes into play during a continuous integration build process. Referenced databases may not be in place or have different names during the process and keep the build from being successful.

Creating a synonym to reference objects on another server

There are times when an application might need to run some code on one server but reference a table on another server. For discussion, assume a server has limited storage space. Therefore, periodically an archive process is run that moves historical information to a different server, that I will call the archive server. If the application needs to select some data from the archive server, they could use a four-part name and a linked server to reference those objects on the archived server, or they could define a synonym.

The four-part names will work, but they are long names to type. By creating a synonym for these long four-part names, it makes the coding easier and makes these remote server references appear like they are local. The code in Listing 5 shows how to create a synonym that references the four-part table name that resides on the archive server.

Listing 5. Defining a synonym for a table on another server

CREATE SYNONYM ClientHistory
   FOR ArchivedProd.MyApp.dbo.ClientHistory;
GO

By creating this synonym, an application can now use the name ClientHistory to reference the client history information that resides on the archived server instead of using the long four-part name ArchivedProd.MyApp.dbo.ClientHistory.

Synonyms cannot reference other synonyms

A synonym cannot reference another synonym. To demonstrate, this I will run the code in Listing 6 to create a new synonym in the NewDB database that references the dbo.BigTable synonym that I created using Listing 4.

Listing 6. Creating a synonym that references another synonym

USE NewDB;
GO
CREATE SYNONYM dbo.Second_BigTable
   FOR dbo.BigTable;
GO

When I execute the code in Listing 6, it runs without error and creates the new synonym named dbo.Second_BigTable. But if I try to execute the SELECT statement in Listing 7, I get the error in Figure 1.

Listing 7. SELECT statement

SELECT * FROM dbo.Second_BigTable;

Figure 1. Error when trying to use a synonym that references another synonym

The reason I got no error when I created the synonym, but I got an error when I executed the SELECT statement is because the code for a synonym is not validated at creation time, but it is validated at run time. This feature of delayed validation is a good thing. Delayed validation allows a DBA to create a synonym in advance of when the actual base object will be created.

Changing a synonym definition

If a base table for a synonym is renamed or moved, the synonym definition will need to be altered to reflect the change. The only problem is that there is no ALTER SYNONYM statement to help support renaming or moving of a based object. To change an existing synonym, it will need to be dropped and then recreated, using the new name and location of the base table.

Having to drop and recreate a synonym can cause a problem if any permissions have been granted directly to a synonym. When the synonym is dropped the permissions associated with the synonym are also dropped. Therefore, if a synonym needs to be dropped and recreated for some reason, then a DBA will need to make sure permissions placed on the existing synonym are reviewed and documented prior to it being dropped. By doing this, they will be able to recreate the GRANT statements to reapply the permissions once the synonym is recreated.

Identifying the synonyms in a database

When working with a new database, it is always wise to review it to see if any synonyms have been defined. Without this review, you might assume a synonym reference is just a reference to a base object and thus cause confusion. There are two different methods for reviewing the synonyms defined in a database.

The first one is to use SSMS. To find the synonym definitions for a database using SSMS, follow these steps.

1. Open up SSMS
2. Expand the Databases folder
3. Expand the database that is being reviewed
4. Expand the Synonym item

Figure 2 shows the synonyms added to the NewDB database.

Figure 2. The synonyms in the NewDB database

The other method to display synonyms in a database is to write a query against the sys.synonyms catalog view in the context of the database where you want to explore for synonyms. An example of how to do that can be found in Listing 8.

Listing 8. Displaying all Synonyms in a Database using TSQL

SELECT * FROM sys.synonyms;

Precautions when using synonyms

If you plan to use synonyms, then you should be aware of a few issues with using them. Here is a list of some of those things that might trip you up when using a synonym.

Cannot retain permissions on a synonym when they are dropped.

Using synonyms causes confusion when someone doesn’t realize that synonyms are being used.

When creating synonyms that referencing objects on other servers for testing and debugging purposes you need to make sure you don’t accidentally update the data on those other servers unless of course, that is what you intend to do.

Depending on the version of SQL Server being used, IntelliSense may not pickup that a name is a synonym.

You cannot reference a synonym across a linked server. If this is tried, you will get an error like the one shown in Figure 3.

Figure 3. Error when trying to reference synonym across a linked server

SQL Server Synonyms

Synonyms are a great way to simplify your code for a number of different reasons, like shortening up those long object names, or references tables in other databases, or instances of SQL Server. Synonyms help in the coordination of renaming base objects or changing their location over time. By creating a synonym, the reference to the synonym might look like a local table, when in fact it resides in a different database or even a different instance of SQL Server. Even with the confusion that can arise when using synonyms, the trade-off of using one might be well worth it for several situations. Next time you have to rename an object or copy an environment, you might want to consider whether a synonym will make this effort easier and more seamless.

 

The post SQL Server Synonyms appeared first on Simple Talk.

from Simple Talk https://ift.tt/3mF0FXn
via