Column alias in table definition?

Question

I have a poorly named column that I would like to rename. The column name is a liablity for hidden "gotchas" because its name is misleading.

The problem is that there are existing programs that use that column. Is there any way to to rename it in the DB and create some sort of alias in the DB so that existing programs can query (no updates or inserts) the table using the old name?

Answer 1

You can create a very basic view that adds a column:

CREATE VIEW dbo.tablename_v
AS
  SELECT a, oldname, newname = oldname FROM ...

Or you can add a computed column if you really only need to SELECT:

ALTER TABLE dbo.tablename ADD newname AS CONVERT(datatype, oldname);

Note that updateability can be an issue in both cases, and an INSTEAD OF trigger may help you work around them.

Ideally, though, you'd fix the schema and the code. You can also perform a smart rename with some tools (e.g. SQL Server Data Tools or Red-Gate's SQL Refactor come to mind) but if your programs are referencing the columns in their compiled code this is going to be a lot more complex. (One of the reasons using stored procedures can be a better choice than embedding SQL in your apps.)