Thursday, 8 October 2009

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

4 comments:

Bob said...

Do you have any useful t-sql for swapping synonyms between table_a and table_b after determining which table the synonym is referencing?

R Reid said...

Well the article you just commented on is all about using SYNONYMs to do just that, switching SYNONYMs between two tables.

You just need to use the CREATE and DROP statement to drop the old pointer and the create the new pointer.

Example code is:

DECLARE @SYNONYM varchar(100)
,@SQL varchar(1000)

-- set name of SYNONYM
SELECT @SYNONYM = 'MY_SYNONYM'

-- ********************************************************
-- PUT CODE TO CHECK WHICH TABLE TO POINT SYNONYM TO HERE *
-- ********************************************************

-- check SYNONYM exists in database
IF OBJECT_ID(@SYNONYM) IS NOT NULL
BEGIN
-- drop the existing SYNONYM
SELECT @SQL = 'DROP SYNONYM ' + @SYNONYM
EXEC(@SQL)
END

-- Now re-create SYNONYM pointing it to the new table
SELECT @SQL = 'CREATE SYNONYM ' + @SYNONYM + '
FOR ' + db_name() + '.dbo.MYTABLE'

-- execute SQL string to create SYNONYM
EXEC(@SQL)

Anonymous said...

That is the craziest use for a synonym I've ever seen. If you have suitable isolation set up when you refresh the table you don't need to go to all those lengths!

Rob Reid said...

Please explain why this is crazy?

When you have a huge table that is constantly hit by visitors and used by thousands of users/BOTS which means the results cannot be built on the fly due to the amount of calculations that need doing. It seems like a good way to me to have a new table being built constantly behind the scenes and have a small delay in the results rather than locking up the table and hoping it doesn't cause issues, locking or blocking due to new data going IN and results being requested at the same time.

This way has worked for 6+ years now, the data is not out of date by much and it allows for very quick /categoryA/categoryB/categoryC/categoryD/ type ISAPI searches where each category has to show the number of live jobs next to it and a filter by one category has to then instantly show ONLY the categories that relate to jobs that have that category AND then show job counts for that category and all the others as well. The table is basically read only as the new one is being built so no locking as jobs continually pour into the site.

The swapping of the SYNONYM takes no time at all and allows 400+ jobboards to use one piece of SQL to reference the table rather than have different names for each site / jobs matrix.

So an explanation would be nice.