Quastion about Temp Tables

I'm relatively new to SQL-Server, and I have a quick question about
temporary tables. I am using a function from Erland Sommarskog's
excellent article about arrays in SQL server.

Erland's approach uses a user-defined function, which parses a string
of delimited values (integers) into a temporary table. The temporary
table is joined to tables in a stored procedure select statement to
limit the records returned.

The question is, what happens to the temporary table generated by the
function? Is it implicitly destroyed when the stored procedure
finishes? Does it need to be dropped explicitly (something not done in
his examples)?

Also, what are the implications of scope, if multiple users are
accessing the stored procedure through a web application?

Thanks in advance,

TimA temporary table created within a stored procedure is automatically dropped
when the procedure exists.

On 12 Feb 2004 12:59:53 -0800, tim.pascoe@.cciw.ca (Tim Pascoe) wrote:

>I'm relatively new to SQL-Server, and I have a quick question about
>temporary tables. I am using a function from Erland Sommarskog's
>excellent article about arrays in SQL server.
>Erland's approach uses a user-defined function, which parses a string
>of delimited values (integers) into a temporary table. The temporary
>table is joined to tables in a stored procedure select statement to
>limit the records returned.
>The question is, what happens to the temporary table generated by the
>function? Is it implicitly destroyed when the stored procedure
>finishes? Does it need to be dropped explicitly (something not done in
>his examples)?
>Also, what are the implications of scope, if multiple users are
>accessing the stored procedure through a web application?
>Thanks in advance,
>Tim|||[posted and mailed, please reply in news]

Tim Pascoe (tim.pascoe@.cciw.ca) writes:
> I'm relatively new to SQL-Server, and I have a quick question about
> temporary tables. I am using a function from Erland Sommarskog's
> excellent article about arrays in SQL server.

Glad to hear that you liked it!

> Erland's approach uses a user-defined function, which parses a string
> of delimited values (integers) into a temporary table. The temporary
> table is joined to tables in a stored procedure select statement to
> limit the records returned.
> The question is, what happens to the temporary table generated by the
> function? Is it implicitly destroyed when the stored procedure
> finishes? Does it need to be dropped explicitly (something not done in
> his examples)?

If you are using the version with the user-defined function, the table
is never stored on disk (at least conceptually), so you don't have to
bother.

If you are using the version where a stored procedure fills in a temp
table that was created prior to the call, that table is stored in disk.
However, as Steve J pointed out, a temp table disappears when the scope
it was created in goes away, so neither in this case you need to worry.

> Also, what are the implications of scope, if multiple users are
> accessing the stored procedure through a web application?

There are no issues with this. A temp table of the # variety are private
to the connection that created it. This applies as well to the return
table created by multi-step function.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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