Which Kind of Table Expression to Use: Common Table Expressions, Views, TVFs, or Derived Tables?

Table expressions in T-SQL are a very convenient tools that make your codes more readable and easier to maintain. There are four kinds of table expressions:

1. Derived tables
2. Common table expressions (CTEs)
3. Views
4. Inline table-valued functions (TVFs)

So which kind of table expression to Use?

Derived tables and CTEs are only valid when the outer query is executed. That is to say, their scope is within the outer query. CTEs can be referred multiple times in the execution of the one SELECT, DELETE, UPDATE, or INSERT statement; while derived tables can only be referred once. On the other hand, views and TVFs are stored as database objects; and thus they are reusable as database objects (such as control access with permissions include SELECT, INSERT, UPDATE, and DELETE permissions). The table below lists the main differences between these four kinds of table expressions. Now will you be able to answer the question at the beginning?

	Scopes	Referred Multiple Times	Reusable	Support Parameters
Derived Tables	Single Statement	No	No	No
CTEs	Single Statement	Yes	No	No
Views	DB Objects	Yes	Yes	No
TVFs	DB Objects	Yes	Yes	Yes

Here is another little quiz: when you need a reusable table expression that need to have input parameters, which one should you use?

Bingo! Table-valued functions!

If you want to dive in more, here are more scenarios to learn:

1. If you need to find hierarchy relationships among the data, you definitely need a recursive CTEs. 


2. If you need pivot data to help you present the aggregation results (such as running totals, year-to-date calculations) or crosstab queries, CTEs shown below help you not intimidated by the complexity of PIVOT operator in T-SQL.
   
   ;WITH SaleTotalByYear AS
   (
   SELECT
   [SalesPersonID],                          -- grouping column
   YEAR([OrderDate]) as OrderYear,           -- spreading column
   [SubTotal]                                -- aggregation column
   FROM [AdventureWorks2008R2].[Sales].[SalesOrderHeader]
   where [SalesPersonID] IS NOT NULL
   )
   SELECT SalesPersonID, [2005],[2006],[2007]
   FROM SaleTotalByYear
   PIVOT(SUM(SubTotal) FOR OrderYear IN ([2005],[2006],[2007]) ) AS P
   ORDER BY SalesPersonID;
   


3. If you need a table expression that work with APPLY operator, table-valued functions are the right choice. The well-known script shown below is to find the scripts and its query plan for all queries currently running on your server:  
   SELECT 
       er.session_id,
       db.name database_name,
       er.status,
       er.wait_type,
       eqp.query_plan,
       est.text
   FROM sys.dm_exec_requests er
       JOIN sys.databases db ON (er.database_id = db.database_id)
       CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) eqp
       CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) est 
   
   

Better Performance?

Last not the least, you may wonder whether table expressions will bring better performance. The book of "Microsoft SQL Server 2012 T-SQL Fundamentals" mentioned that "Generally speaking, table expressions have neither positive nor negative performance impact". However,  The Seven Sins against TSQL Performance studied performance effects when using functions and nested views. So be cautious about performance impacts when using these kinds of table expressions.