Benefits and limitations of using synonyms in SQL Server 2005
September 5th 2008 04:43
Benefits and limitations of using synonyms in SQL Server 2005
Problem
On my new job I faced a situation where archived tables were created in the production database and now there was a requirement to move them as the database was growing. These archival tables were being used by several jobs and also in the application code. Moving them was demanding and also a very complicated process. I wanted to find a way to minimize the amount of work that the development team had to do, since their time was limited too. Based on the needs and the limited time I wasn't sure what was the best option.
Solution
Fortunately I found a feature in SQL Server 2005 which solved my problems and provided further optimization and facilities. The feature is SYNONYMs in SQL Server 2005. A SYNONYM is new to SQL Server 2005. It is a way to give an alias to an already existing or potential new object. It is just a pointer or reference, so it is not considered to be an object.
In order to create a synonym, you need to have CREATE SYNONYM permissions. If you are a sysadmin or db_owner you will have these privileges or you can grant this permission to other users. Also, you create a synonym that crosses databases you will need permissions for the other database as well..
Here is an example to create the SYNONYM in a database that references another object in the database.
USE AdventureWorks
GO
CREATE SYNONYM MySyn FOR Production.Location
GO
To check that this works you can issue a query such as below that uses the new SYNONYM.
SELECT * FROM MySyn
Here is an example to create the SYNONYM in one database that references an object in another database.
USE master
GO
CREATE SYNONYM dbo.MySyn FOR AdventureWorks.Production.Loc ation
GO
To get the meta data for all synonyms use the following command
SELECT * FROM sysobjects
WHERE xtype = 'SN'
ORDER BY NAME
And to drop the synonym use the following command
USE AdventureWorks;
GO
DROP SYNONYM MySyn
GO
SYNONYM's can be very useful and can be created for
Tables
Views
Assembly Stored Procedures, Table Valued Functions, Aggregations
SQL Scalar Functions
SQL Stored Procedures
SQL Table Valued Functions
SQL Inline-Table-Valued Functions
Local and Global Temporary Tables
Replication-filter-procedures
Extended Stored Procedures
Benefits
SYNONYMs provide a layer of abstraction over the referenced object
Allow changes to complicated (multi part) and lengthy names with a simplified alias as a same server resident object.
Provides flexibility for changing the location of objects without changing existing code.
SYNONYMs can be created in the same database to provide backward compatibility for older applications in case of drop or rename of objects.
SYNONYMs can be useful if you give the front-end query tools like spreadsheets and Access linked tables direct links in to the tables.
Limitations
SYNONYMs are loosely bound to the referenced objects. So you can delete a SYNONYM without getting any warning that it is being referenced by any other database object.
Chaining is not allowed. It means that you can not create SYNONYM of a SYNONYM.
Obviously consumes possible object names, as you can not create a table with the same name of a synonym
The object for which the SYNONYM is being created is checked at run time. It is not checked at creation time. So this means that if you make any related error e.g. spelling error, the synonym will created, but you will get an error while accessing the object.
SYNONYM can not be referenced in a DDL statement
Problem
On my new job I faced a situation where archived tables were created in the production database and now there was a requirement to move them as the database was growing. These archival tables were being used by several jobs and also in the application code. Moving them was demanding and also a very complicated process. I wanted to find a way to minimize the amount of work that the development team had to do, since their time was limited too. Based on the needs and the limited time I wasn't sure what was the best option.
Solution
Fortunately I found a feature in SQL Server 2005 which solved my problems and provided further optimization and facilities. The feature is SYNONYMs in SQL Server 2005. A SYNONYM is new to SQL Server 2005. It is a way to give an alias to an already existing or potential new object. It is just a pointer or reference, so it is not considered to be an object.
In order to create a synonym, you need to have CREATE SYNONYM permissions. If you are a sysadmin or db_owner you will have these privileges or you can grant this permission to other users. Also, you create a synonym that crosses databases you will need permissions for the other database as well..
Here is an example to create the SYNONYM in a database that references another object in the database.
USE AdventureWorks
GO
CREATE SYNONYM MySyn FOR Production.Location
GO
To check that this works you can issue a query such as below that uses the new SYNONYM.
SELECT * FROM MySyn
Here is an example to create the SYNONYM in one database that references an object in another database.
USE master
GO
CREATE SYNONYM dbo.MySyn FOR AdventureWorks.Production.Loc ation
GO
To get the meta data for all synonyms use the following command
SELECT * FROM sysobjects
WHERE xtype = 'SN'
ORDER BY NAME
And to drop the synonym use the following command
USE AdventureWorks;
GO
DROP SYNONYM MySyn
GO
SYNONYM's can be very useful and can be created for
Tables
Views
Assembly Stored Procedures, Table Valued Functions, Aggregations
SQL Scalar Functions
SQL Stored Procedures
SQL Table Valued Functions
SQL Inline-Table-Valued Functions
Local and Global Temporary Tables
Replication-filter-procedures
Extended Stored Procedures
Benefits
SYNONYMs provide a layer of abstraction over the referenced object
Allow changes to complicated (multi part) and lengthy names with a simplified alias as a same server resident object.
Provides flexibility for changing the location of objects without changing existing code.
SYNONYMs can be created in the same database to provide backward compatibility for older applications in case of drop or rename of objects.
SYNONYMs can be useful if you give the front-end query tools like spreadsheets and Access linked tables direct links in to the tables.
Limitations
SYNONYMs are loosely bound to the referenced objects. So you can delete a SYNONYM without getting any warning that it is being referenced by any other database object.
Chaining is not allowed. It means that you can not create SYNONYM of a SYNONYM.
Obviously consumes possible object names, as you can not create a table with the same name of a synonym
The object for which the SYNONYM is being created is checked at run time. It is not checked at creation time. So this means that if you make any related error e.g. spelling error, the synonym will created, but you will get an error while accessing the object.
SYNONYM can not be referenced in a DDL statement
| 57 |
| Vote |
Add To: 
Comment by TimmyH
Tech News
Can you HACK it?
Genyration