Using SQL Server 2005's New Synonym Function

Last month I discussed the SQL Server 2005 NEWSEQUENTIALID() function, which generates globally unique identifiers (GUIDs) that are always greater that any previously generated GUID on the same machine. This month, I'll continue with the same theme—new, overlooked SQL Server 2005 functions—and discuss the synonym feature. A synonym is a link, created locally in a database, which points to an object either within the same SQL Server instance or in a linked server. You can use synonyms to point to generic objects—that reside in different databases—as if they resided as local objects in all databases. This capability allows you to simplify your code and the process of maintaining it. Let's explore some techniques for using synonyms.

Creating a Synonym

First, we'll create a synonym. The following sample code shows how to create the MyOrders synonym in the Northwind database, which points to the Orders table in the same database.

USE Northwind;GOIF OBJECT_ID('dbo.MyOrders', 'SN') IS NOT NULL  DROP SYNONYM dbo.MyOrders;GOCREATE SYNONYM dbo.MyOrders FOR dbo.Orders;

When you use the MyOrders synonym, it appears as if the MyOrders table exists in the Northwind database, as in this SELECT statement:

SELECT * FROM dbo.MyOrders;

Next, here's how to create an Orders synonym in the pubs database, which points to the Orders table in the Northwind database (i.e., same server, different databases).

USE pubs;GOIF OBJECT_ID('dbo.Orders', 'SN') IS NOT NULL  DROP SYNONYM dbo.Orders;GOCREATE SYNONYM dbo.Orders FOR Northwind.dbo.Orders;

When you refer to the Orders synonym in the pubs database, as in the following statement, it's as if you're referring to a local table even though the Orders table is in the Northwind database.

SELECT * FROM dbo.Orders;

You can use a synonym to point to objects in a linked server as if they were local objects. The following code creates an Orders synonym in the local tempdb database, which points to the Orders table in the Northwind database on a linked server called DOJO (i.e., different databases on linked servers).

USE tempdb;GOIF OBJECT_ID('dbo.Orders', 'SN') IS NOT NULL  DROP SYNONYM dbo.Orders;GOCREATE SYNONYM dbo.Orders FOR DOJO.Northwind.dbo.Orders;

While you're connected to your local tempdb database, you can issue a query against Orders as if it were a local table, like this:

SELECT * FROM dbo.Orders;

You can also create synonyms that point to a nonexistent object. However, at runtime, if SQL Server determines that the query is pointing to a nonexistent object, SQL Server will generate a resolution error and doesn’t execute the query. SQL Server also lets you create synonyms that point to temporary local and global tables.

Creating Synonyms for Generic Objects

You can also use synonyms to point to generic objects. Let's say you want to access auxiliary tables or routines that perform generic calculations, but these tables and routines reside in different databases. You can create generic objects in a database—let's name the database Generic—and create synonyms in each database to point to these generic objects. This way, regardless of which database you're connected to, you'll be able to refer to the generic objects as if they were local.

Now I'll show you how to create two generic objects that I use frequently in my solutions: an auxiliary table named Nums that contains a sequence of integers and a table-valued function called fn_split that accepts an array with a separator character as input and splits the array into individual elements. Run the code in Listing 1 to create the Nums table in the Generic database and populate it with one million rows—this should take a few minutes. To create the fn_split function in the Generic database, run the code in Listing 2.

Run the following code to create the fn_split synonym in the Generic database, so that you can refer to fn_split as a local object from the Northwind database.

USE Northwind;GOIF OBJECT_ID('dbo.fn_split', 'SN') IS NOT NULL  DROP SYNONYM dbo.fn_split;GOCREATE SYNONYM dbo.fn_split FOR Generic.dbo.fn_split;

Now run this SELECT command, which uses the fn_split function in the Generic database to split an input array into individual elements and produces the output that Table 1 shows.

SELECT * FROM dbo.fn_split('10248,10249,10250', ',') AS F;

Finally, run this code to join the results generated from the fn_split function with the Orders table and return those orders found in the input array. Table 2 shows the output.

SELECT F.pos, O.OrderID, O.CustomerID, O.EmployeeID, O.OrderDateFROM dbo.fn_split('10248,10249,10250', ',') AS F  JOIN dbo.Orders AS O    ON O.OrderID = CAST(F.element AS INT);

If you want to be able to access generic objects from all databases as if they're local objects, you'll need to create synonyms in all databases. To automate the task of creating synonyms in multiple databases, you can create a stored procedure in the master database. Run the code that Listing 3 shows to create the sp_create_synonyms stored procedure. This stored procedure accepts a fully qualified object name and creates a synonym to that object in all databases.

Next, run this code to create synonyms in all databases that point to the Nums auxiliary table and to the fn_split function.

EXEC dbo.sp_create_synonyms N'Generic.dbo.Nums';EXEC dbo.sp_create_synonyms N'Generic.dbo.fn_split';

Note that the stored procedure that Listing 3 shows also creates synonyms in the model database. Thus, all new databases that you'll create in the future will also contain these synonyms, which reside in the tempdb database.

Try to access Nums and fn_split from different databases. The following code invokes the fn_split function from the pubs database, splits an array of author IDs, returns author information for those authors whose IDs appear in the input array in the authors table, and generates the output that Table 3 shows.

USE pubs;SELECT F.pos, A.au_id, A.au_lname, A.au_fnameFROM dbo.fn_split('172-32-1176,213-46-8915,238-95-7766', ',') AS F  JOIN dbo.authors AS A    ON A.au_id = F.element;

The following code shows that you can now query the Nums table from the tempdb database.

USE tempdb;SELECT n FROM dbo.Nums WHERE n 

Generalizing Objects

As I demonstrated in this article, the synonym feature is powerful and you can use it to point to generic objects that reside in different databases as if they appeared as local objects in all databases. This capability allows you to simplify your code and the process of maintaining it.

See also: Using the newsequentialid() GUID Function