Синонимы sql server

Зачем бы вам понадобился синоним?

При определении синоним ссылается на объект в базе данных, который называется базовым объектом. Базовый объект может находиться в той же базе данных, в которой определен синоним, но может находиться и в другой базе данных на том же сервере или даже в другом экземпляре. Есть множество ситуаций, когда синоним может оказаться полезным. Вот некоторые из них:

• Когда требуется согласовать переименование объекта с течением времени, поскольку имеются сотни или тысячи ссылок в коде на объект, который переименовывается.
• Чтобы обеспечить слой абстракции для реального объекта базы.
• Когда приложение хочет сослаться на объект как будто он находится в текущей базе данных, в то время как на самом деле он находится в другой базе данных или экземпляре.
• Когда требуется обратная совместимость с унаследованным объектом.
• Когда необходимо обеспечить слой безопасности для защищаемого базового объекта.
• Когда необходимо переместить объект в другую базу данных или экземпляр без влияния на существующий код.
• Для упрощения стандартов именования для длинных или запутанных имен объектов.
• Для устранения проблем с перекрестными зависимостями баз данных и серверов в среде разработки, тестирования или контроля качества как построения части процесса непрерывной интеграции.

Синтаксис для создания синонимов

В документации Microsoft приводится следующий синтаксис.

Листинг 1. Синтаксис SYNONYM

-- синтаксис для SQL Server

CREATE SYNONYM [ schema_name_1. ] synonym_name FOR < object >    

< object > :: =  

{  

    [ server_name.[ database_name ] . [ schema_name_2 ]. object_name   

  | database_name . [ schema_name_2 ].| schema_name_2. ] object_name  

}

-- Синтаксис для Azure SQL Database

CREATE SYNONYM [ schema_name_1. ] synonym_name FOR < object >   

< object > :: =  

{  

    [database_name. [ schema_name_2 ].| schema_name_2. ] object_name  

}

Создать синоним просто. Он может ссылаться на объект, используя именование из одной, двух и трех частей, как для SQL Server, так и для Azure SQL Database. Но только SQL Server поддерживает имена из 4 частей для ссылок на базовые объекты, которые находятся в другом экземпляре SQL Server.

Синонимы могут создаваться для ссылок на объекты следующих типов:
Хранимые процедуры CLR
Скалярные функции CLR
Табличнозначные функции CLR
Агрегатные функции CLR
Процедура фильтров репликации
Скалярная функция SQL
Встроенная табличнозначная функция SQL
Табличнозначная функция SQL
Хранимая процедура SQL
Представление
Таблица (пользовательская, включая локальные и глобальные временные таблицы)

Создание, использование и управление синонимами

Для лучшего понимания я буду использовать примеры. Эти примеры покажут вам, как использовать синонимы в различных бизнес-ситуациях, а также как работать с синонимами.

Координация переименования объекта

После создания объекта базы данных множество приложений ссылаются на него в своем коде. Поэтому переименование объекта становится кошмаром, связанным с количество затраченных усилий и координационной работы, требуемой для изменения имени без сбоев в работе приложений. Если пропущено будет всего лишь одно место при координировании переименования, результат может оказаться плачевным. Вот где синоним может помочь минимизировать риск, связанный с переименованием базового объекта.

Предположим, что имеется приложение, разработанное для компании, в которой работают сантехники. Каждый сантехник, который когда-либо работал в компании, хранится в таблице с названием dbo.Plumber. В компании написано много кода, связанного с этой таблицей, для управления работой компании и её сантехниками. Однажды в компании решили расширить бизнес и включить электрические работы. Это расширение бизнеса требует модификации кода существующего приложения для поддержки нового вида работ. Кроме того, они планируют написать много нового кода для поддержки своего расширяющегося бизнеса. В первую очередь они хотят изменить имя таблицы dbo.Plumber на dbo.Employee. Тогда они смогут использовать новое имя таблицы для любого нового кода, который они планируют написать, пока координируют изменение названия для всего существующего кода.

Используя синоним, они могут сразу изменить имя и гарантировать, что весь существующий код будет работоспособным. Затем они могут постепенно менять старый код на использование новой таблицы. Как только весь унаследованный код будет изменен на использование нового имени, синоним можно удалить. Чтобы переименовать таблицу dbo.Plumber и создать синоним, администратор может выполнить код из листинга 2.

Переименование базового объекта и создание синонима для поддержки переименования имен

