Using SYNONYMs in SQL 2005

Synonyms are great ways of using an alias to reference one or more tables in your database.

They were implemented in SQL 2005 and designed so that you can reference one or more tables by a single name. For example say you had a category matrix that held thousands of rows of data used on a results page. Instead of recalculating the data every time the page is loaded you have a database table that holds all the category data used on this report. The table is called

JOB_CATEGORY_MATRIX_A

and every 15 minutes you have an MS Agent job that rebuilds a new table containing the most up to date recent job category data and is called

JOB_CATEGORY_MATRIX_B

Once the new table has finished populating your application swaps over its SQL references so that it no longer looks at JOB_CATEGORY_MATRIX_A but instead looks at JOB_CATEGORY_MATRIX_B. Then 15 minutes later it does the same thing and swaps again.

Now SYNONYMs are a very useful tool as it means that in your application SQL you don't have to worry about changing your table names from JOB_CATEGORY_MATRIX_A to JOB_CATEGORY_MATRIX_B and vice versa every 15 minutes as you can reference both tables using a SYNONYM with the same name e.g JOB_CATEGORY_MATRIX.

How does this work?

Well to create a SYNONYM you can use the following SQL:

CREATE SYNONYM SITE_JOBS_MATRIX FOR mydatabase.dbo.SITE_JOBS_MATRIX_A

And to drop a SYNONYM you can use the following SQL:

DROP SYNONYM SITE_JOBS_MATRIX

Therefore to make use of a SYNONYM in your SQL you should just reference it like so:

SELECT * FROM dbo.SITE_JOBS_MATRIX

The time it takes to toggle a SYNONYM between two tables is very minimal almost a split second. However I have found on pages that use them in my SQL that are hit frequently by crawlers that occasionally errors will be raised saying that the table the SYNONYM points to doesn't exist. To get round this I have added some code into my generic data helper object so that if it catches any errors related to XXX does not exist. Then I do a wait for a second or two before trying the SQL again. By doing this the database has been given enough time to change the SYNONYM over.


Some useful SQL related to SYNONYMs

Sometimes you may need to find out details about the current table that the SYNOYM points to. To do this you can make use of the many great system views that come with SQL Server.

If you want to find the OBJECT_ID that the SYNONYM uses then you can use the OBJECT_ID function e.g

-- get the object_id of the SYNONYM
select object_id('SITE_JOBS_MATRIX')

-- get details about the object related to the ID
SELECT * FROM sys.objects WHERE object_id = 1036373490

If you want to know details about the current database table that the SYNONYM points to then you can make use of the system view sys.synonyms eg:

-- return details about the SYNONYM in question and the table it currently points to
SELECT base_object_name, name, object_id, *
FROM sys.synonyms
WHERE name = 'SITE_JOBS_MATRIX'

If you want to get a list of all the SYNONYMS in your database then you make use of the system views sys.objects and sys.sysobjects. I'm sure one of these will be depreciated in a future version of SQL but until then either one will give you the results you require.

-- list all SYNONYMNS in the current database
SELECT *
FROM sys.sysobjects
WHERE type = 'SN'

-- list all SYNONYMNS in the current database
SELECT *
FROM sys.objects
WHERE type = 'SN'

As you can see Synonyms are very useful tools for using aliases in SQL 2005 and can help when you have processes that involve building new tables to the side of existing ones for performance reasons.

If you want more information about this useful feature read the following article:

http://msdn.microsoft.com/en-us/library/ms177544.aspx