Introduction

Using a synonym is a way to create a different ‘pointer’ to a database object. This can be used to provide an alternative name for an object, or even an alternative location, such as a different database.

Background

I need to move a table from one database to another, with the least amount of upheaval for associated systems. Some years ago, I used synonyms but couldn’t quite recall what it would and would not allow. This article shows the work I executed to remind myself.

Method

Firstly, I’ll create a test database, with two very simple tables. These tables will have a clustered index and a foreign key constraint between them. Having checked the index usage and performance of a query against them, I’ll move one of the tables to another database, create a synonym to point to that new table and see what it does or does not allow.

Detail

The first step is to create a database, with two tables. Each table has a clustered index and there is a foreign key constraint between them.

Initial Database and Test Data

Listing 1: Create Test Database and Tables

CREATE DATABASE Synonym_Test;
GO

USE Synonym_Test;
GO

CREATE TABLE dbo.Warehouse
(
    ID INT IDENTITY
        CONSTRAINT PK_Warehouse PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR(50) NOT NULL
);

GO

CREATE TABLE dbo.Products
(
    ID INT IDENTITY
        CONSTRAINT PK_Products PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR(50) NOT NULL,
    Warehouse INT NOT NULL
);

GO

ALTER TABLE dbo.Products
ADD CONSTRAINT FK_Warehouse FOREIGN KEY(Warehouse)
REFERENCES dbo.Warehouse (ID);

GO

Now insert a few rows of test data.

Listing 2: Insert Data Into Tables

INSERT INTO dbo.Warehouse
(
    Name
)
VALUES
(N'Warehouse1'),
(N'Warehouse2'),
(N'Warehouse3'),
(N'Warehouse4');
GO

INSERT INTO dbo.Products
(
    Name,
    Warehouse
)
VALUES
(N'Test Product 1', 1),
(N'Test Product 2', 2),
(N'Test Product 3', 3),
(N'Test Product 4', 4);
GO

The following script is executed within SQL Server management Studio (SSMS), with the option for to include the actual execution plan and SET STATISTICS IO ON, so I can see how the query executes.

Listing 3: Query against both tables

SET STATISTICS IO ON;

SELECT p.Name,
       p.Warehouse,
       w.Name
FROM dbo.Products AS p
    INNER JOIN dbo.Warehouse AS w
        ON w.ID = p.ID;
GO

The output is produced, as well as IO information and the execution plan. This shows a scan and a seek against the clustered indexes.

Figure 1: The output from Listing 3

Now that I’ve established the expected behaviour, what if I move one of the tables to a different database? There are a variety of reasons for wanting to do this. It might be in order to move large, static tables elsewhere, making the management of the original database easier. In a previous role it was because a third-party data source changed regularly, and this was the method that that company used to manage what was effectively a fresh data source being added.

Second Database

This database will be used to relocate one of the tables from the test database that has been used earlier in this article.

First, create the new database and the new Warehouse table. This new table will also have a different name. that isn’t actually necessary but it demonstrates that the name of the table is irrelevant – the name specified by the synonym is the important part.

In reality, the data would be exported from the original database but that isn’t necessary with such a small test example, so the data will be re-created.

Listing 4: Create new Database, Table and Data

USE Synoym_Target;
GO

CREATE TABLE dbo.Synonym_Warehouse
(
    ID INT IDENTITY
        CONSTRAINT PK_Synonym_Warehouse PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR(50) NOT NULL
);

GO

INSERT INTO dbo.Synonym_Warehouse
(
    Name
)
VALUES
(N'Warehouse1'),
(N'Warehouse2'),
(N'Warehouse3'),
(N'Warehouse4');
GO

Back in the original ‘Synonym_Test’ database, I now create a synonym to the new table that is within the database ‘Synonym_Target’. I want to keep the name the same (‘Warehouse’), so the SQL code can remain unchanged.

Listing 5: Create the Synonym

CREATE SYNONYM Warehouse FOR Synoym_Target.dbo.Synonym_Warehouse;
GO

At this point we get an error – There is already an object named ‘Warehouse’ in the database.

This is because we still have the ‘Warehouse’ table within the database. The synonym has have a unique name and doesn’t know (or care) if it is being used to refer to a table, function, view, stored procedure or suchlike. So if it finds an existing object with the same name, it errors.

The solution is to remove the ‘Warehouse’ table. After all, it is now in another database. Drop the constraint from the ‘Product’ table and then drop the ‘Warehouse’ table.

Listing 6: Remove Warehouse Table from Database

USE [Synonym_Test]
GO

ALTER TABLE [dbo].[Products] DROP CONSTRAINT [FK_Warehouse]
GO

DROP TABLE [dbo].[Warehouse]
GO

Now, repeat the code from Listing 5 and the synonym will be created.

Figure 2: Result from Listing 5

Using the Synonym

First of all, we had to drop a foreign key constraint before we were able to drop the original Warehouse table. Now, try to re-instate that constraint.

Listing 7: Create the Constraint

ALTER TABLE dbo.Products
ADD CONSTRAINT FK_Warehouse FOREIGN KEY(Warehouse)
REFERENCES dbo.Warehouse (ID);

GO

The response from this command is the error:

Msg 1768, Level 16, State 0, Line 1
Foreign key ‘FK_Warehouse’ references object ‘dbo.Warehouse’ which is not a user table.
Msg 1750, Level 16, State 1, Line 1
Could not create constraint or index. See previous errors.

Again, this is because the synonym is not actually a table – it is an object, so a constraint can’t be created against it.

So, foreign keys are out, but what about the indexes?

Repeat the code from Listing 3, with the actual execution plan selected.

Figure 3: The output from repeating Listing 3

As an additional test (just, because) I have executed a query that selects based upon a specific Warehouse. I would expect an Index Seek with the following query.

Listing 8: SELECT for a specific Warehouse ID

SELECT p.Name,
       p.Warehouse,
       w.Name
FROM dbo.Products AS p
    INNER JOIN dbo.Warehouse AS w
        ON w.ID = p.ID
WHERE w.ID = 1;
GO

And the resultant execution plan shows what I was expecting – it is using the index on the Warehouse table, within the other database.

Conclusion

Synonyms provide a level of abstraction that can be useful however, when used for moving tables (which is what I was interested in) they do not allow all of the properties that can be associated with table in ‘normal’ use. The indexes are still used, which was my main interest but constraints may be an issue. This depends upon the project that this could be used for.

References

SQLDocs – Synonyms
Practical Uses for Synonyms – SQL Server Central