Table expressions in t-SQL


This article outlines the different Table Expressions available in T-sql, how to define them and how to use them in queries: derived tables, common table expressions, views, inline table-valued functions.

A table expression is a query expression that can be used as a valid relational table in data manipulation statements.
SQL Server provides four types of table expressions: derived tables, common table expressions(CTE), views and inline
table-valued functions.

1. DERIVED TABLES:
Derived tables are also know as table subqueries. They are defined in the FROM clause of an outer query. Their scope of
existence is the outer query. Derived table query is defined within parentheses followed by the AS clause and the derived
table name as specified in the below example:


SELECT *
FROM (SELECT column1, column2
FROM Table1
where column3 = 'test') AS DerivedTable;

derived table query must meet three requirements to be valid:
Order is not gauranteeed, all columns must have names, all columns name must be unique.

2. COMMON TABLE EXPRESSIONS(CTE):

CTEs have following form:

WITH [(target_column_list)]
AS
(

)


For example:

With TestCTE AS
(
Select column1, cloumn2
FROM Table1
where column3 = 'Test'
)
SELECT * FROM TestCTE;

CTE lives in the scope of outer query like derived tables. Inner Query defining CTE must meet three requirements to be
valid:
Order is not gauranteeed, all columns must have names, all columns name must be unique.


3. VIEWS:
derived tables and CTEs have a limited scope, single-statement scope and thus are not reusable. However Views and inline
TVFs are two reusable types of table expressions as they are stored as database objects.

example for defining view:

CREATE view TestView
AS

SELECT column1, column2
FROM TestTable
Where column3 = 'test';

After view has been created it can be queried like any other table in database.

for example:

SELECT * from TestView;

Query which defines view must meet all requirements mentioned earlier for other table expressions.

4. INLINE TABLE-VALUED FUNCTIONS:
Inline TVFs are reusable table expressions, they also support input parameters.

Example for defining inline TVF:

CREATE FUCTION TestTVF
(@param AS INT) RETURNS TABLE
AS
RETURN
SELECT column1, column2
FROM TestTable
WHERE column3 = @param;

Example of using TVF in query:

SELECT column1, column2
FROM TestTVF(1);


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: