Synonyms

Synonyms are words with the same meaning. In SQL server 2005 synonyms works like a permanent alias.

Microsoft SQL Server 2005 introduces the concept of a synonym. A synonym is an alternative name for a schema-scoped object. Client applications can use a single-part name to reference a base object by using a synonym instead of using a two-part, three-part, or four-part name to reference the base object.

A synonym is a database object that serves the following purposes:

• It provides an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.
• It provides a layer of abstraction that protects a client application from changes made to the name or location of the base object.

Something to do:

1.  Create a table with a very long name

CREATE TABLE [dbo].[myverylonglonglonglongtablename](

                [id] [int] IDENTITY(1,1) NOT NULL,

                [field1] [nchar](10) COLLATE Latin1_General_CI_AS NULL,

 CONSTRAINT [PK_myverylonglonglonglongtablename] PRIMARY KEY CLUSTERED

(

                [id] ASC

)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

2. insert some thing in the table

declare @x int

select @x=0

while @x<>10

begin

select @x=@x+1

INSERT INTO [AdventureWorks].[dbo].[myverylonglonglonglongtablename]

           ([field1])

     VALUES

           ('hellox')

end

3.  Use select statement on the table with the long name

                Select * from [dbo].[myverylonglonglonglongtablename]

4.  create and use the synonyms

-- Create a synonym for the Product table in myverylonglonglonglongtablename

CREATE SYNONYM shortname

FOR [dbo].[myverylonglonglonglongtablename]

GO

 

-- Query the  table by using the synonym.

 

select * from shortname