SQL Server has different kinds of non-materialized table objects. A materialized object is a table or indexed view which has physical representation stored on the disk. All other set techniques rely on materialized or static data in order to generate a set of data.
CTE (Common Table Expressions) are useful in that they may contain multiple different expressions resulting in tables. However, only one query may utilize the results of all the table expressions unless you send the results to temporary tables.
Views are a popular standby, available in every SQL engine I have used. Just like a CTE they may consist of many expressions, but only return one final result set.
Table Value Functions are popular. The can differ from views in that you can provide input to the function allowing it to filter or perform some other sort of query based on the input. As long as it returns a set of data, it meets the criteria of a table value function.
Another form or non-materialized data is in the form of inline views. This is when you define a query inside an SQL Statement, and use the results inside another SQL statement. Inline views may perform very similar to CTEs, but, in my opinion, they are a little harder to follow because you consume the values generated before they are defined. With the CTE the definition of the inline view is already made before it is referenced in another query.
You can use stored procedures but they are more difficult to consume the results inside another query. So, I left that out of the mix for this discussion. But, for the record, you can use stored procedures with User Defined Table Types, or sharing temporary tables.
Do you have a preference for non-materialized data? Do you find one method works better, is faster or more efficient, or simply easier for you to use? Share your opinion here or by Email to btaylor@sswug.org.
Cheers,
Ben