Creating a common table expression--temporary table--using TSQL???

[randi_clausen]

Using SQL against a DB2 table the 'with' key word is used to
dynamically create a temporary table with an SQL statement that is
retained for the duration of that SQL statement.
What is the equivalent to the SQL 'with' using TSQL? If there is not
one, what is the TSQL solution to creating a temporary table that is
associated with an SQL statement? Examples would be appreciated.
Thank you!!

[Steve Jorgensen]

On 28 Dec 2004 07:07:49 -0800, ra***********@ins.state.il.us wrote:

Using SQL against a DB2 table the 'with' key word is used to
dynamically create a temporary table with an SQL statement that is
retained for the duration of that SQL statement.
What is the equivalent to the SQL 'with' using TSQL? If there is not
one, what is the TSQL solution to creating a temporary table that is
associated with an SQL statement? Examples would be appreciated.
Thank you!!

I believe there is such a thing in SQL Server 2005, but not in any earlier
versions.

[Paul]

You did not say what version of MSSQL you are on so I will assume 2000.
This is straight from the TSQL books.

Temporary Tables
SQL Server supports temporary tables. These tables have names that
start with a number sign (#). If a temporary table is not dropped when
a user disconnects, SQL Server automatically drops the temporary table.
Temporary tables are not stored in the current database; they are
stored in the tempdb system database.
There are two types of temporary tables:
Local temporary tables
The names of these tables begin with one number sign (#). These tables
are visible only to the connection that created them.
Global temporary tables
The names of these tables begin with two number signs (##). These
tables are visible to all connections. If the tables are not dropped
explicitly before the connection that created them disconnects, they
are dropped as soon as all other tasks stop referencing them. No new
tasks can reference a global temporary table after the connection that
created it disconnects. The association between a task and a table is
always dropped when the current statement completes executing;
therefore, global temporary tables are usually dropped soon after the
connection that created them disconnects.
Many traditional uses of temporary tables can now be replaced with
variables that have the table data type.


Example
create table #TempTable (col1 varchar(10), col2 bit)
insert into #TempTable values('asdf', 1)
select * from #TempTable
select * into #TempTable2 from #TempTable
select * from #TempTable2
drop table #TempTable
drop table #TempTable2
You can just about anything with a temp table that you can with a
normal table, including indexes.
HTH
Paul

[Steve Jorgensen]

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

On 28 Dec 2004 07:19:13 -0800, "Paul" <st*******@yahoo.com> wrote:

You did not say what version of MSSQL you are on so I will assume 2000.
This is straight from the TSQL books.

Temporary Tables
SQL Server supports temporary tables. These tables have names that
start with a number sign (#). If a temporary table is not dropped when
a user disconnects, SQL Server automatically drops the temporary table.
Temporary tables are not stored in the current database; they are
stored in the tempdb system database.
There are two types of temporary tables:
Local temporary tables
The names of these tables begin with one number sign (#). These tables
are visible only to the connection that created them.
Global temporary tables
The names of these tables begin with two number signs (##). These
tables are visible to all connections. If the tables are not dropped
explicitly before the connection that created them disconnects, they
are dropped as soon as all other tasks stop referencing them. No new
tasks can reference a global temporary table after the connection that
created it disconnects. The association between a task and a table is
always dropped when the current statement completes executing;
therefore, global temporary tables are usually dropped soon after the
connection that created them disconnects.
Many traditional uses of temporary tables can now be replaced with
variables that have the table data type.


Example
create table #TempTable (col1 varchar(10), col2 bit)
insert into #TempTable values('asdf', 1)
select * from #TempTable
select * into #TempTable2 from #TempTable
select * from #TempTable2
drop table #TempTable
drop table #TempTable2
You can just about anything with a temp table that you can with a
normal table, including indexes.
HTH
Paul

[Hugo Kornelis]

On 28 Dec 2004 07:07:49 -0800, ra***********@ins.state.il.us wrote:

Using SQL against a DB2 table the 'with' key word is used to
dynamically create a temporary table with an SQL statement that is
retained for the duration of that SQL statement.
What is the equivalent to the SQL 'with' using TSQL? If there is not
one, what is the TSQL solution to creating a temporary table that is
associated with an SQL statement? Examples would be appreciated.
Thank you!!

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
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[John Gilson]

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:eu********************************@4ax.com...

On 28 Dec 2004 07:07:49 -0800, ra***********@ins.state.il.us wrote:

Using SQL against a DB2 table the 'with' key word is used to
dynamically create a temporary table with an SQL statement that is
retained for the duration of that SQL statement.
What is the equivalent to the SQL 'with' using TSQL? If there is not
one, what is the TSQL solution to creating a temporary table that is
associated with an SQL statement? Examples would be appreciated.
Thank you!!

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
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

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]

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
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Steve Jorgensen]

On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <ja*@acm.org> wrote:

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:eu********************************@4ax.com.. .

On 28 Dec 2004 07:07:49 -0800, ra***********@ins.state.il.us wrote:

>Using SQL against a DB2 table the 'with' key word is used to
>dynamically create a temporary table with an SQL statement that is
>retained for the duration of that SQL statement.
>What is the equivalent to the SQL 'with' using TSQL? If there is not
>one, what is the TSQL solution to creating a temporary table that is
>associated with an SQL statement? Examples would be appreciated.
>Thank you!!

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
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

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.

Just curious - can the scope of a WITH be more than one query? An entire
stored procedure, for instance?

[John Gilson]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:h8********************************@4ax.com...

On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <ja*@acm.org> wrote:

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:eu********************************@4ax.com.. .

On 28 Dec 2004 07:07:49 -0800, ra***********@ins.state.il.us wrote:

>Using SQL against a DB2 table the 'with' key word is used to
>dynamically create a temporary table with an SQL statement that is
>retained for the duration of that SQL statement.
>What is the equivalent to the SQL 'with' using TSQL? If there is not
>one, what is the TSQL solution to creating a temporary table that is
>associated with an SQL statement? Examples would be appreciated.
>Thank you!!

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
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

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.

Just curious - can the scope of a WITH be more than one query? An entire
stored procedure, for instance?

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]

On Wed, 29 Dec 2004 08:40:27 GMT, "John Gilson" <ja*@acm.org> wrote:

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:h8********************************@4ax.com.. .

On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <ja*@acm.org> wrote:

>"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
>news:eu********************************@4ax.com.. .
>> On 28 Dec 2004 07:07:49 -0800, ra***********@ins.state.il.us wrote:
>>
>> >Using SQL against a DB2 table the 'with' key word is used to
>> >dynamically create a temporary table with an SQL statement that is
>> >retained for the duration of that SQL statement.
>> >What is the equivalent to the SQL 'with' using TSQL? If there is not
>> >one, what is the TSQL solution to creating a temporary table that is
>> >associated with an SQL statement? Examples would be appreciated.
>> >Thank you!!
>>
>> 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
>> --
>>
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
>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.

Just curious - can the scope of a WITH be more than one query? An entire
stored procedure, for instance?

No, a WITH clause encloses a single query expression and can be used
anywhere a query is used, e.g., in defining a 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
new feature to do this?

[John Gilson]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:5k********************************@4ax.com...

On Wed, 29 Dec 2004 08:40:27 GMT, "John Gilson" <ja*@acm.org> wrote:

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:h8********************************@4ax.com.. .

On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <ja*@acm.org> wrote:

>"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
>news:eu********************************@4ax.com.. .
>> On 28 Dec 2004 07:07:49 -0800, ra***********@ins.state.il.us wrote:
>>
>> >Using SQL against a DB2 table the 'with' key word is used to
>> >dynamically create a temporary table with an SQL statement that is
>> >retained for the duration of that SQL statement.
>> >What is the equivalent to the SQL 'with' using TSQL? If there is not
>> >one, what is the TSQL solution to creating a temporary table that is
>> >associated with an SQL statement? Examples would be appreciated.
>> >Thank you!!
>>
>> 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
>> --
>>
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
>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.

Just curious - can the scope of a WITH be more than one query? An entire
stored procedure, for instance?

No, a WITH clause encloses a single query expression and can be used
anywhere a query is used, e.g., in defining a 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
new feature to do this?

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]

John Gilson (ja*@acm.org) writes:

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.

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

I should that Umachandar Jaychandran, a former SQL Server MVP, rewrote
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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp