How to use aliases / synonyms in MSSQL

Often SQL Server names, objects are very long, tedious and complicated for people to remember, especially for for end-users. Microsoft SQL Server provides synonyms to create aliases that are simple.

These synonyms / aliases are expanded at run time.

Create Synonyms / Aliases

CREATE SYNONYM prodhcosts FOR [W2K3BASE].[AdventureWorks].[Production].[ProductCostHistory];
go

SELECT * FROM prodhcosts;
go
ProductID   StartDate               EndDate                 StandardCost          ModifiedDate
----------- ----------------------- ----------------------- --------------------- -----------------------
707         2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 12.0278               2002-06-30 00:00:00.000
707         2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 13.8782               2003-06-30 00:00:00.000
707         2003-07-01 00:00:00.000 NULL                    13.0863               2003-06-17 00:00:00.000
...         ...                     ...                     ...                   ...
996         2003-07-01 00:00:00.000 NULL                    53.9416               2003-06-17 00:00:00.000
997         2003-07-01 00:00:00.000 NULL                    343.6496              2003-06-17 00:00:00.000
998         2003-07-01 00:00:00.000 NULL                    343.6496              2003-06-17 00:00:00.000
999         2003-07-01 00:00:00.000 NULL                    343.6496              2003-06-17 00:00:00.000

(399 row(s) affected)
  1. I cut off most of the values of select * from prodhcosts;.
  2. There was no message for the creation of the synonym.

View the synonyms / aliases

SELECT name,create_date, modified_date, base_object_name
FROM sys.synonyms;
go
name                                                                                                                             create_date             modify_date             base_object_name
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
prodhcosts                                                                                                                       2007-07-18 09:54:36.500 2007-07-18 09:54:36.500 [W2K3BASE].[AdventureWorks].[Production].[ProductCostHistory]

(1 row(s) affected)

delete Synonym / Alias

DROP synonym prodhcosts;
go
  1. There was no message for the destruction of the synonym.

Important warning

  • Synonyms are NOT validated at creation time.
  • Synonyms are validated only at runtime!