Benefits and limitations of using synonyms in SQL Server 2005

By:   |   Comments (18)   |   Related: > Synonyms


Problem

On my new job I faced a situation where archived tables were created in the production database and now there was a requirement to move them as the database was growing. These archival tables were being used by several jobs and also in the application code. Moving them was demanding and also a very complicated process. I wanted to find a way to minimize the amount of work that the development team had to do, since their time was limited too.  Based on the needs and the limited time I wasn't sure what was the best option. 

Solution

Fortunately I found a feature in SQL Server 2005 which solved my problems and provided further optimization and facilities. The feature is SYNONYMs in SQL Server 2005. A SYNONYM is new to SQL Server 2005. It is a way to give an alias to an already existing or potential new object. It is just a pointer or reference, so it is not considered to be an object. 

In order to create a synonym, you need to have CREATE SYNONYM permissions. If you are a sysadmin or db_owner you will have these privileges or you can grant this permission to other users. Also, you create a synonym that crosses databases you will need permissions for the other database as well..

Here is an example to create the SYNONYM in a database that references another object in the database.

USE AdventureWorks
GO

CREATE SYNONYM MySyn FOR Production.Location
GO

To check that this works you can issue a query such as below that uses the new SYNONYM.

SELECT * FROM MySyn

Here is an example to create the SYNONYM in one database that references an object in another database.

USE master
GO

CREATE SYNONYM dbo.MySyn FOR AdventureWorks.Production.Location
GO

To get the meta data for all synonyms use the following command

SELECT * FROM sysobjects
WHERE xtype = 'SN'
ORDER BY NAME

And to drop the synonym use the following command

USE AdventureWorks;
GO

DROP SYNONYM MySyn
GO

SYNONYM's can be very useful and can be created for

  • Tables
  • Views
  • Assembly Stored Procedures, Table Valued Functions, Aggregations
  • SQL Scalar Functions
  • SQL Stored Procedures
  • SQL Table Valued Functions
  • SQL Inline-Table-Valued Functions
  • Local and Global Temporary Tables
  • Replication-filter-procedures
  • Extended Stored Procedures

Benefits

  • SYNONYMs provide a layer of abstraction over the referenced object
  • Allow changes to complicated (multi part) and lengthy names with a simplified alias as a same server resident object.
  • Provides flexibility for changing the location of objects without changing existing code.
  • SYNONYMs can be created in the same database to provide backward compatibility for older applications in case of drop or rename of objects.
  • SYNONYMs can be useful if you give the front-end query tools like spreadsheets and Access linked tables direct links in to the tables.

Limitations

  • SYNONYMs are loosely bound to the referenced objects. So you can delete a SYNONYM without getting any warning that it is being referenced by any other database object.
  • Chaining is not allowed. It means that you can not create SYNONYM of a SYNONYM.
  • Obviously consumes possible object names, as you can not create a table with the same name of a synonym
  • The object for which the SYNONYM is being created is checked at run time. It is not checked at creation time. So this means that if you make any related error e.g. spelling error, the synonym will created, but you will get an error while accessing the object.
  • SYNONYM can not be referenced in a DDL statement
Next Steps

Now that you know that you can create a SYNONYM to reference objects in an existing database or another database look for ways that a SYNONYM may be used.  Sometimes a simple alias may be the best choice instead of a lot of re-work to move or rename objects.

Take a look at this other tip about SYNONYMs: How and why should I use SQL Server 2005 synonyms?



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, April 2, 2018 - 11:46:45 AM - Roger Jones Back To Top (75579)

This code does not work with a Synonym.

 Select @FileID = IDENT_CURRENT(‘ImportFiles’)

 This code does:

 Select @FileID = SCOPE_IDENTITY() from ImportFiles

Friday, June 3, 2016 - 1:45:37 AM - sujatha Back To Top (41603)

 I have below query:

 

 

 

SELECT *

 

FROM

 

Database1.dbo.TABLE1

 

JOIN Database2.dbo.TABLE1

 

 

After some time If I want to implement above script to Database3, Now as a release manager it is difficult(not correct to edit the script) to replace Database1 with DataBase3.

 

I don’t need dynamic SQL.

