SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Temporary Tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 01/11/2001 :  11:13:34  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Sophie writes "Can you use a Stored Procedure to open a table and copy data to a sort of virtual table (or a records set) so that you can change the values with and not affect the actual data in the actual table. And then return the results of the virtual table? Thanks!"

Article Link.

Anonymous
Starting Member

0 Posts

Posted - 01/23/2001 :  04:09:47  Show Profile  Reply with Quote
if any temporary table(sessiion level) in ORACLE

sir, I am working as a SQLSERVER dba. i have to know any temporary

table in ORACLE

in sqlserver we have optino #tablename;

thanks

gurunathan .m
kguruji@usa.net

Go to Top of Page

tomfoolry
Starting Member

Canada
2 Posts

Posted - 10/02/2001 :  01:10:09  Show Profile  Visit tomfoolry's Homepage  Reply with Quote
quote:

Graz, please tell me how because the reason for my coming here tonight was to find the solution for that exact problem.

I've already created the procedure.

I have done exactly what you are suggesting!
It works when i use query analyzer to test it but it doesn't work when i execute the procedure from a remote location.

If you know why this is i would appreciate your help.

Thanks, Lloyd Cormier.

Sophie writes "Can you use a Stored Procedure to open a table and copy data to a sort of virtual table (or a records set) so that you can change the values with and not affect the actual data in the actual table. And then return the results of the virtual table? Thanks!"<P>Article <a href="/item.asp?ItemID=2029">Link</a>.

Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 10/02/2001 :  10:08:24  Show Profile  Visit graz's Homepage  Reply with Quote
What do you mean "Execute it from a remote location?"

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

tomfoolry
Starting Member

Canada
2 Posts

Posted - 10/02/2001 :  14:03:32  Show Profile  Visit tomfoolry's Homepage  Reply with Quote
quote:

I mean i have created a client application that connects to the server from another computer over the internet. When i use ado to execute the stored procedure in the client, it doesn't return anything. I'm thinking it is because in the timeframe it would take to transfer the contents of the temp table to the remote client, the temp table has already been dropped on the server.
And so it retrieves an empty table.

What do you think?

Thanks, Lloyd Cormier

Go to Top of Page

izaltsman
A custom title

USA
1139 Posts

Posted - 10/02/2001 :  14:38:39  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
If you haven't closed the connection, the temp table is not gonna get dropped. Your problem is most likely caused by the "Records Affected" messages that your stored procedure sends back to the client (ADO sees them as closed recordsets). So... If you can change your stored procedure, add SET NOCOUNT ON statement at the beginning and SET NOCOUNT OFF at the end of it. This will ensure that rowcounts don't get sent back to the client.
If you aren't allowed to change the stored procedure, you can attack this problem from the client side. Use NextRecordset method of your recordset in a loop, until you find an open recordset.

Edited by - izaltsman on 10/02/2001 14:40:10
Go to Top of Page

tinks
Starting Member

United Kingdom
34 Posts

Posted - 02/06/2002 :  04:27:16  Show Profile  Visit tinks's Homepage  Send tinks an ICQ Message  Reply with Quote
A question a collegue asked me...

We have a temp table that holds data for either 1 or many clients. Retrieval of data in permanent tables which we link to the temp table is slow in certain cases and we wish to add an index to the temp table (initial tests have show a large performance increase with the index!), however the temp table is referred to in stored procedures.

The temp table is created in our front end application (therefore outside of the scope of the stored procedure)

If an index is created on the temp table will the stored procedure use this when executing. I know that execution plans are created at the creation time of the stored procedure to speed up retrieval / processing time but seen as the temp table and the temp tables index will differ each time how does SQL deal with it?

Some options that I have rattling in my brain is the use of 'with recompile' as a stored procedure option and create the index outside of the sproc or will using named indexes override the need for this.


Taryn-Vee
@>-'-,---
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 02/06/2002 :  10:09:00  Show Profile  Visit nr's Homepage  Reply with Quote
With v7+ the SPs are compiled at first run not at creation time. Creation just performs syntax checks
With v7+ if a temp table is accessed within a stored procedure but not created in it then the SP will be recompiled on each run.
The SP cannot guarantee the structure of the temp table so has to be recompiled on each run.
Given this all the SPs should use the index if useful.
Note that as all the SPs that use the table will be recompiled on each run then this will degrrade performance and you could end up with contention on system tables.


==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

ProEdge
Yak Posting Veteran

USA
64 Posts

Posted - 03/03/2005 :  15:08:24  Show Profile  Reply with Quote
I have a very similar problem to this except I'd like the temporary table to hold some copied data, then give the user the option to edit it (using the datalist control in ASP.NET), then update the data in the temporary table, and when ready, have them submit it to a final table. Does anyone know if I could do this all in one stored procedure or would I have to have this done in separate ones? Is this even possible? Any help would greatly be appreciated.
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 02/13/2008 :  20:59:05  Show Profile  Reply with Quote
Hi Bill,

Great article... but you may want to update the article a bit because I think there's a couple of misperceptions on your part... You wrote (and I mean NO disrespect)...

