temporary recordset in sql not stored in disk mem

[dpimental]

All, some time ago, someone showed me a way to create some sort of collection / dataset / table that was not a temp table but some sort of collection that was gone the moment a query / proc was finished.

Anyone know what that is?

David Pimental
(US, Oh)

 

[bborissov]

Table VARIABLE

DECLARE @Test TABLE (field list and types here)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

In addition to Borislav's reply may be you're talking about Common Table Expressions (CTE).

 

[dpimental]

I don't think that was it. But I'll try that.

I think it was some sort of disconnected recordset that allowed to select into it, use it in a join against the same or different tables and then when you were done, it didn't exist any more.

But, I'll try your technique.

David Pimental
(US, Oh)

 

[dpimental]

yes, that's it.

The infamous common table expression.

Thanks to both of you!

David Pimental
(US, Oh)

 

[bborissov]

Table variable did exactly what you want.
You can use it as a normal table anywhere in your query and after batch is finished it disappears.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[TysonLPrice]

Here's a good link on what you can and can't do using table variables versus normal tables and temporary tables.

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html