SQL Server Synonyms – What They Are And How To Use Them

Facebooktwitterredditpinterestlinkedinmail

What Is A Synonym?

A synonym in SQL Server can be thought of like having another name for a table.  The table doesn’t even have to be in your own database.  The intent of having synonyms is for an easy way to reference objects in another database.  It basically gives you a short alias to call server objects that live in another database.  You can create a synonym for tables, views, stored procedures, and functions!

 
Example Without Synonym:

SELECT	*
FROM	FarmAnimals.dbo.SoundsOfAnimals

 
Example With Synonym:

SELECT	*
FROM	AnimalSounds

In the example above, the synonym AnimalSounds represents FarmAnimals.dbo.SoundsOfAnimals.

 
NOTE: Because the synonym is basically an alias for a different server object, INSERT, UPDATE, SELECT, DELETE operations happen directly on the base table and not on the synonym.

 
Why Would You Use A Synonym?

There are 2 main reasons for using synonyms.  The first reason is to make the code a little easier to read.  It makes all of the remote objects look like local objects.  The second reason is that it creates a layer of abstraction.  If the base table changes, then you only need to change the synonym.  If you did not use a synonym, then you would need to go in to every stored procedure, view, function, etc… and change the reference to the new table.

 
Real-Life Use

So… although it is a neat concept, I really don’t use synonyms very much.  The one application where I see it very useful is when you do not own the database that you are referencing (like if you bought a 3rd party product).  Product updates could change the table/stored procedure names… if you use a synonym to reference the database tables, then it would only take 1 change on your side to get your program working again.

 
 
Reference:  http://msdn.microsoft.com/en-us/library/ms187552.aspx

 

Connect To Remote Database Without A Linked Server

Facebooktwitterredditpinterestlinkedinmail

Most of the time that people connect to a remote database they use linked servers.  But have you ever wanted to connect to another database server without creating a linked server?  This post will show you how to connect to an ad hoc database server while executing a query.

To accomplish this, one method (samples below) that SQL Server provides is to use opendatasource.  Opendatasource allows you to connect and query a remote data source by passing your connection parameters in to the command.

NOTE: Microsoft does not recommend using this method of connecting to a remote database server if this data source is accessed more than a few times.  If the data source is frequently used, they recommend that you create a linked server.

Connect To Remote Database Using Windows Authentication

SELECT	*
FROM	OPENDATASOURCE('SQLNCLI', 'Data Source=RemoteServerName;Integrated Security=SSPI').Billing.dbo.Invoices

Connect To Remote Database Using SQL Server Authentication

SELECT	*
FROM	OPENDATASOURCE('SQLNCLI', 'Data Source=RemoteServerName;user id=username;password=password).Billing.dbo.Invoices

In the examples above, SQLNCLI tells SQL Server to connect to the database using an SQL Server Native Client provider.  This will automatically use the latest SQL Server Native Client OLE DB Provider.

 

Reference: http://technet.microsoft.com/en-us/library/ms179856.aspx  –  OPENDATASOURCE (Transact-SQL)