In SQL Server we can create synonyms, which allow us to provide an alternative name for a database object. Once created, we can reference the synonym instead of the object itself.
One benefit of synonyms is that they can help shorten the code we need to use when referencing an object. For example, we can type the synonym name instead of the full qualified path when referencing objects in another schema, database, or server.
We can also use synonyms to protect client applications from changes made to the name or location of the base object.
Example
Suppose we have a table called Customers in the Sales schema. We can normally access this table at Sales.Customers. However, we can also create a synonym for this table so that it can be accessed as Customers.
First, here’s what happens when I query that table without specifying the schema:
SELECT
CustomerId,
CustomerName
FROM Customers;Result:
Msg 208, Level 16, State 1, Line 1 Invalid object name 'Customers'.
This happened because I didn’t qualify the path with the schema name. When we don’t do that in a query, SQL Server first looks at our default schema, then it looks in the dbo schema. In my case, dbo is my default schema.
Let’s now create a synonym for the full path Sales.Customers, so that we can reference the table without qualifying it with the schema name:
CREATE SYNONYM Customers
FOR Sales.Customers;Result:
Commands completed successfully.
Now we can run our query again, this time without error:
SELECT
CustomerId,
CustomerName
FROM Customers;Result:
CustomerId CustomerName ---------- ------------------ 1001 Palm Pantry 1002 Tall Poppy 1003 Crazy Critters 1004 Oops Media 1005 Strange Names Inc. 5 row(s) returned
Synonym to Another Database or Server
We can create synonyms to objects located in other databases or on other servers. To do this, simply use the fully qualified name to the object on that database/server:
CREATE SYNONYM Cats
FOR PetHotel.dbo.Cats;Result:
Commands completed successfully.
That created a synonym to the dbo.Cats table in the PetHotel database. Now we can query that table without having to qualify it with the name of the database:
SELECT * FROM Cats;If we hadn’t created the synonym, we would have had to qualify the table with the schema and database name, like this:
SELECT * FROM PetHotel.dbo.Cats;Creating Synonyms for Non-Existent Objects
It’s actually possible to create a synonym for an object that doesn’t yet exist. SQL Server doesn’t check that the base object exists when we create the synonym – it only checks for the existence of the base object at run time.
Therefore, we can do this:
CREATE SYNONYM MyObject
FOR NonExistentObject;Result:
Commands completed successfully.
In this case, NonExistentObject doesn’t actually exist, but that didn’t stop SQL Server from creating the synonym. However, we will get an error when we try to reference the synonym:
SELECT * FROM MyObject;Result:
Msg 5313, Level 16, State 1, Line 1 Synonym 'MyObject' refers to an invalid object.
As expected, we get an error that tells us that the synonym refers to an invalid object.
However, if we create the object we will no longer get the error:
CREATE VIEW NonExistentObject
AS
SELECT CustomerName FROM Customers;Now let’s try the query again:
SELECT * FROM MyObject;Result:
CustomerName ------------------ Palm Pantry Tall Poppy Crazy Critters Oops Media Strange Names Inc. 5 row(s) returned
In my case, my synonym refers to a view.
Valid Objects for Synonyms
We can create synonyms for the following objects:
- Assembly (CLR) stored procedure
- Assembly (CLR) scalar function
- Replication-filter-procedure
- SQL scalar function
- SQL inline-tabled-valued function
- View Assembly (CLR) table-valued function
- Assembly (CLR) aggregate functions
- SQL table-valued function
- SQL stored procedure
- Table, including local and global temporary tables (User-defined)
More Information
See the Microsoft documentation for more information about synonyms such as permissions, where they can be used, etc.