fbpx

 

This SQL tutorial focuses on the SQL Server Column Aliases, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to write basic queries in SQL Server. To read additional posts regarding this subject, please use the following links:


 

SQL Server Column Aliases

In SQL Server by default the column’s heading in the results produced by your query, is the original column’s name as it was defined in the table.

SELECT last_name
FROM employees

last_name
----------
David
John

In many cases, column’s heading is not descriptive enough. In addition different operations, such as: string concatenation or mathematical calculation, will change the column’s heading to be even less readable and more difficult to understand:

SELECT 'Hello ' + last_name
FROM employees

(No column name)
----------------
Hello David
Hello John

SQL Server Column Aliases are used to change column’s heading. Using the SQL Server Column Aliases the headings will be more easy to understand and more meaningful. To create a SQL Server Column Alias use the following syntax :

SELECT column_name AS 'Column_Alias'
FROM table_name

For Example :

SELECT last_name, salary, salary  + 500  AS 'NEW_SALARY'
FROM employees

The result:

Last_name   salary    NEW_SALARY

---------   -------   ------------------

Smith       5700     6200

You can also write the SQL Server column’s alias without the AS Keyword, for example:

SELECT last_name, salary, salary  + 500  'NEW_SALARY'
FROM employees

The result:

Last_name   salary   NEW_SALARY

---------   ------   ------------------

Smith        5700     6200

It is also possible to write the SQL Server column’s alias without the enclosing single quotes (‘ ‘):

SELECT last_name, salary, salary  + 500 new_salary
FROM employees

The result:

Last_name    salary  new_salary

-----------  ------  ------------------

Smith         5700    6200
  • When a SQL Server column alias is written without enclosing single quotes (‘ ‘), it is not possible to write the alias as two (or more) separate words; you will have to insert some character between these words, underline (_) is most commonly used in these cases.
-- This query will generate an error
-- The "New Salary" column alias should be enclosed within single quotes
SELECT last_name, salary, salary * 1.1 AS New Salary
FROM employees

-- Insert an underline between these words :
SELECT last_name, salary, salary * 1.1 AS New_Salary
FROM employees

-- Or use single quotes :
SELECT last_name, salary, salary * 1.1 AS 'New Salary'
FROM employees
  • In order to make your SQL Server query more readable, always use the AS keyword, always use single quotes (‘ ‘), and when your alias consists of two words or more, always ensure that you insert an underline between them.
  • Another common approach to handle an alias consists of two words or more, is to concatenate the words, and capitalize the first letter of each word  :
SELECT last_name, salary, salary  + 500 AS 'NewSalary'
FROM employees