Types of Table Expressions in TSQL

Table expressions are named queries. We write an inner query that returns a relational result set, name it, and query it from an outer query.

T-SQL supports four forms of table expressions

  • Derived Tables
  • Common Table Expressions (CTEs)
  • Views
  • Inline Table-Valued Function

Upper tows are visible only to the statement that defines them and the last twos are database objects; therefore, these are reusable. Just like any other database objects these can be access controlled. Table Expressions are part of relational database, so it follows all rules of set theory, means that all columns returned by the inner query must have unique names. Also, the inner query is not allowed to have an ORDER BY clause as set has no order. But there is an exception of the last rule: If we use TOP or OFFSET-FETCH option in the inner query, the ORDER BY serves a meaning that is not related to presentation ordering; rather, it’s part of the filter’s specification. So if the inner query uses the TOP or OFFSET-FETCH option, it’s allowed to have an ORDER BY clause as well. But then the outer query has no presentation ordering guarantees if it doesn’t have its own ORDER BY clause.

Derived Tables

A derived table is a form of table expression which is very close to a subquery—only a subquery that returns an entire table result. Derived tables are inner query in parentheses in the FROM clause of the outer query.

Difference between Derived tables and Subqueries:

– Derived Tables are used in the FROM clause

– Subqueries are used in the WHERE clause, but can also be used to select from one table and insert into another table).

Image

Image

Common Table Expression

CTE is a named table expression that is visible only to the statement that defines it. A common table expression (CTE) is similar to derived table. A query against a CTE involves three main parts:

The inner query

  • The name you assign to the query and its columns
  • The outer query

Unlike derive table the arrangement is quite different. With CTEs, you first name the CTE, then specify the inner query, and then the outer query—a much more modular approach.

WITH <CTE_name>
AS
(
<inner_query>
)
<outer_query>;

Image

Image

Views and Inline Table-Valued Functions

Both views and Inline Table-Valued Functions are database objects. Both has existence after the query execution. Views are commonly used for data security restriction while Table-Valued Functions are users for returning tables. But the main differences between these two are,

  • Table-Valued Function one can be parametrized but not indexed
  • Views can be indexed but not parametrized

View Example:

Image

Image

Table-Valued Functions Example:

Image

Image

 

Advertisements
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s