Advantages and disadvantages of using synonyms in SQL Server 2005

Questions raised

In my new job, I faced the problem of archiving the tables that were created in the product database, because as the database grows, it needs to be moved now. At the same time, these archived tables are used for several tasks and are invoked in the application's code. Moving them in this way is a very complicated process. I want to find a way to minimize the amount of work that developers have to do, because they have very limited time. Can you tell me the best way to file the table in a limited time?

Expert answers

There is a feature in SQL Server 2005 that solves this problem and provides further optimizations and tool devices, which are synonyms in SQL Server 2005 (synonyms). Synonyms (synonym) are new features in SQL Server 2005. It is an alias that is given in an existing or potential new object. It's just a pointer or parameter, so it's not treated as an object.

In order to create a synonym (synonym), you need to have the right to create synonyms (synonym). If you are a system administrator or db_owner, you will have these permissions and can grant this permission to other users. In addition, if you need to create a synonym that spans a database, you need the permissions of another database.

The following is an example of creating a synonym (synonym) in a database and referencing another object in the database:

USE AdventureWorks
GO
CREATE SYNONYM MySyn FOR Production.Location
GO

To check these tasks, you can use the following query in the use of synonym:

SELECT * From MySyn

Here is another example of creating a synonym (synonym) in a database and referencing the object in another database:

USE master
GO
CREATE SYNONYM dbo.MySyn FOR AdventureWorks.Production.Location
GO

To get all the changes in the synonym (synonyms), you can use the following command:

SELECT * FROM sysobjects
WHERE xtype = 'SN'
ORDER BY NAME

Delete synonyms (synonym) you can use the following command:

USE AdventureWorks;
GO
DROP SYNONYM MySyn
GO