THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Roman Rehak

Synonyms and SQL Server Express

I've been a big fan of using SQL Server aliases for a long time because it allows you to make physical location of SQL Servers transparent to the client machines. With SQL Server 2005 Microsoft introduced synonyms, allowing you to define logical names for objects in another database or even on another server. This could be among other things beneficial if you need to move some tables to another database. Instead of recoding your application, you can define synonyms and point them to the new location (I wrote an article for about synonyms recently, you can get more details there if interested) This week I realized that synonyms can have another great benefit. As you know, SQL Server Express has a limit of 4GB per database. If your database begins to grow close to 4GB, you can move one or more large tables to another database on the same server, create synonyms in the original database and point them to the new location. I tested it on my SQL Server Express instance and it does work as expected. So with this knowledge, this limitation might become less of an obstacle for you to consider SQL Server Express.

Published Saturday, May 3, 2008 12:37 AM by roman



Saggi Neumann said:

Hey Roman,

You're right - it does ease overcoming the size limitation of Express Edition. Just don't setup any foreign keys and make sure to keep same object owner across databases to avoid security issues.

May 4, 2008 4:45 AM

AaronBertrand said:

Personally, I think synonyms are vastly under-rated and under-utilized.    But while I have put them to good use in a variety of scenarios, some things are missing, such as synonym chaining, the ability to override deferred name resolution, ALTER SYNONYM, IntelliSense support, and of course expanding synonyms to higher-level entities such as databases / linked servers.

May 6, 2008 1:25 AM

RickHeiges said:

Cool!!!!  - The old switcheroo.  I remember doing this with ACCESS back in the mid 90s when there was a 1GB data file limtation.  This is another great workaround.  Great Post!

May 7, 2008 9:48 PM

Ilan said:

when I create a sysnonym it does not recognize the server : asking me to link server

, does anyone knows why?

November 4, 2010 5:19 PM
New Comments to this post are disabled
Privacy Statement