Query multiple databases with SQL Server 2005's new SYNONYMS aliasing feature
In SQL Server 2005 it's very easy to query multiple databases in the same statement.
For databases on the same server just use:
SELECT * FROM Database1.dbo.Table1 INNER JOIN Database2.dbo.Table1 ON Database1.dbo.Table1.ID = Database2.dbo.Table1.ID GO
The statement is similar for linked servers.
To provide for changing object names in the future the new SYNONYM feature of SQL Server 2005 is very handy. It lets you declare aliases for the following objects:
- Stored procedures
- CLR stored procedures
- CLR functions
- Replication filter procedures
- Extended stored procedures
- SQL scalar, table-valued, and inline table-valued functions
and the following statements:
CREATE SYNONYM ProdCat FOR AdventureWorks.Production.ProductCategory
``The synonym will then be visible in the object explorer, making them visible and transparent objects for developers:
However, editing of synonyms is not supported, they have to be dropped and recreated.
If you now try to create an object with the same name as an existing synonym you will get an error message:!(/px/SQLServer2005querymultipledatabasesandsy_13791/image0_thumb29.png)
(Liver Writer really blurs).
This means you can effectively reserve and block object names for linked objects in the defining database. Anyway, I'm not a DBA so don't ask me about performance implications of synonyms for distributed queries but they seem to make working with multiple databases quite a bit easier. For more information see the articles here and here.
Posted: 18 April 2007