A synonym is a very useful tool SQL Server tool for granting access to an object in an indirect fashion. You haven’t used Sysnonyms before? How do they work? What do they do?
A synonym is an object you define in an SQL Database that acts as a replacement to other database objects in the same database, or another SQL Server database. You can create a synonym on a Table, View, Function or Stored Procedure.
This capability is really useful for many different scenarios. I find it useful when writing a query that needs to reference a table in another database. I can create a synonym in my local database pointing to a table in another database, and then use the table as if it were persisted in my local database. In this case, the fully qualified table, database.schema.table is mapped through my synonym.
Now when I write any query using the table found at database.schema.table I don’t have to provide the fully qualified name. I can simply specify the schema name instead. This becomes really useful if the table referenced by the synonym is used in many different places, and the remote table is renamed, moved, etc. Now I simply update my synonym and all the code referencing that remote object is none the wiser…it just continues to work.
I can use a schema when defining my synonym. As a result I could have two different synonyms with the same name, belonging to different schemas. They could even point to different tables.
Another use of a synonym is to allow the existence of multiple versions of an object. Perhaps you need to add a parameter to a stored procedure. You could modify the existing stored procedure. But then what happens if you have to roll back your changes.
You could make a new stored procedure with a different name. Now you have to modify all the places in the code where the stored procedure is called.
Perhaps you could use a synonym in place of the stored procedure. Then you could define the synonym to point to the new stored procedure. For rollback, point the synonym back to the old stored procedure. Obviously this doesn’t always work when you are changing things like parameters. Your code calling the procedure must provide the correct attributes of the procedure in order to work.
Do you have favorite uses of a synonym. Share it here online, or drop an email to btaylor@sswug.org.
Cheers,
Ben