Synonimy są nazwami przechowywanymi w bazie danych które mogą być używane jako substytuty dla innych nazw obiektów. Te nazwy są również przechowywane w bazie danych i należą do schematu bazy danych.
Tworzenie synonimów
Aby stworzyć synonim obiektu trzeba po prostu przypisać nazwę synonimu do wybranego obiektu. Przykładowo możemy zdefiniować synonim nazwany Categories w schemacie dbo dla obiektu Production.Categories dzięki czemu użytkownik nie będzie musiał pamiętać schematu bazy danych dla tej tabeli:
USE TSQL2012; GO CREATE SYNONYM dbo.Categories FOR Production.Categories; GO
Teraz pobieranie danych z tabeli może odbyć się bez definiowania schematu:
SELECT categoryid, categoryname, description FROM Categories;
Podstawowa składnia tworzenia jest prosta:
CREATE SYNONYM schema_name.synonym_name FOR object_name
Synonim jest obiektem bazy danych i musi spełniać następujące zasady:
- Synonim nie zawiera żadnych danych ani kodu T-SQL.
- Nazwa synonimu musi być identyfikatorem T-SQL.
- Jeżeli nie zadeklarujemy schematu bazy danych dla synonimu to zostanie użyty domyślny schemat naszego użytkownika.
- Object_name nie musi istnieć i SQL Serwer nie testuje tego.
- Kiedy używamy synonimu w wyrażeniu T-SQL to SQL Serwer sprawdza czy obiekt istnieje.
Synonimy można tworzyć dla obiektów typu:
- Tabele.
- Widoki.
- Funkcje użytkownika.
- Procedury składowane.
- CLR assemblies.
Synonimy nie mogą odnosić się do innych synonimów. Synonimów można używać w wyrażeniach T-SQL które odnoszą się do typu obiektu do którego synonim się odnosi, czyli EXECUTE dla procedur składowanych, SELECT, INSERT, UPDATE i DELETE dla tabel i widoków itd. Nie można używać synonimów do instrukcji DDL takich jak ALTER.
Usuwanie synonimu
Synonim można usunąć za pomocą instrukcji DROP SYNONIM np:
DROP SYNONYM dbo.Categories
Ponieważ nie istnieje instrukcja ALTER SYNONYM to trzeba go pierw usunąć i stworzyć na nowo.
Warstwa abstrakcji
Synonimy mogą odnosić się do obiektów w innych bazach danych również na powiązanych serwerach (linked servers), np.
EXEC sp_addlinkedserver Server_Remote; GO USE tempdb; GO CREATE SYNONYM MyEmployee FOR Server_Remote.AdventureWorks2012.HumanResources.Employee; GO
Użycie synonimów może w ogromny sposób ułatwić zapytania w których używamy wielu baz danych. Załóżmy, że baza danych DB01 ma widok Sales.Reports który jest na tym samym serwerze co baza TSQL2012. Aby uzyć jej w zapytaniu z bazy TSQL2012 trzeba napisać zapytanie jak np poniższe:
SELECT report_id, report_name FROM ReportsDB.Sales.Reports
Dodając synonim zwany Sales.Reports możemy uprościć to zapytanie:
CREATE SYNONYM Sales.Reports FOR ReportsDB.Sales.Reports GO SELECT report_id, report_name FROM Sales.Reports
Synonimy i odwołania do nieistniejących obiektów
Synonim możemy utworzyć nawet jeżeli obiekt do którego się on odwołuje nie istnieje. Zaletą jest to, że można użyć jednego synonimu dla wielu różnych obiektów usuwając i tworząc na nowo synonim. Można również stworzyć taki sam synonim w wielu bazach danych i nie używać nazw trzy segmentowych. Wadą synonimów jest to, że nie istnieje coś takiego jak WITH SCHEMABINDING. Nie zostaja one usuwane wraz z obiektem do którego się odwołują.
Uprawnienia synonimów
Aby stworzyć synonim musisz mieć uprawnienie CREATE SYNONYM. Po utworzeniu synonimu można nadać użytkownikom prawa do wykonywania lub odpytywania synonimu, w zależności od typu obiektu do jakiego synonim się odnosi.
Porównanie synonimów z innymi obiektami bazy danych
Synonimy są dosyć specyficznymi tworami ponieważ tak jak wszystkie inne obiekty w bazie danych należą do schematów bazy danych i są utrwalane w bazie lecz nie zawierają żadnych danych ani kodu. Synonimy nie przekazują metadanych obiektów do których się odnoszą.
Ćwiczenia
I. Użycie synonimów jako nazw opisujących dla raportów.
- Stwórz schemat Reports.
CREATE SCHEMA Reports AUTHORIZATION dbo; GO
- Stwórz synonim dla Sales.CustOrders o nazwie Reports.TotalCustQtyByMonth.
CREATE SYNONYM Reports.TotalCustQtyByMonth FOR Sales.CustOrders; custid, ordermonth, qty FROM Reports.TotalCustQtyByMonth;
- Stwórz synonim dla Sales.EmpOrders o nazwie Reports.TotalEmpQtyValOrdersByMonth.
CREATE SYNONYM Reports.TotalEmpQtyValOrdersByMonth FOR Sales.EmpOrders; SELECT empid, ordermonth, qty, val, numorders FROM Reports.TotalEmpQtyValOrdersByMonth;
- Przejrzyj metadane dla stworzonych synonimów.
SELECT name, object_id, principal_id, schema_id, parent_object_id FROM sys.synonyms; SELECT SCHEMA_NAME(schema_id) AS schemaname, name, object_id, principal_id, schema_id, parent_object_id FROM sys.synonyms;
II. Użycie synonimów do uproszczenia zapytań między bazami danych.
- Utwórz bazę danych SQL2012Reports.
USE master; GO CREATE DATABASE TSQL2012Reports; GO
- Utwórz schemat Reports w nowej bazie danych.
USE TSQL2012Reports; GO CREATE SCHEMA Reports AUTHORIZATION dbo; GO
- Utwórz synonim TotalCustQtyByMonth dla nieistniejącego obiektu lokalnego Sales.CustOrders i przetestuj go.
CREATE SYNONYM Reports.TotalCustQtyByMonth FOR Sales.CustOrders; GO SELECT custid, ordermonth, qty FROM Reports.TotalCustQtyByMonth; -- Fails GO DROP SYNONYM Reports.TotalCustQtyByMonth; GO
- Utwórz synonim TotalCustQtyByMonth odwołujący się do widoku Sales.CustOrders w bazie TSQL2012.
CREATE SYNONYM Reports.TotalCustQtyByMonth FOR TSQL2012.Sales.CustOrders; GO SELECT custid, ordermonth, qty FROM Reports.TotalCustQtyByMonth; -- Succeeds GO
Podsumowanie
- Synonimy są nazwami które odnoszą się do innych obiektów jak np tabele, widoki, funkcje czy też procedury składowane.
- Synonimy nie zawierają danych ani kodu T-SQL.
- Synonimy nie rozpowszechniają metadanych obiektów z którymi sa powiązane.
- Można modyfikować dane za pomocą synonimów ale nie można zmieniać definicji obiektów.
- Aby zmienić synonim trzeba go usunąć i stworzyć na nowo.