SYSK 206: Aliases in SQL Server

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]



SELECT * FROM products



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



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.).


Comments (0)

Skip to main content