whethwe we can use synonym for that?

 

Monday, September 14, 2015 - 3:21:50 AM - Atif Shehzad Back To Top (38670)

Hi Luiscarlo, What is exactly the error message that you get?

 

Friday, September 11, 2015 - 2:16:55 PM - Luiscarlo Back To Top (38655)

i have this scenario:

create synonim for otherDB;

select * from otherDB.dbo.AnyTable; --> this gives me error. is there a way to fix this?

Tuesday, September 25, 2012 - 7:45:14 AM - Jyothi Back To Top (19672)

Thank you

 

Tuesday, September 25, 2012 - 2:30:10 AM - Atif Shehzad Back To Top (19669)

@Josh. You are right synonym is not accessable in data profiling task however it is accessable in OLEDB connection manager editor.

 

Tuesday, September 25, 2012 - 12:51:55 AM - Atif Shehzad Back To Top (19668)

@Jyothi. DBLinks related to inter database objects and their access. While Synonyms are used within or accross the databases with simple purpose that is to use different  and simple names for database objects. It may be used to hide the orignal object names or to simplyfy the long object names.

Monday, September 24, 2012 - 5:49:40 PM - Josh Back To Top (19663)

One more limitation, which I just ran into this morning... The SSIS Data Profiling task cannot see synonyms.  It only presents tables and views in the object list.

Monday, September 24, 2012 - 8:41:08 AM - Jyothi Back To Top (19654)

Can you please tell me what is the difference between synonym and dblinks ? both are used to access tables from other databases except the thing that with synonyms we need to grant privileges ??

Wednesday, May 9, 2012 - 2:04:00 AM - jaspreet Back To Top (17349)

i dont understand to this theory

Friday, April 22, 2011 - 1:17:35 AM - Atif Shehzad Back To Top (13665)

Connecting the server in SSMS would not be sufficient. You have to create linked server objects to access and then create synonyms for any object in linked server. Also have a look at this tip http://www.mssqltips.com/tip.asp?tip=1820

Thanks

Thursday, April 21, 2011 - 11:30:35 AM - Joanie Back To Top (13655)

Is there a way to select a column from, and therefore create a synonym for, a table in a database on another server if both servers are connected in SSMS (SQL Server 2005)?  Thanks.

Wednesday, October 6, 2010 - 2:34:36 AM - Atif Shehzad Back To Top (10225)
@Andy, you are absolutely right. So along with limitation of being accessed in DDL statements, synonyms also not work in truncate statement.

 

Thanks

Atif Shehzad

http://blog.dbdigger.com

Tuesday, October 5, 2010 - 7:05:53 AM - Andy Novick Back To Top (10222)
An additional limitation is that you can't use TRUNCATE TABLE on a synonym.  If you try you'll get the message:;

Msg 4708, Level 16, State 2, Line 1
Could not truncate object 'y' because it is not a table.

So any code that does truncation breaks.

HTH

Wednesday, September 10, 2008 - 10:07:28 PM - @tif Back To Top (1779)

 TimothyAWiseman, thanks for your appreciation. Looking at benifits of synonyms we may say that their usage is far more efficient and benificial than convential usage of views for this purpose.

Wednesday, September 10, 2008 - 10:04:14 PM - @tif Back To Top (1778)

If we use this option frequently, then keping in mind the facts that

  • you can delete a SYNONYM without getting any warning that it is being referenced by any other database object
  • the object for which the SYNONYM is being created is checked at run time. It is not checked at creation time.

It makes it imperitive that we should plan and monitor synonyms bit more than other objects/references in SQL Server.

And ahains, thanks for sharing your experience.

Wednesday, September 10, 2008 - 2:56:23 PM - TimothyAWiseman Back To Top (1776)

 Good tip.

 

In SQL Server 2000 you can achieve something similar for tables and views by creating a view which simply contains

select

*

from

TableName

 

But synonyms are superior when they are an option.

 

Wednesday, September 10, 2008 - 7:51:37 AM - ahains Back To Top (1775)
I like to use synonyms for all of my cross database dependencies, it greatly simplifies when you need to move to a new version of a given database. I put some example code up at _this blog post_.














get free sql tips
agree to terms