BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Basic T-SQL Queries

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.

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

Type Casting

Operations may require that the values involved be of specific types. If the column values are not of the correct type, they can be changed using the CAST function. This function has two parameters. The first is the value to be converted and the second is the new type, including a size where appropriate.

A simple example of the function is when concatenating text. For this combination to work correctly, the two operands must be character types. Concatenating numeric values is not permitted unless they are first cast to fixed or variable length character fields. The following statement generates a full parts list including a formatted price. The price is held in a money column that must be cast before concatenation with the part name.

SELECT PartName + ' (£' + CAST(Cost AS VARCHAR(7)) + ')' FROM Parts

NB: The current and desired types must be compatible in order for the cast operation to be successful. A full matrix of the possible conversions can be found on the Cast and Convert page of the MSDN web site.

Filtering Result Sets

The real power and flexibility of basic queries is provided by the ability to filter the result set, only retrieving rows that match a given set of criteria. To filter the results, one or more predicates must be provided in a WHERE clause. A predicate is an expression that can be evaluated to true, false or unknown. When the combined value of predicates is true for a row, the row is included in the returned results. If the evaluation is false or unknown, the row is omitted from the results.

Comparison Operators

Predicates can be created using comparison operators that require two operands. The six basic operators are as follows:

OperatorTrue Condition
=The two operands are equal.
!=The two operands are not equal.
>The first operand is greater than the second.
>=The first operand is greater than or equal to the second.
<The first operand is less than the second.
<=The first operand is less than or equal to the second.

Let's try some of these operators using the Engineers table. Firstly, we can find all of the engineers that have an hourly rate of exactly £20.00. There should be five such engineers.

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

To find all of the engineers who earn £20.00 per hour or less, try this query:

SELECT
    EngineerId AS Id,
    EngineerName AS [Engineer Name],
    HourlyRate AS Cost
FROM
    Engineers
WHERE
    HourlyRate <= 20
3 August 2008