SSWUG.org Free Expo Event Friday, September 24, 2010 9:00 a.m. PT
SQL Server Performance Monitoring, Tuning & Troubleshooting are the focuse for this free expo. In the sessions, you’ll learn about query plans and the types of information and tuning help they can provide, numerous monitoring
and performance management options and much more. We’ll also be presenting sessions that include information about key setup and operations items for your SQL Servers.
Register here.
Featured White Paper(s)
Improve I/O Performance: Top 7 Benefits to Solid State Disk Storage
Application performance can be finicky. One day an application is slowed by network latencies; another day it can be impacted… (read more)
SQL Synonym
One of the things I like to do in SQL Server is to have libraries of SQL code available for use from any database.
For example, I have a table valued function I use that returns the last n minutes from any DateTime point of reference. I pass in the datetime and the number of minutes I want returned, and the function returns n records starting at the time I specified rolling back n minutes, with the start and end date/time for each time segment.
In older versions of SQL Server you could actually make this look like a system function, place it in the Master database, and it could be called from any database without full qualification. With SQL Server 2005 this capability was disabled.
Another technique was to put these kinds of functions in the Model database. Doing this caused them to be created in every new database from that point on. However, if you needed to make changes to the code, you now had multiple locations for the same code. It also had no impact in existing databases.
A third technique is to have a user defined/managed database with reusable SQL objects. The problem here is that every time you use one of these objects from another database you have to more fully qualify the object by database.owner.object. If the object is on a different server it then has to be fully qualified as server.database.owner.object.
This is where the Synonym technique, introduced in SQL Server 2005, can reduce the amount of typing and maintenance you have to perform. You can create a Synonym in any database, name it anything you like, and have it point to the real object. What does this buy me? I can now use the shared database option #3 to maintain a number of centralized SQL objects. Any database that uses this re-usable object has a Synonym pointing to the central database. Changes made to the object in the central database are reflected in calls from all Synonyms without any additional effort.
A additional benefit of Synonyms is that it results in loose coupling of your code. For example, SELECT ... FROM SharedDB.DBO.RollingMinute(GETDATE(), 20) is hard coded. If this code is part of a stored procedure and I move the RollingMinute Table Function elsewhere, or rename it, the stored procedure breaks. However, if I have a Synonym of RollingMinute in a database pointing to the original object, and I modify the original object, I simply update all Synonyms (if needed) and don’t have to physically address each place it is used.
To do this I would first create a Synonym like the following:
CREATE SYNONYM [dbo].[RollingMinute] FOR [Shared].[dbo].[RollingMinute]
Now my query looks like:
SELECT ... FROM RollingMinute(GETDATE(), 20)
Sure, this is a little more work. But you get the benefit of a virtual shared object without fully qualifying your objects. And, you have the ability to enable them at a schema level for security purposes as well.
For more information on Synonyms you can check out the Microsoft documentation at http://msdn.microsoft.com/en-us/library/ms187552.aspx.
Do you have your own Synonym for a Synonym (other ways to do the same thing)? Drop me an Email of things you like to make re-usable or techniques you have used for sharing your objects. You can reach me at btaylor@sswug.org.
Cheers,
Ben
Featured Article(s)
Step By Step DB2 9.X install on AIX Manual Method – Part 3
Step By Step DB2 9.X install on AIX Manual Method – Part 3