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 (http://blog.SQLAuthority.com), Jonathan (Las Vegas), BOL – SYNONYMS

About these ads

34 thoughts on “SQL SERVER – 2005 – Introduction and Explanation to SYNONYM – Helpful T-SQL Feature for Developer

  1. Hi Pinal,
    I am working on SQL Server 2005 from last 2 years, mainly on SSRS and Database as Developer.Today I got this site while searching answer for some question, this is great site for study.

    Thanks for your effort….!

    Vishal.

  2. I really like the SQL Synonyms but not being able to use the SET IDENTITY_INSERT is a real show-stopper. Do you know any way to insert into an identity column using a synonym?

  3. Dear sir,
    It is excellent site 4 learning SQL Server.I have got problem
    connecting with C# 2008 to SQL Server 2005.plz advice me.

  4. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 7 Journey to SQL Authority with Pinal Dave

  5. Pinal,

    I am trying to create a synonym for first 3 part of the object (without object name itself).

    For example, I have a linked server named “Legacy”. What I need is a “MyLegacy” synonym or alias for “Legacy.MyDatabase.dbo”, so I can use something like MyLegacy.Table1, MyLegacy.View2 etc.

    I don’t want to provide an object name while creating synonyms (otherwise I will end up with huundreds of synonyms, one for each object).

    Is there any way to do this?

    Thanks.

    • There is a way of doing this but its not what you would think. I had the same need and this is what I came up with.

      Setup 1 synonym for 1 DB object. You wil not be using the synonym at all. What you will be doing is pulling the Server.Database.Schema from the synonym, then use the Server.Database.Schema in String SQL.

      Below is the code I am using to extract the Server.Database.Schema from the synonym. You could use the Server.Database.Schema from the synonym to dynamically build a Synonym as needed then drop when done if you do not want to use string sql.

      Declare @ServerToUpdt varchar(255)
      ,@DBToUpdt varchar(255)
      ,@Schema char(3)

      Select @ServerToUpdt = Replace(Replace(Left(base_object_name,charindex(‘.’,base_object_name)-1),'[‘,”),’]’,”)
      ,@DBToUpdt = Replace(Replace(Substring(base_object_name,
      charindex(‘.’,base_object_name)+1,
      charindex(‘.’,base_object_name,charindex(‘.’,base_object_name)+1) – charindex(‘.’,base_object_name) – 1)
      ,'[‘,”),’]’,”)
      ,@Schema = Replace(Replace(Substring(base_object_name,
      charindex(‘.’,base_object_name,charindex(‘.’,base_object_name)+1)+1,
      charindex(‘.’,base_object_name,charindex(‘.’,base_object_name,charindex(‘.’,base_object_name)+1)+1) – charindex(‘.’,base_object_name,charindex(‘.’,base_object_name)+1) – 1)
      ,'[‘,”),’]’,”)
      From sys.synonyms Where name = ‘icc_SaveRptParamsCutAudit’

      Print @ServerToUpdt
      Print @DBToUpdt
      Print @Schema

  6. Hi Pinal,
    I created synonym for a stored procedure with 3 input parameters.

    The synonym works great as long as the Server defined in the synonym is different from the server the synonyn is being executed.

    When the synonym is executed on the same server as defined in the synonym I get the following error:
    Msg 201, Level 16, State 4, Procedure @Param1, Line 1186
    Procedure or function ‘MyProc’ expects parameter ‘@Param1′, which was not supplied.

    Can I not use a synonym with a stored procedure object on the same server as defined in the synonym?
    Is there a system setting I need to set?
    Do I need to clap my hands and spin 3 times?

    For example:
    Create the synonym on ProdServer.ProdDB
    USE ProdDB
    CREATE SYNONYM MySynProc
    FOR ProdServer.ProdDB.MyProc;

    Using the Synonym on a different Server works great
    Log onto server ReportServer
    USE ProdDB_Rpt
    Exec MySynProc @Param1=’A’, @Param2=’1′, @Param3=’Gold’

    Using the Synonym on the Server defined in the Synonym generates an error
    Log onto server ProdServer
    USE ProdDB_Copy
    Exec MySynProc @Param1=’A’, @Param2=’1′, @Param3=’Gold’

    Using the Synonym on the Server & database defined in the Synonym generates an error
    Log onto server ProdServer
    USE ProdDB
    Exec MySynProc @Param1=’A’, @Param2=’1′, @Param3=’Gold’

    • I realize this is an old post but since there is no solution posted yet, here it is:

      If you are running a stored procedure as a synonym on the same server, you must not specify the server name when creating the synonym.

  7. Hi Pinal,
    Sorry, I should have added that in the 3 examples from my previous posting I am executing the synonym from with in a stored procedure.

    The Exec MySynProc @Param1=’A’, @Param2=’1′, @Param3=’Gold’ is on line 1186 of the stored procedure.

    This stored procedure is used for a report in reporting services. The Synonym is a stored procedure used to update the application audit log table. I am updating the audit log from the report stored procedure because we are also saving the selected report parameter values in the audit log table.

  8. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 16 of 31 Journey to SQLAuthority

  9. HI……..
    this is harish i am having 54% in b.tech and i have joined in a company and i doing sql server 2005. i would like to know how i can step into mnc’s with this percentage and my domain………..

  10. Hi Pinal, Whenever I search for any query or problem SQL related it always pops up yours name first, I find your articles and solutions correct and to the point. Coming to the point, I want to know what happens under the hood when we create a Synonym in SQL. As for Oracle I read it somewhere that it creates a pointer to that table. So whats the scenario in SQL? I have one more query regarding it, if in SQL also it creates a pointer then what happens when we create a Synonym for the table from a database to the linked server in SQL.?

  11. Hi Pinal,

    I have requirement where i want to rename the tables which has depdency with many objects, is there any simple solution to rename tables having dependencies wtih that table?

  12. Here is an update to MIKE M. script using the PARSENAME
    Select base_object_NAME
    , PARSENAME(base_object_name, 4) AS ‘Server’
    , PARSENAME(base_object_name, 3) AS ‘Database’
    , PARSENAME(base_object_name, 2) AS ‘Schema’
    , PARSENAME(base_object_name, 1) AS ‘Object’
    From sys.synonyms Where name = ”

  13. 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.

  14. 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

  15. Pingback: SQL SERVER – Weekly Series – Memory Lane – #011 « SQL Server Journey with SQL Authority

  16. 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.

  17. 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.)

  18. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s