Sql-server – Aliased Column name in SQL Server

sql server

Sometimes, when developing in a hurry, devs make poor choices on column names.

Changing them can be a challenge, having to stage code, and time migration perfectly.

I'm told that Oracle has a way to give a secondary name to a column. Does SQL Server have anything like this? The best I can think of is to add a calculated column to duplicate the column, but this will lead to follow on indexing and performance challenges.

Any good patterns or a magic bullet for SQL Server 2008 or 2012?

(Someone mentioned a view, which would definitely work, but wondering if there's a built-in option.)

I guess I'm looking for a Synonym for Columns.
http://msdn.microsoft.com/en-us/library/ms177544.aspx

Best Answer

As @JNK mentioned, you can use a view. There are some steps to take, of course. The view should map only to columns in a single table.

I assume that you do not want to deploy completely updated code that uses the new name. So, you can create a view with a different (but similar) name and any new code could use the view, and the new name. Existing code would still be going directly to the table.

This would get you moving, but one day you will probably want everthing to use the new column name. Eventually, you will have to deploy new code. Which would still mean a number of name changes if you don't want to have the view layer over the table.

Related Solutions

Sql-server – Where clause for aliased column

You can't reference an alias in the WHERE clause. You'd have to do something like this:

select left(DateTimeSent, 17) as timesent
from allog_07_11_e
where left(DateTimeSent, 17) <> '0'

Likewise, you could also use a subquery:

select *
from
(
    select left(DateTimeSent, 17) as timesent
    from allog_07_11_e
) a
where timesent <> '0'

Sql-server – SQL column name search

You have two options. Option 1 is to use the system views:

select
    t.name
    ,c.column_id
    ,c.name
    ,st.name
from
    sys.tables t
    join sys.columns c on (t.object_id = c.object_id)
    join sys.types st on (c.system_type_id = st.system_type_id)
where
    c.name = ''

Option 2 is a free add on from Red Gate Software called SQL Search.

Best Answer

Related Solutions

Sql-server – Where clause for aliased column

Sql-server – SQL column name search

Related Question