In SQL Server we’ll typically end up using environments containing multiple databases which happen to have data which relate to each other. Due to this there will be times when we’ll need to reference objects in another database when writing a query.
What options do we have for doing this?
Here we’ll look at three ways in which we can reference a table in a query across databases. We’ll be using two databases for our example, DbOne and DbTwo. Lets start by setting up a table and adding some data into DbOne:
USE DbOne
GO
/* Create the table */
CREATE TABLE dbo.CrossDbTable (
ID INT IDENTITY(1, 1) PRIMARY KEY,
NumberField INT,
ModulusTen INT,
ModulusThou INT
);
GO
/* Add some records */
INSERT INTO dbo.CrossDbTable (
NumberField,
ModulusTen,
ModulusThou
)
SELECT
CHECKSUM(NEWID()),
CHECKSUM(NEWID()) % 10,
CHECKSUM(NEWID()) % 1000;
GO 10000
Multipart Naming
First up we’ll take a look at using mutli-part naming (sometimes referred to as three-part naming).
When we usually reference a table we’ll use one or two-part naming for example just CrossDbTable or dbo.CrossDbTable respectively. This can be extended further to three parts to also include the database, so we could write a query like this:
USE DbTwo
GO
SELECT TOP (1000) *
FROM DbOne.dbo.CrossDbTable;
You’ll see that we’ve specified the database name which is then followed by the schema and table name. Note that we can’t omit the schema in this case if we’re specifying the database to query too.
This is the most straightforward way reference a table in another database. We aren’t reliant on any other objects in DbTwo to support this and so long as we have appropriate permissions this would work against any other database. You can find a little more detail in the online documentation.
Views
Another approach we could look to take would be to introduce a view into DbTwo to point to the table we’re looking to query in the other database.
This would provide us with an object which is local to our database and we’d be able to include it in our queries using one or two-part naming which is more typical in queries. We could create the view like this:
USE DbTwo
GO
CREATE VIEW dbo.vw_CrossDbTable AS
SELECT * FROM DbOne.dbo.CrossDbTable
GO
You’ll see that I’ve prefixed the view name with vw_ which I’ve done purely to differentiate the approaches we’re looking at here. Some folks may like a prefix to indicate what type of object it is but its completely personal preference.
With the view in place then our query can refer to that rather than needing to reference DbOne at all, for example:
USE DbTwo
GO
SELECT TOP (1000) *
FROM dbo.vw_CrossDbTable;
Views can also be used to reference multiple objects if we’re including joins or functions for example, etc. and could also introduce specific logic such as calculations or aggregations. In this instance we’re only looking at referencing the table as a whole when comparing these approaches.
You can also find more details on Views in the online documentation.
Synonyms
Last up we’ll look at the option of using a synonym to reference the table.
Synonyms are similar to views in that they are objects local to our second database, however unlike views these are purely a one-to-one mapping to a source object without any potential to include extra logic such as a join or calculation.
Creating the synonym would look something like this:
USE DbTwo
GO
CREATE SYNONYM dbo.sy_CrossDbTable
FOR DbOne.dbo.CrossDbTable;
GO
Again here I’ve used a prefix – sy_ – which is purely a choice for this example to differentiate the objects we’re creating.
Querying the synonym looks similar to a view in that its like querying a regular local object:
USE DbTwo
GO
SELECT TOP (1000) *
FROM dbo.sy_CrossDbTable;
With the definition of a synonym we can’t specify any additional logic nor even a list of fields we want to make available. Its almost like a shortcut to the remote table.
Again you can find further details about synonyms in the online documentation.
Wrap Up
Here we’ve looked at three different options for referencing objects external to a database. Really though there’s one big distinction to be made between these solutions – abstraction.
If we were to make heavy use of the multipart naming to reference another database and that database or object were to change, we may need to make a number of changes to our database to accommodate this.
Through the use of a view or synonym locally to point to the external table, we could resolve an issue like this by only changing the one impacted object. All queries could still be targeting our local object. Abstraction in this way is very powerful.
With that said, there are some interesting differences when using views and synonyms in this way which I want to revisit soon.
In the meantime, do you perform cross database queries like this and do you have any abstraction layer between your databases to make changes easier to manage? (or should you?)
Andy Brownsword 