SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Synonyms

By Ben Kubicek,

I am not talking about words that have the same meaning. I am talking about the SQL Server object that allows you to create an alternate name for a database object. The subject came up on a discussion forum that was connected to an article about using custom schemas. I thought it would be a good topic to bring into the light today.

First, if you are not familiar with synonyms in SQL Server, you can get a good overview and a general understanding from an article that Jason Shadonix wrote a while back. I don’t want to rehash Jason’s article, but synonyms can be very helpful in certain situations. I wrote an article recently about the importance of a consistent server setup.

In that article I advocate for database names to be named the same across prod, test, and dev. Still, some shops have a need or choose to use a _test or _dev appended to their production database name in their test or dev environments. In those cases, synonyms can really help. When you need to access data tables, views, stored procs, functions, on different databases or linked servers, you can create a synonym. This ensures that your queries and stored proc calls don’t have to change when you promote your code between dev, test and production.

Of course, you should be careful in how you use them. In some cases, it might be appropriate to name the synonym in such a way for it to be obvious to others that it is not the actual SQL object. Perhaps adding "S" to the front of the original object name when you create the synonym, for example. In other cases, you might want to obfuscate the actual underlying object with a synonym. In any case, synonyms are a useful tool for the SQL professional to be aware of.

If you haven’t used or heard of synonyms before, click a few links. Do a few Google searches and learn about them. They have the potential to help you save some time and they might make your life easier when it comes to promoting code to different environments.

Do you use synonyms in your work place? If you do, share how you use them and any issues you have run across with them in your systems?

 
Total article views: 54 | Views in the last 30 days: 54
 
Related Articles
ARTICLE

Practical Uses for Synonyms in SQL Server

The concept of a synonym was introduced in SQL Server 2005. Synonyms are very simple database objec...

FORUM

Synonym with servername\instancename

I am trying to create a synonym as follows. . .

FORUM

Synonym-Given String

Sql Server Query -help(Synonym for Given String)

BLOG

Using Synonyms to Extend SSIS

There are a million and one uses for synonyms.  There are at least that many uses for SSIS.  The rea...

FORUM

Synonyms

Hi all Is it possible to create a Synonyms in Sql server 2005 for a Oracle. Thank you in Advance...

Tags
editorial    
synonyms    
 
Contribute