SQL server, SQLServerPedia Syndication

CTE (Common Table Expressions) and sub queries.

Common Table Expressions or CTEs can be used to create temporary result sets based on another query. The CTE is not saved as a DB object and has the same lifetime of the query. CTEs come in handy when a recursive query is required, or when you need to aggregate on top of another select statement.

The CTE is populated by using a Select clause, similarly to how views are defined . Below I show an example of a CTE.

— With CTE
with uo_cte(id,CustomerID,ShoppingListstatus,ShoppingListdate)
as
(
SELECT  top 100 Id,CustomerID,ShoppingListStatus,ShoppingListDate FROM UserShoppingList where ShoppingListDate<‘2013-02-10’
)
SELECT ShoppingListstatus,COUNT(id) from uo_cte
group by ShoppingListstatus

I usually use subqueries to aggregate on top of another select clause. In the example below, I show how the CTE above can be redefined as a subquery.

— With Subquery
SELECT subq1.ShoppingListStatus,COUNT(subq1.Id) from
(
SELECT  top 100 Id,CustomerID,ShoppingListStatus,ShoppingListDate FROM UserShoppingList where ShoppingListDate<‘2013-02-10’
)subq1
GROUP by subq1.ShoppingListStatus

The above examples have pretty much the same performance and function. CTE’s have an advantage over using a subquery in that you can use recursion in a CTE. To read more about a recursive CTE, check out this link.

Advertisements
Standard

Thinking about someting? 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