Please see my other Database Development articles.

Advanced Concepts

Apart from some of the latest advancements in t-sql, Ranking Functions for example, the t-sql query language provides the ability to perform some rather complex operations with data being returned from a query.
TOP WITH TIES

A fairly routine task most db developers are requested to perform is the query that returns a finite number of records.
For example, with the following employee records one request by be to only see the first n number of records, usually combined with an “ORDER BY” clause affected the sort order using one of the field.
Prior to SQL Server 2005, a sql developer would simply use the “TOP” statement along with whatever numeric indicator as to number of rows desired.
However, if the query specified “2” as in this example (below), the users of the report wouldn’t know they were missing three other employee records also with a “City” of “London.”
To ensure records also matching the desire sort sequence when using “TOP,” include the “WITH TIES” statement which ensure any additional records with identical values of the field target by the “ORDER BY” clause will also be included within the result set.
Subquery

Perhaps, the most common advanced type of query utilized by sql developers is the “subquery.”
The most subqueries can grow to be quite massive in size and complexity, the underlying premise is actually rather simple.
When the desire query requires more than one step of execution before the final result set is returned and the order of execution must be controlled, the subquery is the preferred method.
A subquery’s basic structure is simply a series of sql statements with two or more hierarchical nesting of the queries.
The precedence of the queries making up the overall subquery is determined by the innermost nested query – that is executed first.
In the example below, you’ll only two levels of a hierarchy within the subquery.
As previously stated, the inner most query beginning with “SELECT emp2.employeeid” is executed first.
The results from that query are then returned to the outer query for processing and the final step is executed, then the final result set is returned. This query’s job is to return a list of employees and their order count, but only for those employees with a territory of “New York” or “Philadelphia.”
To accomplish this task, first I find those employees in the desired territory.
They are returned for the final processing of the outer query – count their orders and retrieve their names.
CUBE

Though the concept of a “cube” isn’t new, its implementation in SQL Server is, and it’s quite powerful.
Though my query is relatively simple, the results are impressive.
Here, I’ve simply asked for a count of all orders by their “Shipped Date.”
Also, I’ve requested that each row have its “Freight Total” values summed.
The result is a very helpful overview of a company’s orders and freight costs.