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:
Operator | True 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