SQL 2005+

Basic T-SQL Queries (2)

The eighth part of the SQL Server Programming Fundamentals tutorial describes the use of the SELECT statement of the structured query language. This command allows the creation of database queries that return table rows that meet specified criteria.

Using Column Aliases

Sometimes you will want to change the names of the columns in the set of results. To do so, a column alias can be provided in the column list using the AS keyword. The AS keyword appears after the real column name and before the alias. In the following example, all three columns have aliases, one including a space.

SELECT
    EngineerId AS Id,
    EngineerName AS [Engineer Name],
    HourlyRate AS Cost
FROM
    Engineers

Limiting the Result Set Size

If a query will return a very large number of results, you may decide to limit the result set size. By adding the TOP clause immediately after the SELECT keyword you can specify the maximum number of rows to return. This is particularly useful when working with ordered results as the sorting occurs before the result set is limited. The sample below limits the number of engineers returned to ten.

NB: The parentheses around the number 10 are optional for backward compatibility. Microsoft advises that these parentheses are included.

SELECT TOP (10) EngineerId, EngineerName, HourlyRate FROM Engineers

The row limitation can be expressed as a percentage, rather than a discrete value, by adding the PERCENT clause. If the exact percentage is not an integer, the number of rows will be rounded upwards. For example, in the following statement ten percent of the results would be 2.5 rows. This is rounded and three rows are returned.

SELECT TOP (10) PERCENT EngineerId, EngineerName, HourlyRate FROM Engineers

Eliminating Duplicates

In all of the above examples, each use of the SELECT statement has resulted in the return of every row. Had there been duplicate rows in the tables, you would have seen duplicate rows in the results. We can demonstrate this by switching to a different table. In this case, we will use the EngineerWorkingAreas table to find the list of areas that are serviced by DisasterFix.

SELECT AreaCode FROM EngineerWorkingAreas

As the table simply links engineers to their working areas, the results include duplicates. For example, "CEN" appears four times in the output as several engineers work in this area. If you want to eliminate these duplicates from the results, you can use the DISTINCT clause before the column list. The previous statement returned all sixty-two rows of data; the modified version below only returns the forty-eight unique rows.

SELECT DISTINCT AreaCode FROM EngineerWorkingAreas

The DISTINCT clause operates on the columns that are included in the column list. If multiple columns are provided, the combination of data must be unique. When using the clause to remove duplicates, null values are considered equal.

Using Expressions

As we have seen, literal values can be present in the column list of a query. In addition, expressions can be included to perform calculations and various operations using literal values and column names as their operands. A review of all of the operators and functions available is beyond the scope of this article; some of the more important functions will be described later in the tutorial. In this article we will review some simple arithmetic and concatenation operators.

To perform a calculation using the arithmetic operators (+ - * /), the expression is simply included in the column list. Usually you will specify an alias for the calculated column, as no name will be provided by default. The statement below calculates the total cost of the parts used on all jobs by multiplying the unit cost by the number of parts used.

SELECT PartNumber, UnitsUsed, Cost, UnitsUsed * Cost AS ExtendedCost FROM UsedParts

Another useful operation is the concatenation of character values. This is achieved using the concatenation operator (+). The text to be combined can be sourced from columns or can include literal strings surrounded by apostrophes, as in the next sample, which combines the part names and numbers from the Parts table.

SELECT PartName + ' (' + PartNumber + ')' FROM Parts