EXEC sp_rename 'dbo.Plumber', 'Employee';

CREATE SYNONYM dbo.Plumber

   FOR dbo.Employee;

Этот код сначала переименовывает существующую таблицу dbo.Plumber в dbo.Employee, а затем создает синоним для поддержки переименования. Этот код будет выполнен очень быстро, минимизируя риск ошибок в промежутке времени переименованием таблицы dbo.Plumber и созданием синонима dbo.Plumber.

Создание синонима в целях безопасности

Предположим в этом примере, что политика безопасности компании не позволяет разработчикам обновлять производственные данные. Согласно этой политике пользователи не смогут по ошибке обновлять производственные данные, когда они думают, что работают в среде разработки. Однако возникают ситуации типа проблем плохих данных, когда разработчикам необходимо обновить производственную таблицу. Если возникает такая ситуация, может быть создан синоним с соответствующими разрешениями, позволяющий разработчикам обновлять производственные данные для устранения проблемы с данными.

Предположим, что имела место проблема в таблице dbo.Article, которую требуется решить, выполняя операторы SELECT, INSERT, UPDATE и/или DELETE в производственном окружении. Чтобы обеспечить разработчикам доступ к обновлению для решения проблемы, может быть создан синоним с подходящими правами. Для того, чтобы сделать это, можно выполнить код в листинге 3.

Листинг 3. Код создания синонимов и разрешений.

CREATE SYNONYM dbo.Dev_Article

   FOR dbo.Article;

GRANT SELECT, INSERT, UPDATE, DELETE 

   on dbo.Dev_Article TO Developer_Group;

В листинге 3 был создан синоним dbo.Dev_Article, указывающий на базовую таблицу с именем dbo.Article. После создания синонима разрешения SELECT, INSERT, UPDATE и DELETE на новый синоним dbo.Dev_Article были предоставлены группе Developer_Group. Сделав это, программисты в группе Developer_Group будут иметь возможность просматривать и обновлять фактически таблицу dbo.Article, используя синоним dbo.Dev_Article. После решения проблемы синоним может быть удален.

Создание синонима в другой базе данных

В этом примере предположим, что имеется компания, которая хочет создать зеркало базы данных в среде разработки так, чтобы можно было вести параллельную разработку. Под параллельной разработкой я имею в виду то, что одна группа разработчиков может работать и писать код T-SQL в одной базе данных, в то время как другая группа разработчиков может выполнять работу в другой базе данных. Однако администратор базы данных говорит о проблеме недостаточного места на диске для копирования всех таблиц из существующей базы данных разработки (CurrentDB) в новую зеркальную базу данных разработки (NewDB). В этой ситуации, DBA решает, что он может установить новую зеркальную базу данных, создав синонимы в базе данных newDB для каждой большой таблицы, которую он не может перенести в зеркало из-за недостатка места на диске. В коде листинга 4 показывается, как создать синоним в базе данных NewDB для одной из этих больших таблиц, которые не могут быть зеркалированы.

Листинг 4. Создание синонима, который ссылается на таблицу в другой базе данных

USE NewDB;

Go

CREATE SYNONYM dbo.BigTable

   FOR [CurrentDB].dbo.BigTable;

GRANT SELECT, INSERT, UPDATE, DELETE on dbo.BigTable TO Developer_Group2;

GO

После создания синонима в базе данных NewDB все разработчики из группы Developer_Group2 смогут писать запросы SELECT, INSERT, UPDATE и DELETE к таблице dbo.BigTable, как будто она находится в базе данных NewDB. Помните, что когда разработчики выполняют код над таблицей dbo.BigTable в базе данных NewDB, команды фактически выполняются над таблицей dbo.BigTable в базе данных CurrentDB.

Это также применяется в процессе непрерывной интеграции. Базы данных, на которые есть ссылки, могут отсутствовать или иметь отличные имена во время процесса, что мешает успешной сборке.

Создание синонима для ссылок на объекты на другом сервере

Иногда приложению может понадобиться выполнить некоторый код на одном сервере, но сослаться на таблицу на другом сервере. Например, можно предположить, что сервер имеет ограниченное по размерам хранилище. Тогда периодически запускается процесс архивации, который перемещает историческую информацию на другой сервер, который я буду называть архивным сервером. Если приложению требуется выбирать некоторые данные с архивного сервера, то можно использовать имя из четырех частей и прилинкованный сервер для ссылок на эти объекты на архивном сервере, а можно определить синоним.

