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 tableThis 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.
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
No comments:
Post a Comment
Please Use Good Leanguage