You are here:Home » synonyms » Synonyms in SQL Server

Synonyms in SQL Server

Before starting at depth, I just want to define Synonyms in SQL server. As per online,  it is an alternate name of any other database object and provide a layer of abstraction which refers to ad base object for local as well remote server. Which can be created for tables, stored procedures, views, linked server and for some others likes to list here. Let me give some examples as how it can be created and used,
-- Creating base table
CREATE TABLE dbo.results
(
 StudentId INT,
 Subject varchar(20),
 Scrore int
)

-- Creating a schema
CREATE SCHEMA VW
GO

-- Creating a view 
CREATE VIEW vw.results 
AS 
  SELECT StudentId,
   subject, 
         scrore 
  FROM   dbo.results 
GO 

-- Creating a Synonym for local object 
CREATE synonym dbo.snm_result 
 FOR demodb.vw.results 
GO 

-- Viewing data from Synonym 
SELECT * 
FROM   dbo.snm_result 
GO 

-- Creating a schema
CREATE SCHEMA lkd 
GO 

-- Creating a Synonym for remote object
CREATE synonym lkd.remotelinked 
 FOR RemoteServer1.demodb.dbo.Students 
GO 

-- Using in another script
SELECT rm.strundname, 
       r.subject,
    r.scrore 
FROM   vw.results r 
       INNER JOIN lkd.remotelinked rm 
               ON ( rm.studentid = r.studentid ) 
GO
This helps to use it in many places once it's created as we seen in the above example. You may have more idea of its usage. Your comments will be appreciated.