Table Derivatives

The table object is not the only tabulated data structure you can query in SQL Server. It is quite possible, desirable, and often essential to query the subsets and other derivatives of the base tables for various operations. We already discussed how it makes sense to divvy up a database into several tables, and as such it is possible to draw data from multiple tables and also query the subsets of the base tables. These subsets are still, however, stored and maintained on the server. Some may be temporary, others permanent.

We can query the following subsets and derivative tables from the SQL Server base tables:

• On-the-fly result sets (the results of queries and subqueries, unions, intersections, joins, and so on)

• Views

• Partitions

• Cursors

• Table derivates such as temporary tables, table variables, and the common table expression (CTE)

On-the-Fly Result Sets

These are result sets, the results of SELECT statements and subqueries, that return volatile subsets of data derived from base tables. I call them “on-the-fly” because the result set, unless specifically stored in a new table or view, is not persistent. It is created within the workings of a complete SELECT statement. In other words, as soon as it is derived from the base table, it is transmitted to the client or used in a subquery and then discarded.

The only vestige of the cause of the result set remains in the form of the SELECT statement stored in the query plan cache. But no subset resulting from the query remains after it has been returned to the client. I will call these “result sets” from here on.

It is possible, however, within a stored procedure, to draw a result set from a base table and prevent the data from being returned to the client automatically, which is the opposite of what SQL Server is inclined to do. Using flow control programming and assignment, you can then derive additional result sets from the earlier result sets (nested results sets) and control exactly which data gets returned to the client, if any. Still, upon termination of the procedure and achievement of the objective or termination through error, the result set is discarded.

Despite the volatility of the result set, a result set can explicitly be saved to a new table, in the midst of SELECT execution, by using SELECT INTO, which creates a table on the fly. I talk about SELECT INTO later in this chapter.

These nonpersistent result sets are created with the T-SQL SELECT statement and its support for ANSI or SQL-92/2003 style structure, as well as some legacy SELECT syntax, which has been kept around for old time’s sake.

Views and cursors can be queried in exactly the same fashion as tables, table derivatives, subsets, and result sets. There are several methods of catching fish; the most popular is the fishing pole (or rod). The SELECT is our fishing pole, and we first have to learn how to fish with it, before we can cast in the ponds of tables, views, cursors, and so on.