{"id":28073,"date":"2022-12-20T12:37:03","date_gmt":"2022-12-20T02:37:03","guid":{"rendered":"https:\/\/database.guide\/?p=28073"},"modified":"2025-11-15T12:07:07","modified_gmt":"2025-11-15T02:07:07","slug":"how-to-create-a-synonym-in-sql-server-t-sql","status":"publish","type":"post","link":"https:\/\/database.guide\/how-to-create-a-synonym-in-sql-server-t-sql\/","title":{"rendered":"How to Create a Synonym in SQL Server (T-SQL)"},"content":{"rendered":"\n

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. <\/p>\n\n\n\n\n\n\n\n

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. <\/p>\n\n\n\n

We can also use synonyms to protect client applications from changes made to the name or location of the base object.<\/p>\n\n\n\n

Example<\/h2>\n\n\n\n

Suppose we have a table called Customers<\/code> in the Sales<\/code> schema. We can normally access this table at Sales.Customers<\/code>. However, we can also create a synonym for this table so that it can be accessed as Customers<\/code>.<\/p>\n\n\n\n

First, here’s what happens when I query that table without specifying the schema:<\/p>\n\n\n\n

SELECT \n    CustomerId,\n    CustomerName\nFROM Customers;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Msg 208, Level 16, State 1, Line 1\nInvalid object name 'Customers'.<\/pre>\n\n\n\n
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<\/code> schema. In my case, dbo<\/code> is my default schema.<\/p>\n\n\n\n
Let’s now create a synonym for the full path Sales.Customers<\/code>, so that we can reference the table without qualifying it with the schema name:<\/p>\n\n\n\n
CREATE SYNONYM Customers\nFOR Sales.Customers;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Commands completed successfully.<\/pre>\n\n\n\n
Now we can run our query again, this time without error:<\/p>\n\n\n\n
SELECT \n    CustomerId,\n    CustomerName\nFROM Customers;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
CustomerId  CustomerName      \n----------  ------------------\n1001        Palm Pantry       \n1002        Tall Poppy        \n1003        Crazy Critters    \n1004        Oops Media        \n1005        Strange Names Inc.\n\n5 row(s) returned<\/pre>\n\n\n\n
Synonym to Another Database or Server<\/h2>\n\n\n\n
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:<\/p>\n\n\n\n
CREATE SYNONYM Cats\nFOR PetHotel.dbo.Cats;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Commands completed successfully.<\/pre>\n\n\n\n
That created a synonym to the dbo.Cats<\/code> table in the PetHotel<\/code> database. Now we can query that table without having to qualify it with the name of the database:<\/p>\n\n\n\n
SELECT * FROM Cats;<\/code><\/pre>\n\n\n\n
If we hadn’t created the synonym, we would have had to qualify the table with the schema and database name, like this:<\/p>\n\n\n\n
SELECT * FROM PetHotel.dbo.Cats;<\/code><\/pre>\n\n\n\n
Creating Synonyms for Non-Existent Objects<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
Therefore, we can do this:<\/p>\n\n\n\n
CREATE SYNONYM MyObject\nFOR NonExistentObject;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Commands completed successfully.<\/pre>\n\n\n\n
In this case, NonExistentObject<\/code> 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:<\/p>\n\n\n\n
SELECT * FROM MyObject;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Msg 5313, Level 16, State 1, Line 1\nSynonym 'MyObject' refers to an invalid object.<\/pre>\n\n\n\n
As expected, we get an error that tells us that the synonym refers to an invalid object.<\/p>\n\n\n\n
However, if we create the object we will no longer get the error:<\/p>\n\n\n\n
CREATE VIEW NonExistentObject\nAS\nSELECT CustomerName FROM Customers;<\/code><\/pre>\n\n\n\n
Now let’s try the query again:<\/p>\n\n\n\n
SELECT * FROM MyObject;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
CustomerName      \n------------------\nPalm Pantry       \nTall Poppy        \nCrazy Critters    \nOops Media        \nStrange Names Inc.\n\n5 row(s) returned<\/pre>\n\n\n\n
In my case, my synonym refers to a view<\/a>.<\/p>\n\n\n\n
Valid Objects for Synonyms<\/h2>\n\n\n\n
We can create synonyms for the following objects:<\/p>\n\n\n\n