LearnNowOnline

Blog

T-SQL: Using the SELECT Statement for Naming Columns

Excerpt by Don Kiely | April 18, 2013

One of the most versatile statements in all of T-SQL is the SELECT statement. It's guaranteed to become your go-to tool for retrieving data stored in a SQL Server database, with a dizzying array of clauses and options that let you select data from any databases and tables, shape it however you want, and apply conditions and filters so that you get exactly the data you need. This article discusses how to use the SELECT statement for naming columns.

Naming Columns

Notice in the Figure below that the column has no name. Since the column is the result of an expression, rather than a column in the database, you must explicitly name the column if you want to be able to reference the calculated column in a client application. Here is how you can do that by using the AS keyword:

SELECT LastName + ', ' + FirstName AS FullName

FROM dbo.Employees;

The query itself is essentially the same as the previous example; all that's changed is that the column now has a name, FullName. This name is also called an alias. Aliases are used routinely in complex SQL and are necessary for certain types of joins, such as the self joins that you'll learn about later in the chapter. To include a space within an alias, surround the alias with square brackets; the results appear in Figure 4.

SELECT LastName + ', ' + FirstName AS [Full Name]

FROM dbo.Employees;

The AS clause is optional-you can name a column just by using the name separated from the column list by a space:

SELECT LastName + ', ' + FirstName FullName

FROM dbo.Employees

This query functions in the same way as the query with the AS clause, but makes your SQL statement harder to read. Is FullName a column in the database and the programmer forgot the comma, or an alias? It is better to be explicit with the AS clause. The AS clause is the most explicit way to alias a column and is supported by the ANSI standard.

Another supported option is to use an equal sign for defining a column alias, which produces the same results as the previous statements. This syntax is required in some cases when using more advanced features of the SELECT statement.

SELECT FullName = LastName + ', ' + FirstName

FROM dbo.Employees

Deprecated Syntax - One method of defining column aliases using the equal sign (=) is now deprecated, which means it will not be supported in future versions of SQL Server. (Even though it has been deprecated for a long time, it still works in SQL Server 2012.) This method uses a string expression to define the alias:

-- Deprecated Syntax

SELECT 'FullName' = LastName + ', ' + FirstName

FROM dbo.Employees

You can, however, still use a string expression to define the column alias if you use the AS syntax (even if you leave out the word AS).

This post is an excerpt from the online courseware for our SQL Server 2012: T-SQL Select, Where, and Clauses course written by expert Don Kiely.