MS SQL Server 2005 introduces a host of new features, some well-known; some not as familiar, but exciting like SYNONYMs.

Synonyms help us to create aliases for our objects. They replace fully qualified name into shortest user-defined name and simplify the naming of remote objects as well as objects that are stored in another database or another schema. They protect against changes in underlying objects.

Synonyms can be created for user-defined tables (including local and global temporary tables), views, assembly (CLR) stored procedure / table-valued function / scalar function / aggregate functions, replication-filter-procedure, extended stored procedure, SQL scalar function / table-valued function / inline-tabled-valued function / stored procedure. But take notice, that SYNONYMs base object will be checked only at run time.

Here is an example that directly accesses a table on another server: Normaly:

USE master
EXEC sp_addlinkedserver [servername\instancename];
GO

USE TEMPDB
SELECT * FROM
[servername\instancename].
AdventureWorks.Production.Product

And now with SYNOYM:

USE TEMPDB
CREATE SYNONYM synGetProductionProduct
FOR AdventureWorks.Production.Product;
GO

Now you can use:

SELECT * FROM synGetProductionProduct

s SQL Server versions, mostly you do this by creating a view! But you can't do that for stored procedures and functions, but now with SYNONYMs you can do something like this:

USE [AdventureWorks]
GO
CREATE FUNCTION [dbo].[ufnGetProductName]
(@ProductNumber [nvarchar] (25))
RETURNS [nvarchar](50) WITH EXECUTE AS CALLER
AS 
-- Returns the name.
BEGIN
    DECLARE @ProductName nvarchar (50);

    SELECT @ProductName = p.[Name] 
    FROM [Production].[Product] p 
	WHERE p.[ProductNumber]= @ProductNumber
            
    RETURN @ProductName;
END;
USE tempdb;
GO
-- Create a synonym for the 
-- Product table in AdventureWorks.
CREATE SYNONYM synGetProductName
FOR AdventureWorks.dbo.ufnGetProductName;
GO

SELECT dbo.synGetProductName('AR-5381') AS ProductName

For more information about SYNONYMs search BOL

Technorati Profile

CU tosc