Имена из четырех частей будут работать, но их долго набирать. Использование синонима для таких длинных имен из четырех частей делает кодирование проще и делает удаленные ссылки подобные локальным. Код в листинге 5 показывает, как создать синоним, который ссылается на таблицу с именем из четырех частей, которая находится на архивном сервере.

Листинг 5. Определение синонима для таблицы на другом сервере

CREATE SYNONYM ClientHistory

   FOR ArchivedProd.MyApp.dbo.ClientHistory;

GO

Создав этот синоним, приложение теперь может использовать имя ClientHistory для ссылок на информацию об истории клиента, которая находится на архивном сервере, вместо длинного имени ArchivedProd.MyApp.dbo.ClientHistory.

Синонимы не могут ссылаться на другие синонимы

Синоним не может ссылаться на другой синоним. Для демонстрации этого я выполню код в листинге 6, создающий новый синоним в базе данных NewDB, который ссылается на синоним dbo.BigTable, созданный кодом в листинге 4.

Создание синонима, который ссылается на другой синоним

USE NewDB;

GO

CREATE SYNONYM dbo.Second_BigTable

   FOR dbo.BigTable;

GO

Если выполнить код в листинге 6 он выполнится без ошибок и создаст новый синоним с именем Second_BigTable. Но если я попытаюсь выполнить оператор SELECT в листинге 7, то получу ошибку, которая показана на рисунке 1.

Листинг 7. Оператор SELECT

SELECT * FROM dbo.Second_BigTable;

Листинг 7. Рис.1. Ошибка при попытке использовать синоним, который ссылается на другой синоним

Причина, по которой я не получаю ошибки при создании синонима, но получаю её при выполнении оператора SELECT, заключается в том, что код для синонима проходит валидацию не при создании, а во время исполнения. Эта возможность отложенной валидации — хорошая вещь. Отложенная валидация позволяет администратору создавать синонимы заранее до создания базового объекта.

Изменение определения синонима

Если базовая таблица для синонима переименовывается или перемещается, необходимо изменить определение синонима, чтобы отразить это изменение. Единственная проблема в том, что не существует оператора ALTER SYNONYM, которая бы помогла поддерживать переименование или перемещение базового объекта. Чтобы изменить существующий синоним, придется удалять его и создавать заново с использованием нового имени и местоположения базовой таблицы.

Удаление и повторное создание синонима может вызвать проблему, если какие-либо разрешения делегировались непосредственно синониму. Когда синоним удаляется, связанные с ним разрешения также удаляются. Следовательно, если по некоторым причинам требуется удалить и снова создать синоним, тогда администратор баз данных должен будет убедиться, что разрешения, предоставленные для существующего синонима, проверены и задокументированы до его удаления. Тогда они смогут воссоздать операторы GRANT для повторного применения разрешений после воссоздания синонима.

Обнаружение синонимов в базе данных

При работе с новой базой данных всегда мудро проверить, не определены ли в ней синонимы. Без этой проверки вы можете принять ссылку на синоним просто за ссылку на базовый объект, что может вызвать путаницу. Существует два различных метода обнаружения синонимов в базе данных.

Первый метод использует SSMS. Чтобы найти определения синонимов в базе данных с помощью SSMS, выполните следующие действия:

1. Откройте SSMS
2. Разверните папку баз данных
3. Разверните требуемую базу данных
4. Разверните пункт Synonym

На рисунке 2 показаны синонимы, созданные в базе данных NewDB,

Рис.2. Синонимы в базе данных NewDB

Другим методом показа синонимов в базе данных — это написать запрос к представлению каталога sys.synonyms в контексте той базы данных, которую вы проверяете. Пример запроса приведен в листинге 8.

Листинг 8. Вывод всех синонимов в базе данных с помощью T-SQL

SELECT * FROM sys.synonyms;

Предосторожности при использовании синонимов

Если вы планируете использовать синонимы, то должны знать о некоторых проблемах, с ними связанных. Вот перечень некоторых из вещей, которые могут отвернуть вас от использования синонимов.

Нельзя сохранить разрешения на синоним при их удалении.

Использование синонимов может ввести в заблуждение, если не знать, что используешь синоним.

При создании синонимов, которые ссылаются на объекты на других серверах, для тестирования и отладки вам нужно убедиться, что вы случайно не обновите данные на этих других серверах, если, конечно, вы не намеревались делать это.

