What types of objects can synonyms reference in SQL

A synonym is a database object that provides an alternate name (alias) for another object, called the base object. Internally SQL Server stores only the object name string and it is not schema binding and not validation at creation time. Name resolution happens at runtime. It does NOT copy data or logic.

Read: Deferred name resolution in SQL server

https://bageshkumarbagi-msbi.blogspot.com/2016/06/deferred-name-resolution-in-sql-server.html

Synonyms in SQL server

https://bageshkumarbagi-msbi.blogspot.com/2016/11/synonyms-in-sql-server.html

synonyms work on below objects.

See the example

Tables:

For the demo here we are creating a table and inserting few records after that creating the synonyms.

CREATE TABLE dbo.Products (     ProductID INT PRIMARY KEY,     Name VARCHAR(50) );   INSERT INTO dbo.Products VALUES (1,'Laptop'),(2,'Mobile');   CREATE SYNONYM syn_Products FOR dbo.Products; GO

Query the synonym

View

Creating a view

CREATE VIEW dbo.vwProducts AS SELECT * FROM dbo.Products; GO CREATE SYNONYM syn_vwProducts FOR dbo.vwProducts;

 

See the data

 

SP:

Creating user defined SP

CREATE PROC dbo.usp_GetProducts AS SELECT * FROM dbo.Products; GO CREATE SYNONYM syn_GetProducts FOR dbo.usp_GetProducts;

See the result

Function

Creating Function

CREATE FUNCTION dbo.fn_CountProducts() RETURNS INT AS BEGIN     RETURN (SELECT COUNT(*) FROM dbo.Products); END; GO CREATE SYNONYM syn_CountProducts FOR dbo.fn_CountProducts;

See the result

Sequence

Creating a Sequence

CREATE SEQUENCE dbo.ProductSeq START WITH 100 INCREMENT BY 1; GO CREATE SYNONYM syn_ProductSeq FOR dbo.ProductSeq;

See the result.

SELECT NEXT VALUE FOR syn_ProductSeq;

Ooo! It is failed. SQL Server does NOT support synonyms for sequence objects directly. Indirectly it will support.

Here creating a SP which call the Sequence.

CREATE PROCEDURE dbo.GetNextProductID AS BEGIN     SELECT NEXT VALUE FOR dbo.ProductSeq AS NextProductID; END;

Now creating a synonym on the view

CREATE SYNONYM dbo. GetNextProductIDSyn FOR dbo. GetNextProductID;

Now we can query the sequence.

SQL Server does not allow synonyms directly on sequence objects. However, sequences can be exposed via wrapper views or stored procedures, and synonyms can reference those wrappers to provide indirection across schemas or databases.

creating synonyms on temp table

we cannot create synonyms

see the example

CREATE TABLE #Products (     ProductID INT PRIMARY KEY,     Name VARCHAR(50) );   INSERT INTO #Products VALUES (1,'Laptop'),(2,'Mobile');   CREATE SYNONYM syn_ProductsTemp FOR #Products; GO

 

Synonyms created successfully but it cannot run

Running synonym

In the same scope we can use it but if you want to use in other session then we can’t use.

Because the base object is temporary.