quote:
If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory. The code above using a table variable might look like this:



The red part is what I have an issue with... please read the following Microsoft article on Temp Tables and Table Variables paying particular attention to Q3/A3 and Q4/A4...

http://support.microsoft.com/default.aspx?scid=kb en-us 305977&Product=sql2k

... considering that table variables cannot be created using a SELECT/INTO, they cannot be made to use statistics, they cannot be made to use non-constraint/keyed indexes, they cannot be referenced in nested scope, and the fact that they spool to disk (TempDB) if they get too big, one has to ask why you said they are more flexible than Temp Tables.

Further, Temp Tables also live in memory, just like table variables, until they too get too big at which time they spool to TempDB. They also allow creation using SELECT/INTO, do have statistics, and can use non-key indexes AND constraints. Still further, they "persist" in a QUERY ANALYZER or SSMS session (unlike table variables) so that you can do ad-hoc selects from them for troubleshooting and other analysis and they can be accessed in nested scope.

What about all the recompiles that Temp tables cause? Well, apparently, that's a bloody myth. They state in the following URL...

http://blogs.msdn.com/sqlprogrammability/archive/2007/01/18/11-0-temporary-tables-table-variables-and-recompiles.aspx

... the following... (I've highlighted the "Myth Buster" part...)

When the stored procedure DemoProc1 is compiled, the insert and select query are not compiled. This is because during initial compilation, the temporary table does not exist and the compilation of this query is deferred until execution time. A compiled plan for the stored procedure is generated, but is incomplete. At execution time, the temporary table is created, and the select and insert statement are compiled. Since the stored procedure is already in execution, this compilation of the select and insert query are classified as a recompilation. It is important to note that in SQL Server 2005, only the select and insert statement in the stored procedure are recompiled. In SQL Server 2000, the entire stored procedure is recompiled. Subsequent re-executions of this stored procedure do not result in any more recompiles since the compiled plan is cached. Notice that even though the temporary table is re-created each time the stored procedure is executed, we do not recompile the stored procedure each time. This is because the temporary table is referenced in the plan by name and not by ID if they are created in the same module. Since the temporary table is re-created each time with the same name, the same compiled plan is re-used. Now consider a case when the temporary table is referenced in a second stored procedure as below:

The beauty of the article (MSDN Blog, really) is that they have all the code to backup what they say.

To me, the only advantage a Table Variable has over a Temp table, is that a UDF cannot use a Temp Table. If they removed that particular advantage, I'd never use a Table variable...

... of course, I could be wrong...

Thanks for "listening". Again, I've not meant any disrespect... this is a great forum with a great "core" of people and you're one of the best.

--Jeff Moden
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 02/14/2008 :  04:55:55  Show Profile  Visit spirit1's Homepage  Reply with Quote
table variables are good to have for small sets of data that all fit in the memory.
if the data in the table variable exceeds the available memory size is will get flushed to the tempdb.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 02/14/2008 :  08:24:15  Show Profile  Visit graz's Homepage  Reply with Quote
Jeff,

Interesting points. I mostly agree with what you're saying. The situation is certainly better now with 2005 than it was with 2000. I'll put that article in my queue to get updated.

-Bill

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 02/14/2008 :  23:23:06  Show Profile  Reply with Quote
quote:
Originally posted by spirit1

table variables are good to have for small sets of data that all fit in the memory.
if the data in the table variable exceeds the available memory size is will get flushed to the tempdb.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out



Heh... agreed... the following is also true...

Temp Tables are good to have for small sets of data that all fit in the memory.
if the data in the Temp Table exceeds the available memory size is will get flushed to the tempdb.



--Jeff Moden
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 02/14/2008 :  23:25:20  Show Profile  Reply with Quote
quote:
Originally posted by graz

Jeff,

Interesting points. I mostly agree with what you're saying. The situation is certainly better now with 2005 than it was with 2000. I'll put that article in my queue to get updated.

-Bill

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.



Outstanding... thanks for your consideration, Bill.

--Jeff Moden
Go to Top of Page

Wodzu
Yak Posting Veteran

58 Posts

Posted - 09/09/2009 :  08:33:23  Show Profile  Reply with Quote
quote:
Originally posted by graz

Jeff,

Interesting points. I mostly agree with what you're saying. The situation is certainly better now with 2005 than it was with 2000. I'll put that article in my queue to get updated.

-Bill

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.



Bill, you still haven't upgraded your article in the part which was marked red by Jeff ;-)

Anyway, very nice article. Thanks for your effort.
Go to Top of Page

Postalus_Michaelus
Starting Member

USA
1 Posts

Posted - 05/02/2010 :  11:24:30  Show Profile  Reply with Quote
Such a simple article and it gets the point across correctly.

I'm able to see why some temp table objects in a script I'm debugging aren't going to work and I can rapidly do something about it. This presentation of information allows me to find this as a top link, get my question answered and get something done.

Keep up the good work.

Postalus_Michaelus
From the City of
Cincinnatus
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.53 seconds. Powered By: Snitz Forums 2000