20 February 2005

New Feature: Synonym

Finally have a database-wide alias mechanism for sql server objects. Want a long, descriptive object name? Knock yourself out:

CREATE SYNONYM shortName FOR [Really_Long_Descriptive_Table_Name_That_We_Never_Want_To_Type_Again]

-- you can then use the synonym any time you want to query the monstrously named table
SELECT * FROM shortName -- (not that you would likely see much select * in production...)

It also provides a nice layer of abstraction from the physical database layout. If we decide to change the base table we are selecting from so we can use a different table in the same database or, some table in another database or, even, on another server, we just update our synonym (of course, we still need to have the appropriate columns; can't be entirely out to lunch, here).

Looks like they work on quite a handful of objects, too; ranging from tables and views, through various types of functions and procedures, all the way to CLR Assemblies and functions (haven't got to play with the CLR in SQL, yet).

Handy advantage / possible drawback: The create synonym statement will successfully create synonyms for nonexistent objects. This is a handy feature if you want to create the alias before bothering to create the aliased objects (or drop and recreate an aliased object without worrying about maintaining the alias). However, it's a run-time gotcha if you forget to bother creating the aliased objects. Conversely, you can drop the aliased object without cleaning up the synonym, creating another potential run-time gotcha.

No comments: