T-SQL: Using the SELECT Statement for Naming Columns

Excerpt by Don Kiely | April 18, 2013

Giving a name to the computed column

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).


Thumbnail for 566This 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.

Don Kiely

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.

This course excerpt was originally posted April 18, 2013 from the online courseware SQL Server 2012, Part 8 of 9: T-SQL Select, Where, and Clauses by Don Kiely