В зависимости от используемой версии SQL Server, IntelliSense может не распознать, что это имя — синоним.

Вы не можете ссылаться на синоним на прилинкованном сервере. Если вы попытаетесь это сделать, то получите ошибку, подобную показанной на рисунке 3.

Ошибка при попытке сослаться на синоним на прилинкованном сервере

Синонимы SQL Server

Синонимы — отличный способ упростить ваш код по многим причинам, например, чтобы сделать более короткими длинные имена объектов, или для ссылок на таблицы в других базах данных или экземплярах SQL Server. Синонимы помогают в координации переименования базовых объектов или изменения их местоположения. После создания синонима ссылка на него может выглядеть как локальная таблица, хотя на самом деле она находится в другой базе данных или даже в другом экземпляре SQL Server. Даже при недоразумениях, которые могут возникать при использовании синонимов, компромисс использования синонима может стоить того в некоторых ситуациях. В следующий раз, когда вы захотите переименовать объект или сделать копию среды, возможно, вы захотите рассмотреть вариант с использованием синонима, чтобы упростить процедуру.

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	f1_keywords	helpviewer_keywords	dev_langs
CREATE SYNONYM (Transact-SQL)	CREATE SYNONYM (Transact-SQL)	markingmyname	maghan	02/01/2022	sql	t-sql	reference	CREATE_SYNONYM_TSQL SYNONYM_TSQL SYNONYM CREATE SYNONYM	alternate names [SQL Server] names [SQL Server], synonyms CREATE SYNONYM statement synonyms [SQL Server], creating	TSQL

CREATE SYNONYM (Transact-SQL)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]

Creates a new synonym.

:::image type=»icon» source=»../../includes/media/topic-link-icon.svg» border=»false»::: Transact-SQL syntax conventions

Syntax

-- SQL Server Syntax  
  
CREATE SYNONYM [ schema_name_1. ] synonym_name FOR <object>  
  
<object> :: =  
{  
  [ 
    server_name.[database_name].[schema_name_2].
  | database_name.[schema_name_2].
  | schema_name_2. 
  ] 
  object_name  
}  

-- Azure SQL Database Syntax  
  
CREATE SYNONYM [ schema_name_1. ] synonym_name FOR < object >  
  
< object > :: =  
{  
    [database_name. [ schema_name_2 ].| schema_name_2. ] object_name  
}  

[!INCLUDEsql-server-tsql-previous-offline-documentation]

Arguments

schema_name_1

Specifies the schema in which the synonym is created. If schema_name isn’t specified, [!INCLUDEssNoVersion] uses the default schema of the current user.

synonym_name

Is the name of the new synonym.

server_name

Applies to: [!INCLUDEsql2008-md] and later.

Is the name of the server on which base object is located.

database_name

Is the name of the database in which the base object is located. If database_name isn’t specified, the name of the current database is used.

schema_name_2

Is the name of the schema of the base object. If schema_name isn’t specified, the default schema of the current user is used.

object_name

Is the name of the base object that the synonym references.

[!INCLUDEssSDSfull] supports the three-part name format database_name.[schema_name].object_name when the database_name is the current database or the database_name is tempdb and the object_name starts with #.

Remarks

The base object need not exist at synonym create time. [!INCLUDEssNoVersion] checks for the existence of the base object at run time.

Synonyms can be created for the following types of objects:

• Assembly (CLR) Stored Procedure
• Assembly (CLR) Table-valued Function
• Assembly (CLR) Scalar Function
• Assembly Aggregate (CLR) Aggregate Functions
• Replication-filter-procedure
• Extended Stored Procedure
• SQL Scalar Function
• SQL Table-valued Function
• SQL Inline-table-valued Function
• SQL Stored Procedure
• Table¹ (User-defined)
• View

^{1 Includes local and global temporary tables}

Four-part names for function base objects are not supported.

Synonyms can be created, dropped and referenced in dynamic SQL.

[!NOTE]
Synonyms are database-specific and cannot be accessed by other databases.

Permissions

To create a synonym in a given schema, a user must have CREATE SYNONYM permission and either own the schema or have ALTER SCHEMA permission.

The CREATE SYNONYM permission is a grantable permission.

[!NOTE]
You do not need permission on the base object to successfully compile the CREATE SYNONYM statement, because all permission checking on the base object is deferred until run time.

Examples

A. Create a synonym for a local object

The following example first creates a synonym for the base object, Product in the AdventureWorks2012 database, and then queries the synonym.

-- Create a synonym for the Product table in AdventureWorks2012.  
CREATE SYNONYM MyProduct  
FOR AdventureWorks2012.Production.Product;  
GO  
  
-- Query the Product table by using the synonym.  
SELECT ProductID, Name   
FROM MyProduct  
WHERE ProductID < 5;  
GO  

[!INCLUDEssResult]

----------------------- 
ProductID   Name 
----------- -------------------------- 
1           Adjustable Race 
2           Bearing Ball 
3           BB Ball Bearing 
4           Headset Ball Bearings 

(4 row(s) affected)

B. Create a synonym to remote object

In the following example, the base object, Contact, resides on a remote server named Server_Remote.

Applies to: [!INCLUDEsql2008-md] and later.

EXEC sp_addlinkedserver Server_Remote;  
GO  
USE tempdb;  
GO  
CREATE SYNONYM MyEmployee FOR Server_Remote.AdventureWorks2012.HumanResources.Employee;  
GO  

C. Create a synonym for a user-defined function

The following example creates a function named dbo.OrderDozen that increases order amounts to 12 units. The example then creates the synonym dbo.CorrectOrder for the dbo.OrderDozen function.

-- Creating the dbo.OrderDozen function  
CREATE FUNCTION dbo.OrderDozen (@OrderAmt INT)  
RETURNS INT  
WITH EXECUTE AS CALLER  
AS  
BEGIN  
IF @OrderAmt % 12 <> 0  
BEGIN  
    SET @OrderAmt +=  12 - (@OrderAmt % 12)  
END  
RETURN(@OrderAmt);  
END;  
GO  
  
-- Using the dbo.OrderDozen function  
DECLARE @Amt INT;  
SET @Amt = 15;  
SELECT @Amt AS OriginalOrder, dbo.OrderDozen(@Amt) AS ModifiedOrder;  
  
-- Create a synonym dbo.CorrectOrder for the dbo.OrderDozen function.  
CREATE SYNONYM dbo.CorrectOrder  
FOR dbo.OrderDozen;  
GO  
  
-- Using the dbo.CorrectOrder synonym.  
DECLARE @Amt INT;  
SET @Amt = 15;  
SELECT @Amt AS OriginalOrder, dbo.CorrectOrder(@Amt) AS ModifiedOrder;  

See also

• DROP SYNONYM (Transact-SQL)
• EVENTDATA (Transact-SQL)
• GRANT (Transact-SQL)
• Synonyms (Database Engine)

Next steps

• Create Synonyms

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.

We all remember the English lesson we had in grammar school where we learned about synonyms. Just in case you don’t remember, a synonym is a noun that has a similar meaning as other words; just like intelligent has the same meaning as smart. SQL Server allows you to create a synonym so you can define an alternate name for an object that already exists. Synonyms function like aliases on columns or tables. However, a synonym is created as an object in a database, instead of being temporally defined at runtime. In this article, I will explain how to create and use synonyms to help develop and manage TSQL code.

Why would you need or want a synonym?

When a synonym is defined, it references an object in a database, known as a base object. The base object can be in the same database where the synonym is defined, but it can also reside in a different database on the same server, or even on a different instance altogether. There are lots of situations where a synonym might provide some benefits. Let me list a few:

• When there is a need to coordinate an object rename over time because there are 100’s or even 1000’s of references in code to the object that is being renamed.
• To provide a layer of abstraction to the actual base object.
• When an application wants to reference an object as if it appears to be in the current database when, in reality, it resides in a different database or instance.
• When backwards compatibility is needed to an old legacy object.
• When there is a need to provide a security layer for protecting the base object.
• When an object needs to be moved to another database, or instance without affecting existing code.
• To simplify naming standards for lengthy, or confusing object names.
• To eliminate issues with cross database and server dependencies in downstream environments such as development, test, and quality assurance as part of a continuous integration build process.

Syntax for creating Synonyms

The syntax for creating a synonym, as found in the Microsoft documentation, is shown in Listing 1.

Listing 1. The SYNONYM syntax

A synonym is simple to create. It can reference an object using one, two or three-part naming for both SQL Server and Azure SQL Database. But only SQL Server supports four-part names for referencing base objects that reside a different instance of SQL Server.

