SQL-Ex blog

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

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

• Когда требуется согласовать переименование объекта с течением времени, поскольку имеются сотни или тысячи ссылок в коде на объект, который переименовывается.


• Чтобы обеспечить слой абстракции для реального объекта базы.


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


• Когда требуется обратная совместимость с унаследованным объектом.


• Когда необходимо обеспечить слой безопасности для защищаемого базового объекта.


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


• Для упрощения стандартов именования для длинных или запутанных имен объектов.


• Для устранения проблем с перекрестными зависимостями баз данных и серверов в среде разработки, тестирования или контроля качества как построения части процесса непрерывной интеграции.

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

В документации 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. Даже при недоразумениях, которые могут возникать при использовании синонимов, компромисс использования синонима может стоить того в некоторых ситуациях. В следующий раз, когда вы захотите переименовать объект или сделать копию среды, возможно, вы захотите рассмотреть вариант с использованием синонима, чтобы упростить процедуру.