Alias creation in SQL Server

How we can create alias of a column in table in SQL Se开发者_StackOverflow社区rver?

Creation of aliases is very easy

SELECT tableColumnName as ColumnAlias FROM Table

Another thing is usage of the aliases, you must remember that the aliases are available after projection (select) this mean that you can't use those aliases in FROM, WHERE, GROUP BY, HAVING sections. Is allowed only in ORDER BY.

EDIT: Usage of aliases

Tables:

STACK 
 - STACK_ID
 - STACK_NAME
 - STACK_ORDER
 - STACK_MIN
 - STACK_MAX

Wrong statement:

    SELECT 
     STACK_NAME, 
     STACK_MIN, 
     STACK_MAX, 
     STACK_MIN + STACK_MAX as STACK_SUM 
   FROM 
    STACK WHERE STACK_SUM = 10;

We use in WHERE section column that is not available on this level.

To solve this we have two options

Option One - We do the calculation in where statement

    SELECT 
     STACK_NAME, 
     STACK_MIN, 
     STACK_MAX, 
     STACK_MIN + STACK_MAX as STACK_SUM 
   FROM 
    STACK WHERE STACK_MIN + STACK_MAX = 10;

Option Two - We create a temporary table

WITH STACK_SUM_TAB AS (
 SELECT 
  STACK_NAME, 
  STACK_MIN, 
  STACK_MAX, 
  STACK_MIN + STACK_MAX as STACK_SUM 
 FROM STACK 
)

 SELECT 
  STACK_NAME, 
  STACK_MIN, 
  STACK_MAX, 
  STACK_SUM 
 FROM STACK_SUM_TAB WHERE STACK_SUM = 10;

select somecolumn as foo from bar where foo = 5

SELECT columnname AS [ColumnAliasName] FROM [TableName]