SYSK 206: Aliases in SQL Server

  • Article

Almost everybody knows that you can give aliases to columns in a result set and to tables/views in a query.  But did you know that you can also assign aliases to tables (including temporary tables), views, stored procedures (both, CLR and T-SQL), extended stored procedures, replication filter procedures, user defined aggregate functions (CLR), table valued functions (CLR and T-SQL), and scalar functions (CLR and T-SQL) outside of a query? 

 

So, instead of typing (and seeing) long four-part object names (ServerName.DatabaseName.OwnerName.ObjectName), you could use the new SYNONYMS feature in SQL 2005, and then use that shorter alias name (synonym).  For example, instead of

SELECT * FROM [servername].[AdventureWorks].[Production].[Product]

 

you’d do the following:

 

CREATE SYNONYM products FOR [servername].[AdventureWorks].[Production].[Product]

go

SELECT * FROM products

go

Once the synonym is “registered”, you can use it until it’s dropped by invoking

DROP SYNONYM products

 

Note : most solutions have pros and cons.  This one is no exception; so before adopting this feature, think whether the pros (shorter names) outweigh the cons (extra step required to get server-database-owner-objectname information, possible inconsistency in naming, etc.).