Make your life easier with T-SQL Common Table Expressions

1. Make your life easier with T-SQL Common Table Expressions

2. About PASS The PASS community encompasses everyone who uses the Microsoft SQL Server or Business Intelligence Platforms. This includes database administrators, application developers, Business Intelligence professionals, information technology professionals, executives, consultants, educators, instructors, and students. The Professional Association for SQL Server (PASS) is the only independent, user-run, not-for-profit association dedicated to the SQL Server community helping its members Connect, Share, and Learn by: • Facilitating member networking and exchange of information through our websites, Chapters, annual Summits, regional and local events, and virtual communities • Working with Microsoft to influence the evolution of SQL Server products and services • Delivering high quality, up-to-date, technical content, presentations, and other educational material for professional development

3. Abstract • Nested sub-queries, queries with multiple derived tables and queries with more than a few joins can be difficult to troubleshoot. Common table expressions (CTE's) compartmentalize your logic helping make complex queries easier to write as well as read. Recursive queries are handled especially well using a CTE.

4. Common Table Expressions Def. (Cont.) • Referred to as a “temporary named result set” in BOL. • Similar to a derived table, sub-query, view or a temp table, it “Provides a tabular result set that can be queried like a regular table” • Can reference the resulting table multiple times in the same statement. • Canduplicate the result set within the same query • Substitute a CTE for a view when the general use of a view is not requiredor is not permitted

5. Common Table Expressions Def. (Cont.) • Can be used to replace Subqueries, Temp and Derived tables. • Can replace cursors and while loops (in some cases). • Can be used for multiple levels of aggregation • Can be used in Select, Insert, Update, and Delete statements • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access. • Can be used for recursive queries

6. Common Table Expressions Def. • CTE’s offer the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until a final result set is generated.

7. General Format ;WITHCTEName(ColumnA,ColumnB,ColumnC)AS ( inner_query_definition--SELECT ) Outer_Query---SELECT, INSERT, UPDATE OR DELETE FROMCTEName----OR JOIN CTEName; • Recommended naming convention is to use prefix or suffix CTE • Column Names are optional and can Alias actual column names

8. Important facts! ;WITHCTEName(ColumnA,ColumnB,ColumnC)AS ( inner_query_definition--SELECT ) Outer_Query---SELECT, INSERT, UPDATE OR DELETE FROMCTEName----OR JOIN CTEName; • Always begin a CTE using “;WITH“ • Always remember that CTE's are materialized once for each time it is used in the query. This can have consequences for very large datasets

9. Restrictions • Scope is limited to the current batch, once the CTE is defined in the statement, you have to use it right away • Cannot use Order BY clause in your CTE definition unless TOP, OFFSET or FOR XML is also specified. • Recursive CTEs have further restrictions

10. Simple CTE Example • Get NetSales per Employee per Location order by highest achiever (open is SSMS) • All examples utilize Adventureworks2008R2_Database - http://msftdbprodsamples.codeplex.com/releases/view/93587 • Get to know your data - Quick familiarization with Adventureworks data • Sales.SaleOrderHeader table • Sales.SalesOrderDetail table • Sales.Customer • Sales.SalesPerson • Production.Product • Production.ProductSubcategory

11. CTE’s vs. Subqueries • Both are temporary objects. • Both can be created w/in stored procedures, views, and triggers. • Both can be non-Correlated or Correlated • Both can be in SELECT, FROM, WHERE, HAVING, IN, EXISTS clauses • A subquery is defined within an outer query. A CTE is defined before calling it from within the query. • A CTE can reference itself, a subquery cannot. • A CTE can reference other CTEs within the same WITH clause (Nest). A subquery cannot reference other subqueries. • A CTE can be referenced multiple times from a calling query. A subquery cannot be referenced.

12. Correlated vs. Non-Correlated • Non-Correlated -The inner select query of a subquery or CTE does not reference tables in the outer query and thus can be run independent of its outer query. • Correlated - The inner select query of a subquery or CTE does reference tables in the outer query and thus cannot be run independent of its outer query.

13. Examples • Subqueries can be replaced and or combined with CTE's. The following examples compare and contrast Subqueries with CTEs and demonstrate the flexibility of placement within the different statement clauses: • SELECT, FROM, JOIN, WHERE, HAVING, IN, EXISTS and as a Derived table • Example 1 - Determine Sum of Orderdetail for a SaleOrderID and return Sales Order Header Info • Example 2 - Determine Max Unit Price per SalesOrder

14. Thank You from PASSwww.sqlpass.org