Зачем бы вам понадобился синоним?
При определении синоним ссылается на объект в базе данных, который называется базовым объектом. Базовый объект может находиться в той же базе данных, в которой определен синоним, но может находиться и в другой базе данных на том же сервере или даже в другом экземпляре. Есть множество ситуаций, когда синоним может оказаться полезным. Вот некоторые из них:
- Когда требуется согласовать переименование объекта с течением времени, поскольку имеются сотни или тысячи ссылок в коде на объект, который переименовывается.
- Чтобы обеспечить слой абстракции для реального объекта базы.
- Когда приложение хочет сослаться на объект как будто он находится в текущей базе данных, в то время как на самом деле он находится в другой базе данных или экземпляре.
- Когда требуется обратная совместимость с унаследованным объектом.
- Когда необходимо обеспечить слой безопасности для защищаемого базового объекта.
- Когда необходимо переместить объект в другую базу данных или экземпляр без влияния на существующий код.
- Для упрощения стандартов именования для длинных или запутанных имен объектов.
- Для устранения проблем с перекрестными зависимостями баз данных и серверов в среде разработки, тестирования или контроля качества как построения части процесса непрерывной интеграции.
Синтаксис для создания синонимов
В документации 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, выполните следующие действия:
- Откройте SSMS
- Разверните папку баз данных
- Разверните требуемую базу данных
- Разверните пункт 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. Даже при недоразумениях, которые могут возникать при использовании синонимов, компромисс использования синонима может стоить того в некоторых ситуациях. В следующий раз, когда вы захотите переименовать объект или сделать копию среды, возможно, вы захотите рассмотреть вариант с использованием синонима, чтобы упростить процедуру.
Summary: in this tutorial, you will learn about SQL Server synonym and how to create synonyms for database objects.
What is a synonym in SQL Server
In SQL Server, a synonym is an alias or alternative name for a database object such as a table, view, stored procedure, user-defined function, and sequence. A synonym provides you with many benefits if you use it properly.
SQL Server CREATE SYNONYM statement syntax
To create a synonym, you use the CREATE SYNONYM statement as follows:
CREATE SYNONYM [ schema_name_1. ] synonym_name
FOR object;
Code language: SQL (Structured Query Language) (sql)The object is in the following form:
[ server_name.[ database_name ] . [ schema_name_2 ]. object_name
Code language: SQL (Structured Query Language) (sql)In this syntax:
- First, specify the target
objectthat you want to assign a synonym in theFORclause - Second, provide the name of the synonym after the
CREATE SYNONYMkeywords
Note that the object for which you create the synonym does not have to exist at the time the synonym is created.
SQL Server CREATE SYNONYM statement examples
Let’s take some examples of using the CREATE SYNONYM statement to get a better understanding.
A) Creating a synonym within the same database example
The following example uses the CREATE SYNONYM statement to create a synonym for the sales.orders table:
CREATE SYNONYM orders FOR sales.orders;
Code language: SQL (Structured Query Language) (sql)Once the orders synonym is created, you can reference it in anywhere which you use the target object (sales.orders table).
For example, the following query uses the orders synonym instead of sales.orders table:
SELECT * FROM orders;
Code language: SQL (Structured Query Language) (sql)B) Creating a synonym for a table in another database
First, create a new database named test and set the current database to test:
CREATE DATABASE test;
GO
USE test;
GO
Code language: SQL (Structured Query Language) (sql)Next, create a new schema named purchasing inside the test database:
CREATE SCHEMA purchasing;
GO
Code language: SQL (Structured Query Language) (sql)Then, create a new table in the purchasing schema of the test database:
CREATE TABLE purchasing.suppliers
(
supplier_id INT
PRIMARY KEY IDENTITY,
supplier_name NVARCHAR(100) NOT NULL
);
Code language: SQL (Structured Query Language) (sql)After that, from the BikeStores database, create a synonym for the purchasing.suppliers table in the test database:
CREATE SYNONYM suppliers
FOR test.purchasing.suppliers;
Code language: SQL (Structured Query Language) (sql)Finally, from the BikeStores database, refer to the test.purchasing.suppliers table using the suppliers synonym:
SELECT * FROM suppliers;
Code language: SQL (Structured Query Language) (sql)Listing all synonyms of a database
You can view all synonyms of a database by using Transact-SQL and SQL Server Management Studio.
A) Listing synonyms using Transact-SQL command
To list all synonyms of the current database, you query from the sys.synonyms catalog view as shown in the following query:
SELECT
name,
base_object_name,
type
FROM
sys.synonyms
ORDER BY
name;
Code language: SQL (Structured Query Language) (sql)Here is the output:
B) Listing synonyms using SQL Server Management Studio
From the SQL Server Management Studio, you can view all synonym of the current database via Synonyms node as shown in the following picture:
Removing a synonym
To remove a synonym, you use the DROP SYNONYM statement with the following syntax:
DROP SYNONYM [ IF EXISTS ] [schema.] synonym_name
Code language: SQL (Structured Query Language) (sql)In this syntax:
- First, specify the synonym name that you want to remove after the
DROP SYNONYMkeywords. - Second, use the
IF EXISTSto conditionally drop the synonym only if it exists. Removing a non-existing synonym without theIF EXISTSoption will result in an error.
Removing synonyms example
The following example uses the DROP SYNONYM statement to drop the orders synonym:
DROP SYNONYM IF EXISTS orders;
Code language: SQL (Structured Query Language) (sql)When to use synonyms
You will find some situations which you can effectively use synonyms.
1) Simplify object names
If you refer to an object from another database (even from a remote server), you can create a synonym in your database and reference to this object as it is in your database.
2) Enable seamless object name changes
When you want to rename a table or any other object such as a view, stored procedure, user-defined function, or a sequence, the existing database objects that reference to this table need to be manually modified to reflect the new name. In addition, all current applications that use this table need to be changed and possibly to be recompiled. To avoid all of these hard work, you can rename the table and create a synonym for it to keep existing applications function properly.
Benefits of synonyms
Synonym provides the following benefit if you use them properly:
- Provide a layer of abstraction over the base objects.
- Shorten the lengthy name e.g., a
very_long_database_name.with_schema.and_object_namewith a simplified alias. - Allow backward compatibility for the existing applications when you rename database objects such as tables, views, stored procedures, user-defined functions, and sequences.
In this tutorial, you have learned how to about the SQL Server synonyms and how to use them effectively in your applications.
A synonym, is nothing more than an alternate name that you can create for a database object. You can simple create a synonym to point to an object, and then reference that synonym in all of your queries.
- You can create synonyms for tables, temp tables, views, stored procedures, and functions.
- The object for the synonym does not have to be exist at the time the synonym is being created.
Even though it sounds simple, they can be very helpful for DBAs. But keeping track of all the synonyms in your database can be problematic.
For that reason, here is a script to list the synonyms in a specific database;
SELECT name AS synonym_name,
base_object_name AS synonym_definition,
COALESCE (PARSENAME (base_object_name, 4), @@servername) AS server_name,
COALESCE (PARSENAME (base_object_name, 3), DB_NAME (DB_ID ())) AS DB_name,
COALESCE (PARSENAME (base_object_name, 2), SCHEMA_NAME (SCHEMA_ID ())) AS schema_name,
PARSENAME (base_object_name, 1) AS table_name,
create_date,
modify_date
FROM sys.synonyms
This query returns 8 columns;
synonym_name Name of the synonyms
server_name Name of the server
synonym_definition Details of the synonyms
DB_name Name of the database
schema_name Name of the schema
table_name Name of the table
create_date Creation date of the synonyms
modify_date Modification date of the synonyms
- Although there are many other ways to get this info, here is a stored procedure for looping through all the databases and returning to you all the synonyms into the database;
CREATE PROCEDURE [dbo].[sp_synonym_details]
@server VARCHAR (100)
WITH
EXEC AS CALLER
AS
IF EXISTS
(SELECT 1
FROM tempdb.dbo.sysobjects
WHERE [name] LIKE ‘%DBTemp’ AND [type] = ‘u‘)
BEGIN
DROP TABLE ##DBTemp
END
DECLARE
@SQL NVARCHAR (1000),
@Count INT,
@Loop INT,
@Name NVARCHAR (100)
SELECT [name], 0 process
INTO ##DBTemp
FROM sys.databases
WHERE [name] NOT IN (‘msdb’, ‘master’, ‘model’)
ORDER BY [name]
CREATE TABLE #Temp
(
DBName VARCHAR (100),
synonymName VARCHAR (100),
synonymDefinition VARCHAR (100),
referenceServerName VARCHAR (100),
referenceDBName VARCHAR (100),
referenceSchemaName VARCHAR (100),
referenceObjectName VARCHAR (100),
createDate DATETIME,
modifyDate DATETIME
)
SET @Count =
(SELECT count (*)
FROM ##DBTemp
WHERE process = 0)
SET @Loop = 1
WHILE @Loop <= @Count
BEGIN
SET @Name =
(SELECT TOP 1 [name]
FROM ##DBTemp
WHERE process = 0)
SET @SQL =
‘insert into #Temp
(DBName ,
synonymName ,
referenceServerName ,
referenceDBName ,
referenceSchemaName ,
referenceObjectName,
createDate ,
modifyDate
)
(
SELECT
”’
+ @Name
+ ”’ as DBName,
name as synonymName,
base_object_name AS synonymDefinition,
COALESCE(PARSENAME(base_object_name,4),@@servername) AS referenceServerName,
COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) AS referenceDBName,
COALESCE(PARSENAME(base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS referenceSchemaName,
PARSENAME(base_object_name,1) AS referenceObjectName ,
create_date AS createDate,
modify_date AS modifyDate
FROM ‘
+ @Name
+ ‘.sys.synonyms
Where COALESCE(PARSENAME(base_object_name,4),@@servername)=”’
+ @server
+ ”’
)’
EXEC (@SQL)
SET @Loop = @Loop + 1
UPDATE ##DBTemp
SET process = 1
WHERE [name] = @Name
END
DROP TABLE ##DBTemp
SELECT * FROM #Temp
GO
SQL Server synonyms can be used to permanently alias database objects in the same or another database. In this article, Greg Larsen demonstrates how to use synonyms and the many reasons to consider using them.
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
|
— SQL Server Syntax 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 Syntax CREATE SYNONYM [ schema_name_1. ] synonym_name FOR < object > < object > :: = { [database_name. [ schema_name_2 ].| schema_name_2. ] object_name } |
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
|
EXEC sp_rename ‘dbo.Plumber’, ‘Employee’; CREATE SYNONYM dbo.Plumber FOR dbo.Employee; |
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.
|
CREATE SYNONYM dbo.Dev_Article FOR dbo.Article; GRANT SELECT, INSERT, UPDATE, DELETE on dbo.Dev_Article TO Developer_Group; |
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
|
USE NewDB; Go CREATE SYNONYM dbo.BigTable FOR [CurrentDB].dbo.BigTable; GRANT SELECT, INSERT, UPDATE, DELETE on dbo.BigTable TO Developer_Group2; GO |
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
|
CREATE SYNONYM ClientHistory FOR ArchivedProd.MyApp.dbo.ClientHistory; GO |
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
|
USE NewDB; GO CREATE SYNONYM dbo.Second_BigTable FOR dbo.BigTable; GO |
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
|
SELECT * FROM dbo.Second_BigTable; |
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.
- Open up SSMS
- Expand the Databases folder
- Expand the database that is being reviewed
- 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
|
SELECT * FROM sys.synonyms; |
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.
A SYNONYM provides another name for database object, referred to as original object, that may exist on a local or another server. A synonym belongs to schema, name of synonym should be unique. A synonym cannot be original object for an additional synonym and synonym cannot refer to user-defined function.
The query below results in an entry for each synonym in database. This query provides details about synonym metadata such as the name of synonym and name of the base object.
select * from sys.synonyms ;
Note : Synonyms are database dependent and cannot be accessed by other databases.
Syntax –
CREATE SYNONYM synonymname FOR servername.databasename.schemaname.objectname; GO
Example –
Let us assume Geektabtable of GFGdatabase, Geeekshschema on server named Server1. To reference this table from another server, Server2, an application would have to use four-part named Server1.GFG.Geeeksh.Geektab. Also, if the location of table were to change, for example, to another server, application would have to be modified to reflect that change.
To address both these issues, one can create synonym, Geektable, on Server2for Geektabtable on Server1. Now, the application only has to use single-part name, Geektable, to point Geektab table. Also, if location of the Geektab table changes, you will have to modify synonym, Geektable, to point to new location of Geektab table.
Now, let us create synonym for Geektab table of GFG database, Geeeksh schema on server named Server1.
CREATE SYNONYM Geektable FOR Server1.GFG.Geeeksh.Geektab; GO
Find the output in Server2 by using synonym.
SELECT ID, Name FROM Geektable;
Output –
| ID | Name |
|---|---|
| 1 | Nisha |
| 2 | Mira |
| 3 | Punit |
| 4 | Ram |
(4 row(s) affected)
Last Updated :
24 Aug, 2020
Like Article
Save Article