How to create a synonym for a column in SQL Server?

Question

The following is not working. I want to create a synonym on a column in SQL Server 2017. How would I go about doing this?

CREATE SYNONYM dbo.[ProductTest] for [dbo].[Product].[ProductId]
GO

select ProductTest from dbo.Product

Answer 1

If you want to retain the table name but refer to an existing column by a new name a computed column will do this for you. The computed value of the column will be just the value of the existing column.

alter table [dbo].[Product]
add [ProductTest] as ([ProductId]);

Now you can write

select
    [ProductId],
    [ProductTest] 
from [dbo].[Product];

The two columns will have the same value, always.

If you reference only the computed column my guess is there won't even be a run-time overhead.

Answer 2

You can't create a synonym for a column; synonyms are simple redirects to first class entities like tables and stored procedures. The documentation lists the object types that are candidates (and column is not in the list):

Synonyms can be created for the following types of objects:

CLR Stored Procedure             CLR Table-valued Function
CLR Scalar Function              CLR Aggregate Functions
Replication-filter-procedure     Extended Stored Procedure
Scalar Function                  Table-valued Function
Inline-table-valued Function     Stored Procedure
View                             Table1

To simulate a synonym, if you can't change the table, you can create a view, and force users to access the view instead of the table:

CREATE VIEW dbo.vProduct
AS
  SELECT ProductTest = ProductID FROM dbo.Product;

Or control access through a stored procedure:

CREATE PROCEDURE dbo.Product_Get
AS
BEGIN
  SET NOCOUNT ON;
  SELECT ProductTest = ProductID FROM dbo.Product;
END
GO

If you already have a stored procedure that selects the original column name and you want to conditionally return the alias instead of the original, sometimes, you can use WITH RESULT SETS. The downside is you must define the data type:

CREATE PROCEDURE dbo.Product_Get_v2
AS
BEGIN
  SET NOCOUNT ON;
  SELECT ProductID FROM dbo.Product;
END
GO

EXEC dbo.Product_Get_v2;
-- returns column ProductID

EXEC dbo.Product_Get_v2 WITH RESULT SETS ((ProductTest int));
-- returns column ProductTest

If you can change the table and it needs to be more transparent, you can create a computed column, as Michael Green suggests. Just note that now both columns will come back in SELECT * and you'll have to update any stored procedures, views, or application code that currently references the old name.