Step by Step guide to create Synonym in SQL Server 2008

Following are the main purposes of synonyms:

·         Provides an alternative name for another database object, referred to as the base object that can exist on a local or remote server.

·         Provides a layer of abstraction that protects a client application from changes made to the name or location of the base object.

To create synonyms we need to take care of followings

·         A synonym belongs to a schema.

·         Synonym must be unique in a schema.

·         You can create synonyms for the following database objects:

Assembly (CLR) stored procedure	Assembly (CLR) table-valued function
Assembly (CLR) scalar function	Assembly (CLR) aggregate functions
Replication-filter-procedure	Extended stored procedure
SQL scalar function	SQL table-valued function
SQL inline-tabled-valued function	SQL stored procedure
View	Table

On demand of one of my friend I have posted the blog post to show how we can create table using synonym. In following example I have created a sample database Blog and a table synonym which has two columns ID and name. I have inserted 3 sample values in this table. We will start synonym generation process from synonym tab in SSMS

When click on New Synonym we got following screen

We have following attributes

·         Synonym name: name of the table used as synonym

·         Synonym schema: schema that used with synonym. You can insert different schema then original table schema.

·         Server name: if we want to call a table from different server then we need to mention its name. as we can see in later attached pictures

·         Database name: name of the database from where we pick a table for synonym

·         Schema: current table schema

·         Object type: we can select Table and other options mentioned above. But for our example we will select Table now

·         Object name: tables listed from mentioned database above and select required table. In this example I have select ‘Synonym’

I have named my synonym student

In the above attached screen you can see Synonym as table and Student as synonym. When I ran a query on both tables I return same dataset.

Following are the options you can select creating synonym.

This screen will show you how you can mention server name

And look at the last screen shot. If you need to call a table from master database or any other server. You need to mention database name before table name. Just for reminder you need to create link server for cross server database table.

major benefit using this technique we do not need to mention database name before table.