Synonyms can be created that reference the following types of objects:

Assembly (CLR) stored procedure	SQL inline-tabled-valued function
Assembly (CLR) scalar function	SQL table-valued function
Assembly (CLR) table-valued function	SQL stored procedure
Assembly (CLR) aggregate functions
Assembly (CLR) aggregate functions	View
Replication-filter-procedure	Table (User-Defined, including local and global temporary tables)
SQL scalar function

Creating, using, and managing synonyms

To better understand how to create, use and manage synonyms, I’ll go through a few examples. The examples will show you how to use synonyms that support different business situations, as well as how to manage synonyms.

Coordinating the renaming of an object

Once a database object has been created, and lots of application code has been written that references the object, it becomes a nightmare to rename the object. The nightmare comes from the amount of effort and coordination work required to make the name change without the application failing. If just one place is missed when coordinating the rename, the outcome could be disastrous. This is where a synonym can help minimize the risk associated with renaming a base object.

Suppose there is an application that has been built for a company that employs plumbers. Each plumber who has ever worked for the company is stored in a table named dbo.Plumber. The company has written a lot of code around this table to manage their business and their plumbers. Then one day, the company decides to expand its business to include electrical work. This expansion of the business now requires modifying their existing application code to support the new line of work. Additionally, they plan to write a lot of new code to support their expanding business. One of the modifications they want to make is to change the name of the dbo.Plumber table to dbo.Employee as soon as possible. This way, they can start using this new table name for any new code they plan to write, while they coordinate the name change for all the existing code.

By using a synonym, they can make the name change now, and ensure all the existing code doesn’t fail. Then as time allows, they can slowly change the old code to use the new table. Once all the legacy code has been changed to use the new name, then the synonym can be dropped. To rename the dbo.Plumber table and create the synonym, a DBA could execute the code in Listing 2.

Listing 2. Renaming base object and creating synonym to support a name change

The code in Listing 2 first renames the exiting dbo.Plumber table to dbo.Employee and then creates the synonym to support the renaming effort. This code will run very quickly to minimize the risk of any errors occurring between the time the dbo.Plumber table is renamed and the dbo.Plumber synonym is created.

Creating a synonym for security purposes

In this example, assume that a company has a security policy that does not allow developers to update production data. They have this policy so developers don’t mistakenly update production data when they thought they were working in development. However, there are situations, like bad data issues, where developers need to update a production table. When this situation occurs, a synonym can be created, with appropriate permissions, to allow developers to update production so they can fix the data issue.

Suppose there was a data issue in the dbo.Article table that needed to be resolved by issuing SELECT, INSERT, UPDATE and/or DELETE statements in the production environment. To provide the developers update access to resolve this issue, a synonym with appropriate permissions could be created. To accomplish this the code in Listing 3 could be run.

Listing 3. Code to create synonyms and permissions.

In Listing 3, the synonym dbo.Dev_Article was created to point to the based table named dbo.Article. Once this synonym is created the Developer_Group was granted SELECT, INSERT, UPDATE, or DELETE permissions on the new dbo.Dev_Article synonym. By doing this, the programmers in the Developer_group will be able to browse and update the actual table, dbo.Article using the synonym dbo.Dev_Article. Once the data issues are resolved the synonym can be dropped.

Creating a synonym in another database

For this example, suppose there is a company that wants to create a mirrored database in a development environment so that parallel development efforts can take place. By parallel development, I mean one group of developers can work and write TSQL code in one database, while another group of developers can do development work in a different database. The only problem is the company DBA says there isn’t enough disk space to copy all the tables from the existing development database (CurrentDB) to the new mirrored development database (NewDB). In this situation, the DBA decides he can establish the new mirrored database by creating synonyms in the NewDB database to point to each of the big tables that can’t be mirrored due to lack of disk space. The code in Listing 4 shows how to create a synonym in the NewDB databases that points to one of those big tables that can’t be mirrored.

Listing 4. Creating a synonym that references table in another database

After creating this synonym in the NewDB database, all the developers in Developer_Group2 will be able to write SELECT, INSERT, UPDATE and DELETE statements against the dbo.BigTable as if it resides in the NewDB database. Keep in mind when the developers executed code against the dbo.BigTable in the NewDB database the commands will actually be run against the dbo.BigTable in the CurrentDB database.

This also comes into play during a continuous integration build process. Referenced databases may not be in place or have different names during the process and keep the build from being successful.

Creating a synonym to reference objects on another server

There are times when an application might need to run some code on one server but reference a table on another server. For discussion, assume a server has limited storage space. Therefore, periodically an archive process is run that moves historical information to a different server, that I will call the archive server. If the application needs to select some data from the archive server, they could use a four-part name and a linked server to reference those objects on the archived server, or they could define a synonym.

The four-part names will work, but they are long names to type. By creating a synonym for these long four-part names, it makes the coding easier and makes these remote server references appear like they are local. The code in Listing 5 shows how to create a synonym that references the four-part table name that resides on the archive server.

Listing 5. Defining a synonym for a table on another server

By creating this synonym, an application can now use the name ClientHistory to reference the client history information that resides on the archived server instead of using the long four-part name ArchivedProd.MyApp.dbo.ClientHistory.

Synonyms cannot reference other synonyms

A synonym cannot reference another synonym. To demonstrate, this I will run the code in Listing 6 to create a new synonym in the NewDB database that references the dbo.BigTable synonym that I created using Listing 4.

Listing 6. Creating a synonym that references another synonym

When I execute the code in Listing 6, it runs without error and creates the new synonym named dbo.Second_BigTable. But if I try to execute the SELECT statement in Listing 7, I get the error in Figure 1.

Listing 7. SELECT statement

Figure 1. Error when trying to use a synonym that references another synonym

The reason I got no error when I created the synonym, but I got an error when I executed the SELECT statement is because the code for a synonym is not validated at creation time, but it is validated at run time. This feature of delayed validation is a good thing. Delayed validation allows a DBA to create a synonym in advance of when the actual base object will be created.

Changing a synonym definition

If a base table for a synonym is renamed or moved, the synonym definition will need to be altered to reflect the change. The only problem is that there is no ALTER SYNONYM statement to help support renaming or moving of a based object. To change an existing synonym, it will need to be dropped and then recreated, using the new name and location of the base table.

Having to drop and recreate a synonym can cause a problem if any permissions have been granted directly to a synonym. When the synonym is dropped the permissions associated with the synonym are also dropped. Therefore, if a synonym needs to be dropped and recreated for some reason, then a DBA will need to make sure permissions placed on the existing synonym are reviewed and documented prior to it being dropped. By doing this, they will be able to recreate the GRANT statements to reapply the permissions once the synonym is recreated.

Identifying the synonyms in a database

When working with a new database, it is always wise to review it to see if any synonyms have been defined. Without this review, you might assume a synonym reference is just a reference to a base object and thus cause confusion. There are two different methods for reviewing the synonyms defined in a database.

The first one is to use SSMS. To find the synonym definitions for a database using SSMS, follow these steps.

1. Open up SSMS
2. Expand the Databases folder
3. Expand the database that is being reviewed
4. Expand the Synonym item

Figure 2 shows the synonyms added to the NewDB database.

Figure 2. The synonyms in the NewDB database

The other method to display synonyms in a database is to write a query against the sys.synonyms catalog view in the context of the database where you want to explore for synonyms. An example of how to do that can be found in Listing 8.

Listing 8. Displaying all Synonyms in a Database using TSQL

Precautions when using synonyms

If you plan to use synonyms, then you should be aware of a few issues with using them. Here is a list of some of those things that might trip you up when using a synonym.

Cannot retain permissions on a synonym when they are dropped.

Using synonyms causes confusion when someone doesn’t realize that synonyms are being used.

When creating synonyms that referencing objects on other servers for testing and debugging purposes you need to make sure you don’t accidentally update the data on those other servers unless of course, that is what you intend to do.

Depending on the version of SQL Server being used, IntelliSense may not pickup that a name is a synonym.

You cannot reference a synonym across a linked server. If this is tried, you will get an error like the one shown in Figure 3.

Figure 3. Error when trying to reference synonym across a linked server

SQL Server Synonyms

Synonyms are a great way to simplify your code for a number of different reasons, like shortening up those long object names, or references tables in other databases, or instances of SQL Server. Synonyms help in the coordination of renaming base objects or changing their location over time. By creating a synonym, the reference to the synonym might look like a local table, when in fact it resides in a different database or even a different instance of SQL Server. Even with the confusion that can arise when using synonyms, the trade-off of using one might be well worth it for several situations. Next time you have to rename an object or copy an environment, you might want to consider whether a synonym will make this effort easier and more seamless.