mssqltips logo

How and why should I use SQL Server 2005 synonyms?

By:   |   Updated: 2006-09-18   |   Comments (5)   |   Related: More > Synonyms

Problem
Changing the name of an table once an application has been deployed has traditionally been a difficult task in SQL Server 2000.  The typical solution in SQL Server 2000 for referencing a different object was to use a View.  A second option was to use the sp_rename system stored procedure to rename objects as needed.  Unfortunately, this limits the capabilities of referencing other objects.  The need to be able to reference another object can become critical during system migrations, application testing, data corruption, etc.  Does SQL Server 2005 handle this in a more elegant manner and support more than just tables? 

Solution
SQL Server 2005 has introduced synonyms which enables the reference of another object (View, Table, Stored Procedure or Function) potentially on a different server, database or schema in your environment.  In short, this means that the original object that is referenced in all of your code is really using a completely different underlying object, but no coding changes are necessary.  Think of this as an alias as a means to simplify migrations and application testing without the need to make any dependent coding changes.

How do I create a synonym via SQL Server 2005 Management Studio?

ID Directions Screen Shot
1 Launch GUI - To access the synonym interface navigate to SQL Server 2005 Management Studio | Server Name | Databases | Database Name | Synonyms | Right click on the Synonyms folder | New Synonym... Not applicable
2 General Tab - Complete the Synonym name which should be a new name in the schema followed by the information for the aliased object
3 Permission Tab - Click the 'Add' button to grant rights to the synonym for specific database users
4 Extended Properties Tab - Add any extended properties for documentation purposes

For more information visit - Using Extended Properties on Database Objects

How do I create a synonym via T-SQL?

USE [UserDefinedDatabase]
GO
CREATE SYNONYM [dbo].[Customer]
FOR
[JTKNotebook].[JTKTest].[dbo].[Customer]
GO
EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'This is a sample synonym referencing the JTKTest database' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'SYNONYM',@level1name=N'Customer'

How can I verify the statements issued against the synonym are affecting the base object?

Issue an INSERT statement against the synonym:

INSERT INTO [BaltSSUG].[dbo].[Customer]
([CustID] ,[CustName] ,[CustAddress] ,[CustCity] ,[CustState] ,[CustZipCode])
VALUES (99 ,'DBA Dave' ,'123 Main Street' ,'Warren' ,'OH' ,12345)

Issue a SELECT statement on the synonym:

SELECT *
FROM [BaltSSUG].[dbo].[Customer]

1 AAA 123 Main Washington DC 11111
2 BBB 456 Main New York NY 22222
3 CCC 789 Main St. Louis MO 33333
4 DDD 123 Maple Denver CO 44444
5 EEE 456 Maple San Fran CA 55555
99 DBA Dave 123 Main Street Warren OH 12345

Can I still issue statements against the base object?

Issue an INSERT statement against the base table:

INSERT INTO [JTKTest].[dbo].[Customer]
([CustID] ,[CustName] ,[CustAddress] ,[CustCity] ,[CustState] ,[CustZipCode])
VALUES (100 ,'Developer Donna' ,'456 Main Street' ,'Chicago' ,'IL' ,67890)

Issue a SELECT statement on the base table:

SELECT *
FROM [JTKTest].[dbo].[Customer]

1 AAA 123 Main Washington DC 11111
2 BBB 456 Main New York NY 22222
3 CCC 789 Main St. Louis MO 33333
4 DDD 123 Maple Denver CO 44444
5 EEE 456 Maple San Fran CA 55555
99 DBA Dave 123 Main Street Warren OH 12345
100 Developer Donna 456 Main Street Chicago IL 67890

Next Steps

  • As you begin to migrate to SQL Server 2005 consider synonyms as a means to alias objects in your environment without the need to change the dependent code.
  • The next time you need to make significant code changes due to application testing or a system migration and you are concerned about the level of effort to support the changes, first test the synonym functionality to determine if this will streamline the process and save time.
  • Do not limit yourself to uses for the synonyms, consider other applications such as in data warehousing when managing fact tables.
  • Check out our recent testing related tips:


Last Updated: 2006-09-18


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Sunday, October 27, 2013 - 7:41:25 AM - Anthony Dass Back To Top

Dear Jeremy Kadlec

I have created synonym of the base database and used in another database however when I call the procedure or tables from my front end code I get exception as "Object does not exits"

 

No clue about this error I try to execute the same from query analyser and it does return me the output.

 

Please suggest what would be wrong here? is it related to rights ? please assist

 


Friday, January 11, 2013 - 6:08:00 PM - Jeremy Kadlec Back To Top

Brien,

Thank you for the post.  In my circumstances, I have used synonyms more for migration simplicity than performance.  Views could have been an alternative, but I personally selected synonyms based on the project needs.  Let me see if I can write a tip about some performance tests to see which option performs better.

Thank you,
Jeremy Kadlec
Community Co-Leader


Thursday, January 10, 2013 - 12:49:52 AM - Brien King Back To Top

I see lots of posts about how great Synonyms are, but I have yet to see one that tells me WHY I should use it over a view.  Are there performance bennifits? 

I like the idea, but you can achive the same results with a View.


Thursday, November 29, 2012 - 4:24:58 PM - Jeremy Kadlec Back To Top

Doug,

Thank you for the feedback.  I need to update this tip to the SQL Server 2012 platform.

Let me get that done and re-post this tip.

Thank you,
Jeremy Kadlec


Thursday, November 29, 2012 - 2:59:21 PM - Doug Back To Top

Maybe I'm missing something here, and I am not sure I'll get a response since this article is 6 years old, but if you have:

CREATE SYNONYM [dbo].[Customer]
FOR
[JTKNotebook].[JTKTest].[dbo].[Customer]

and use

INSERT INTO [JTKTest].[dbo].[Customer]

are you not trying to insert into [JTKTest].[JTKNotebook].[JTKTest].[dbo].[Customer], which is not a valid object name?



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools