SYNONYMS:
Problem:
At work we face situations like ,
1. where tables created in the production database are required to move to another DB as a minimal time period, Moving the table with data's is a tedious job
2. And also we use various SPs, TVF Functions in some DB say DB1. When we are working in some other DB2 and we have situation to use the same SPs and Functions in DB1 for a test process, here we need to run the whole scripts in DB2 and we have to drop it if it is no more useful which comsume time
Solution:
The feature is SYNONYMs in SQL Server 2005. SYNONYMs is new to SQL Server 2005. It is a way to give an alias to an already existing or potential new object(May be a Table,SP,Functions,Views etc). It is just a pointer or reference, so it is not considered to be an object.
Required Permissions:
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..
CREATING SYNONYMs:
A SYNONYM can be created within a DB or Between DBs and also Between DBs in Different Servers.
Example 1:
Here is an Example for Creating a Synonyms within a DB
Syntax:
CREATE SYNONYM [SynName] FOR [ObjectName]
USE AdventureWorks
GO
CREATE SYNONYM MySyn FOR Production.Location
GO
To check that this works you can issue a query like
SELECT * FROM MySyn
This returns the values of the table Production.Location . This any modifications to this Synonyms will reflect in Production.Location table.
Say for example
UPDATE MySyn
SET Name = 'MukundsIdeas'
WHERE LocationID = 1
Executing this query will affect the values in the
Production.Location table of AdventureWorks DB.
Example 2:
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.Location
GO
This creates a Synonym that can be accessible form any DB inside the Same Server.
Note: Make note on what Schema your DB use. For eg : if ur DB uses schema dbo then u should mention it as
CREATE SYNONYM dbo.MySyn FOR AdventureWorks.dbo.Production.Location
Example 3:
USE master
GO
CREATE SYNONYM dbo.MySyn FOR [UrServerName].[DBName].[TableName or SPname or Functionname or viewname etc]
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
1. Tables
2. Views
3. Assembly Stored Procedures,
4. Table Valued Functions,
5. Aggregations
6. SQL Scalar Functions
7. SQL Stored Procedures
8. SQL Table Valued Functions
9. SQL Inline-Table-Valued Functions
10. Local and Global Temporary Tables
11. Replication-filter-procedures
12. 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.
No comments:
Post a Comment