SQL SERVER – 2005 – Introduction and Explanation to SYNONYM – Helpful T-SQL Feature for Developer
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
Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant with over 22 years of hands-on experience. He holds a Masters of Science degree and numerous database certifications.
Pinal has authored 14 SQL Server database books and 99 Pluralsight courses. To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,800 database tech articles on his blog at https://blog.sqlauthority.com.
Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service. If you need help with any SQL Server Performance Tuning Issues, please feel free to reach out at pinal@sqlauthority.com.
Pinal is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer (CF-L2).
Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long-term multi-channel campaigns to drive leads for their sales pipeline.
Comprehensive Database Performance Health Check
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
SQL Server Performance Tuning Practical Workshop
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
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