Creating a common table expression--temporary table--using TSQL??? -- sql server (alternate)

Steve Jorgensen
12/28/2004 3:30:44 PM

Yes, but I think he wanted to associate the statement with the name, a
physical table - like a temporary view.

[quoted text, click to view]

John Gilson
12/28/2004 4:44:04 PM

[quoted text, click to view]

Hi Hugo, a common table expression, provided by the WITH
clause, is defined in Standard SQL (beginning with SQL:1999)
and is implemented in SQL Server 2005. Semantically, the WITH
clause is similar to defining one or more views whose scope and
extent is the enclosed query. Factoring out and naming these
common subexpressions in a query is meant to aid readability,
conciseness, maintainability, and even efficiency. There are cases
when a derived table is a perfectly good alternative, however,
when that derived table is used multiple times in the query a
common table expression becomes handy.

Taking the BOL example from above, imagine you wanted to
rank stores in decreasing order by number of distinct titles. Using
WITH, one could write (admittedly, in this case a view is a reasonable
choice too):

WITH DistinctTitles (stor_id, title_count) AS
(SELECT stor_id, COUNT(DISTINCT title_id)
FROM sales
GROUP BY stor_id)
SELECT T1.stor_id, T1.title_count,
COUNT(DISTINCT T2.title_count) AS stor_rank
FROM DistinctTitles AS T1
INNER JOIN
DistinctTitles AS T2
ON T2.title_count >= T1.title_count
GROUP BY T1.stor_id, T1.title_count;

It's also through the WITH clause that we can define recursive queries. This
is where WITH truly shines.

--
JAG

Hugo Kornelis
12/28/2004 4:51:50 PM

[quoted text, click to view]

Hi Randi,

I don't know if it's exactly the same as the DB2 version (probably not),
but SQL Server supports derived table expressions. Example (from BOL):

USE pubs
GO
SELECT ST.stor_id, ST.stor_name
FROM stores AS ST,
(SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
FROM sales
GROUP BY stor_id
) AS SA
WHERE ST.stor_id = SA.stor_id
AND SA.title_count = (SELECT COUNT(*) FROM titles)

Best, Hugo
--

Hugo Kornelis
12/28/2004 9:30:28 PM

Hi John,

Thanks for your explanation. I had heard that WITH would be introduced in
SQL Server 2005; unfortunately, I'll have to wait a little longer before
I'll get a chance to actually play with it. (I don't have a spare system
lying around that I can use to safely toy with beta software).

It does look promising, though. I'm sure I'll really get to like this
feature once I have it available!

Best, Hugo
--

Steve Jorgensen
12/29/2004 8:21:35 AM

[quoted text, click to view]

Just curious - can the scope of a WITH be more than one query? An entire

John Gilson
12/29/2004 8:40:27 AM

[quoted text, click to view]

No, a WITH clause encloses a single query expression and can be used
anywhere a query is used, e.g., in defining a view.

--
JAG

Steve Jorgensen
12/29/2004 8:48:00 AM

[quoted text, click to view]

Darn - I thought this would finally be a tool for removing SQL code
duplication within stored procedures. Does SQL Server 2005 offer some other

John Gilson
12/29/2004 9:13:23 AM

[quoted text, click to view]

The idea behind the common table expression in a WITH clause is that
it doesn't act like a macro but is instead evaluated to a virtual table
that is used in each place where it's referenced in the enclosed query.
So in a stored procedure one might use a temp table or table variable
to store an intermediate result in lieu of such an animal. Nothing exciting
I'm afraid.

--
JAG

Erland Sommarskog
12/29/2004 10:35:35 PM

John Gilson (jag@acm.org) writes:
[quoted text, click to view]

Nah, the current implementation appears to be quite macro-like, at least
for non-recursive queries.

When I look at the query plan for the query below, the CTE is computed
many times. For this query a temp table or a table variable would be
a much better alternative.

CREATE TABLE prodreport (id int NOT NULL,
product1 int NOT NULL,
product2 int NULL,
product3 int NULL,
CONSTRAINT pk_report PRIMARY KEY(id))
go
INSERT prodreport (id, product1)
SELECT PurchaseOrderID, MIN(ProductID)
FROM AdventureWorks.Purchasing.PurchaseOrderDetail
GROUP BY PurchaseOrderID
go
-- This is the query of the show.
WITH temp (id, productid, rowno) AS
(SELECT PurchaseOrderID, ProductID,
rowno = (SELECT COUNT(*)
FROM AdventureWorks.Purchasing.PurchaseOrderDetail p2
WHERE p1.PurchaseOrderID = p2.PurchaseOrderID
AND p1.ProductID >= p2.ProductID)
FROM AdventureWorks.Purchasing.PurchaseOrderDetail p1)
UPDATE prodreport
SET product1 = t1.productid,
product2 = t2.productid,
product3 = t3.productid
FROM prodreport r
JOIN temp t1 ON t1.id = r.id
AND t1.rowno = 1
LEFT JOIN temp t2 ON t2.id = r.id
AND t2.rowno = 2
LEFT JOIN temp t3 ON t3.id = r.id
AND t3.rowno = 3

SELECT * FROM prodreport
go
DROP TABLE prodreport
go

[quoted text, click to view]
the query in this way:

WITH top_3_prods(id, productid, rowno) AS
(
SELECT PurchaseOrderID, ProductID,
ROW_NUMBER() OVER(PARTITION BY PurchaseOrderId
ORDER BY ProductID)
FROM AdventureWorks.Purchasing.PurchaseOrderDetail p1
) ,
pvt_top_3_prods (id, product1, product2, product3) AS
(
SELECT id, [1], [2], [3]
FROM top_3_prods
PIVOT (min(ProductId) for rowno in ( [1], [2], [3] )) as pv
)
UPDATE prodreport
SET product1 = t1.product1,
product2 = t1.product2,
product3 = t1.product3
FROM prodreport r
JOIN pvt_top_3_prods t1 ON t1.id = r.id

There's a whole fireworks of new T-SQL features in that one!

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

sql server (alternate) : Creating a common table expression--temporary table--using TSQL???