The Brave Programmer - Blogging and coding
Not for the faint hearted
 

Blog Posts From The Brave Programmer

Minimize

Using Common Table Expressions (CTE) in MS SQL

Jun30

Written by:
2009/06/30 09:49 AM RssIcon

A common table expression is SQL  expression that returns a temporary result set from a simple or complex query, defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can work with both DDL and DML statements.

Normally, views are used to break down complex queries into digestible chunks or to provide a set of subquery results that can be grouped and filtered. Sometimes views can be overkill. They are permanent objects at in the database. Normally this type of scenario means we we only need to reference this complex query in a single stored procedure or UDF. Another option available to use is to use a derived table. Unfortunately, derived tables can make the readability of the query a little bit confusing. The derived table must also be repeated for each use in a statement.

A common table expression looks very similar to a temporary table or view.  The only difference between a CTE and a view is that views are created as database objects, whereas CTEs are not created as objects in the database. A CTE is available only for a single statement, and in general, every CTE can also be a part of a view.

Common Table Expression Syntax
A Common Table Expression contains three core parts:

  • The CTE name (this is what follows the WITH keyword)
  • The column list (optional)
  • The query (appears within parentheses after the AS keyword)

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is:

SELECT <column_list>

FROM expression_name;

The query using the CTE must be the first query appearing after the CTE. That is, you cannot do the following:

WITH ProductAndCategoryNamesOverTenDollars

(ProductName, CategoryName, UnitPrice) AS

(
SELECT
      p.ProductName,
      c.CategoryName,
      p.UnitPrice
FROM Products p
INNER JOIN Categories c ON
         c.CategoryID = p.CategoryID
WHERE p.UnitPrice > 10.0
)
 
SELECT *
FROM Products
 
SELECT *
FROM ProductAndCategoryNamesOverTenDollars
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC 

A typical Common Table Expression might look like this:

WITH ProductAndCategoryNamesOverTenDollars

(ProductName, CategoryName, UnitPrice) AS

(
SELECT
      p.ProductName,
      c.CategoryName,
      p.UnitPrice
FROM Products p
INNER JOIN Categories c ON
         c.CategoryID = p.CategoryID
WHERE p.UnitPrice > 10.0
)
 
SELECT *
FROM ProductAndCategoryNamesOverTenDollars
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC 

Notice that we use the CTE immediately after it has been declared.

Multiple CTEs can be defined after the WITH keyword by separating them with a comma. Each CTE can be referenced by the CTE that follows it, building off of each other. The data manipulation language (DML) statement that follows the CTE definitions can also refer to any of the CTEs defined within the WITH clause.

WITH CategoryAndNumberOfProducts

(CategoryID, CategoryName, NumberOfProducts) AS

(
SELECT
      CategoryID,
      CategoryName,
      (SELECT COUNT(1) FROM Products p
WHERE p.CategoryID = c.CategoryID) as NumberOfProducts
FROM Categories c
),
 
ProductsOverTenDollars (ProductID, CategoryID, ProductName, UnitPrice) AS
(
SELECT
      ProductID,
      CategoryID,
      ProductName,
      UnitPrice
FROM Products p
WHERE UnitPrice > 10.0
)
 
SELECT c.CategoryName, c.NumberOfProducts,
      p.ProductName, p.UnitPrice
FROM ProductsOverTenDollars p
INNER JOIN CategoryAndNumberOfProducts c ON
      p.CategoryID = c.CategoryID
ORDER BY ProductName 

Using CTE’s

There are many great uses of the CTE. We cannot list them all in this one post. Perhaps one day we will look at other examples. But one great example that I would like to share is of a particular problem I faced the other day. IT was a delete problem. Say, you have to delete some rows in your table, but they are particular rows. You only want to delete the oldest rows. A simple select order by will give you your rows, but you can’t delete them.

After many hours of checking which would be the best way, I eventually decided on using a CTE. With the CTE I can select the rows I want, order them in Desc, then use that select statement as the basis of my delete command.

Consider the following:

with oldblogs as
(

SELECT *,ROW_NUMBER() OVER (order by addeddate desc) as Rownum

from blog_comments

where entryID =1
)
deletefrom oldblogs where rownum < 10

You could also use a top 10% or top 100 or what ever. The point is that CTE’s make life a little easier. Your TSQL is readable, you can doo all sorts shenanigans inside the CTE expression and then work on that expression.

Another great use of CTE’s is finding and deleting duplicate rows. For what ever reason, you might have duplicate rows in your table. The following is an example of what you can do with a CTE to find and delete duplicate rows.

WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1

 

Tags:
Categories:
blog comments powered by Disqus

2 comment(s) so far...


Gravatar

Re: Using Common Table Expressions (CTE) in MS SQL

Good stuff Robert, some clear, useful, real-world examples there!
Of course one of the more powerful (though not as common) uses of CTEs is that they can reference themselves, which therefore allows for recursive querying - you would use this to return hierarchal datasets, for example. (I have only ever used this once in real-project scenario though)
I have seen some more inventive uses of it including a sort of concatenation-cum-pivot thing, etc., but that's another story :)

By BaldricMan on  2009/06/30 06:49 PM
Gravatar

Re: Using Common Table Expressions (CTE) in MS SQL

Baldric,

You took the words right out of my mouth. I was going to do another post on just that example. Table hierarchies, CTE recursion. There is just too much to do in one post. I normally use the adjacency list module for my hierarchies, I am going to show two different ways to one can display and access this data. Though older normal tsql methods and as you mention, with CTE. Stay tuned.

By Robert Bravery on  2009/07/01 08:05 AM
 
Blog Updates Via E-mail
 Blog Updates Via E-mail
Minimize

Do you want to receive blog updates via e-mail. Then just click on the link below. You will be redirected to Google's feed burner, where you can fill out a form. Supplying your e-mail address.

The subscription is managed entirely by Google's Feedburner. We cannot and do not collect your email address.

Subscribe to The Brave Programmer by Email

Print  
 

 

Latest Comments
 Latest Comments
Minimize
Powered by Disqus

Sign up with Disqus to enjoy a  surprise box of features

Print  
 
Blog Roll
 Blog Roll
Minimize
Print  
 
Categories
 Categories
Minimize
Print  
 
<h1>Search Blogs From The Brave Programmer</h1>
 

Search Blogs From The Brave Programmer

Minimize
Print  
 
Archive
 Archive
Minimize
Archive
<November 2017>
SunMonTueWedThuFriSat
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789
Monthly
Go
Print  
 
<h1>News Feeds (RSS)</h1>
 

News Feeds (RSS)

Minimize
Print  
 

Follow robertbravery on Twitter

Blog Engage Blog Forum and Blogging Community, Free Blog Submissions and Blog Traffic, Blog Directory, Article Submissions, Blog Traffic

View Robert Bravery's profile on LinkedIn

Mybyte

 

Robert - Find me on Bloggers.com

Tags
 Tags
Minimize
Print  
 
Contact Us Now
 Contact Us Now
Minimize
 

Email  us now or call us on 082-413-1420,  to host your website.

We design and develop websites. We develop websites that make a difference. We do Dotnetnuke Module development.

Web Masters Around The World
Power By Ringsurf
Print