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