Thursday, 31 January 2008

SQL Server 2005 Synonymns

I think this is probably one of the most useful features of SQL Server 2005 (and certainly one of my favourites).

A synonymn allows you to expose a stored proc, table, function etc, locally within in a database as a pointer back to another database (which can either be on the same server or a remote server).

This functionality really is brilliant for scaling database, and allows you to easily seperate functionally seperate database but still harness the power of common functionality. This allows you to get out of the mindset of lumping all your tables into one database and build an approriate solution.

Here's how you do it

CREATE SYNONYM [schema].[localtablename] FOR [databasename].[schema].[remotetablename]

Simple huh e.g

CREATE SYNONYM [dbo].[MyCommonStoredProc] FOR [MyCommonDB].[dbo].[MyCommonStoredProc]

Hope this helps, I certainly love it

No comments: