Maybe if I searched more, I'd use synonyms

One of the features added to SQL Server a few years ago were the ability to create synonyms and use those to reference other objects. The ability to create synonyms is something that I had wanted for years in SQL Server, but when they were released, I found them to be a tool that I rarely reached for. Whether it was because this was something I rarely needed to accomplish, or because my habits were too ingrained, I’m not sure, but I have only created synonyms for testing purposes and not for use in any production databases.

When a developer needs to reference data in another database (or on another server), they have a variety of ways in which they can do this. Some people prefer a three or four part naming convention, others use a view local to the database, and still others might use synonyms. While they all work, from a maintenance standpoint, I think a view or synonym provide a nice layer of abstraction while minimizing the potential maintenance headaches of future changes.

If you find synonyms more useful than local views, I would be interested in knowing why. They seem to almost operate in the same way to me, but for some reason I find views to be easier to track and manage. Perhaps it’s just an ingrained habit from years of making do with views, or maybe it’s my habit of browsing for objects, instead of using a tool like SQL Search.

Whichever method you use, I do urge you to always consider a layer of abstraction. That other database you reference might be located on the same instance today, but in the future it might grow and require it’s own instance. If you have three part naming buried in all of your stored procedure or application code, it might not be as simple as a global search and replace to make changes. If it’s not, then you are wasting development time down the road by not having a layer of abstraction implemented at the beginning.

Steve Jones

Steve Jones

The Voice of the DBA Podcasts

• Watch the Windows Media Podcast – 21.1MB WMV 
• Watch the iPod Video Podcast – 18.7MB MP4 
• Watch the MP3 Audio Podcast – 3.6MB MP3