One of my friend and extremely smart DBA Jonathan from Las Vegas has pointed out nice little enhancement in T-SQL. I was very pleased when I learned about SYNONYM feature in SQL Server 2005.
DBA have been referencing database objects in four part names. SQL Server 2005 introduces the concept of a synonym. A synonyms is a single-part name which can replace multi part name in SQL Statement. Use of synonyms cuts down typing long multi part server name and can replace it with one synonyms. It also provides an abstractions layer which will protect SQL statement using synonyms from changes in underlying objects (tables etc).
Create Synonyms :
USE AdventureWorks;
GO
CREATE SYNONYM MyLocation
FOR AdventureWorks.Production.Location;
GO
Use Synonyms :
USE AdventureWorks;
GO
SELECT TOP 5 *
FROM MyLocation;
GO
Drop Synonyms :
USE AdventureWorks;
GO
DROP SYNONYM MyLocation;
GO
Synonyms can be created on only following objects.
- Assembly (CLR) Stored Procedure
- Assembly (CLR) Table-valued Function
- Assembly (CLR) Scalar Function
- Assembly Aggregate (CLR) Aggregate Functions
- Replication-filter-procedure
- Extended Stored Procedure
- SQL Scalar Function
- SQL Table-valued Function
- SQL Inline-table-valued Function
- SQL Stored Procedure
- View
- Table (User-defined)
Additionally SYNONYMS can be used only to change data of object not the schema of the object. SYNONYMS can be used with only SELECT, UPDATE, INSERT, DELETE, EXECUTE commands.
Following is image demonstrates use of SYNONYMS.
An example of the usefulness of this might be if you had a stored procedure on a Users database that needed to access a Clients table on another production server. Assuming you created the stored procedure in the database Users, you might want to set up a synonym such as the following:
USE Users;
GO
CREATE SYNONYM Clients
FOR Offsite01.Production.dbo.Clients;
GO
Now when writing the stored procedure instead of having to write out that entire alias every time you accessed the table you can just use the alias Clients. Furthermore, if you ever change the location or the name of the production database location all you need to do is modify one synonym instead of having to modify all of the stored procedures which reference the old server.
Reference : Pinal Dave (https://blog.sqlauthority.com), Jonathan (Las Vegas), BOL – SYNONYMS
33 Comments. Leave new
Hi Pinal Dave,
Thanks for your information.How to use Synonyms in same database. For ex:i am using Thrid Party tool. Data will store into SQL.I create a Table in same database (like Employee Master).I want to store a information in my New Table .Is it possible in SYNONYMS Concept.Please clear it.
How would you create a disaster recovery for 2 databases on different servers that use synonyms between each other. So we have serverA.databaseA.dbo.synonym1 pointing to serverB.databaseB.dbo.table1
but we have mirrored servers: serverA.databaseA mirrored to disasterRecoverA.databaseA and serverB.databaseB mirrored to disasterRecoverB.databaseB using sql server 2008R
Hi Pinal,
This is a great article about synonym, but i’m curious,i found one of the comments (from Mike M) about connecting two different database server,and create one queries that use table on both server. can you explain or give me a simple step by step or example queries with condition you have two database server, Server A and Server B, and then you want to display output inner join tableA1 in database AA on Server A with tableB1 in Database BB on Server B, and using synonym on it,can we do that and if we can, please tell me how. i appreciate your help for helping me.
thank you.
Hi Pinal,
Question about synonym…
Why MSSQL accept this, but we can’t use this ???
CREATE SYNONYM [dbo].[syn_Server] FOR [ServerRemote]
CREATE SYNONYM [dbo].[syn_Db] FOR [AdventureWorks]
CREATE SYNONYM [dbo].[syn_Employee] FOR [syn_Server] .[syn_Db].dbo.Employee
after that, I read the synonym:
select name, base_object_name
from sys.synonyms
order by name
Result:
name base_object_name
syn_Server [ServerRemote]
syn_Db [AdventureWorks]
syn_svDM_Schema [dbo]
syn_Employee [syn_Server] .[syn_Db].[syn_svDM_Schema].Employee
When I try to use synonym as:
select * from dbo.syn_Employee
I got:
Msg 7202, Level 11, State 2, Line 1
Could not find server ‘ServerRemote’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
The question is: Why MSSQL accept the syntax when you can not use it?
Someone have a idea?
Thanks :-)
My understanding is that synonyms cannot be chained (a synonym referencing another synonym). I also understand that the creation of synonyms is not checked for validity at creation time, only at run time, which is why it allowed you to execute the third command even though it is an invalid synonym.
(I know this question is over a year old, but maybe posterity needs an answer.)
sorry not: Could not find server ‘ServerRemote’… but Could not find server ‘syn_Server’
Sorry!
A big limitation of synonyms is that queries that reference a table synonym in SQL Server 2008 will only use the clustering index. Nonclustering indexes appear to be invisible to the query optimizer. If you would like a quick demo over GTM or JOIN ME, let me know. For me, this is a good reason to not use synonyms for large tables and complex queries. Index hints might be a solution but there are too many procedures to change to make this viable.
Hi Pinal,
Nice Article. I have a small question about this . Is it possible to create a Synonym for the Entire database rather than DB objects ? This will help me to do cross DB reference by using a single Synonym . Something like this
USE AdventureWorks;
GO
CREATE SYNONYM MyLocation
FOR AdventureWorks
SELECT TOP 5 * [MyLocation]..Table1
SELECT TOP 5 * [MyLocation]..Table2
i m searching defination of synonymes but there is no exact defination of this what exact is