Table Datatype vs local and global temp tables

[vituja]

Hi All,

Have some basic questions about temp and table datatype.

I've just started using the datatype in a Stored Procedure that is called from a web page, and it seems to be working fine but I have one nagging questions that need some clearification.

Lets say two hits to the SP come in at the same time, what will happen? With the table datatype, will SQLServer do all the locking and create seperate sessions for each request?

I've tried this in our test environment and there seems to be a no issue. So does this mean each hit to the SP create a seperate table unique to that session automatically?

 

[SQLSister]

Yes each table variable is local to the Stored Procedure session just like temp tables. If you create an actual table in the database to use for a temp situation, this will have contention problems.

Questions about posting. See faq183-874

 

[vituja]

Thanks SQLSister for the info. We had instances when a pure temp table was used (##TEMP_TBL) and there were times when two or more users acessed the SP which caused the temp table to exhibit blank rows.

Temp tables in SQL2000 do not seem to be created in an isolated session. Of course I couldn't find that in the documentation but I came along to this new data type, table.

My manager wants to know will the same problem we experienced with ##Temp_Tables be resolved using the table datatype. My researh states that session locking is done automatically. Does this mean a seperate instance of that table is created each time the SP is called?

 

[SQLSister]

## are global temp tables, they are by definition accesible outside the scope of teh sp. If you want a temp table to be limited to the scope of an sp use one that starts with only one # sign. However table variables are more efficient anyway.

From BOL:

A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.

So yes it is limited in scope to the session of the sp which is running.

Questions about posting. See faq183-874

 

[JefB]

Related to the above:

My SP works using a temp table (#tmpTLS) and I can display the data in QA but if I try using the temp table in a recordset in an .asp I get an error message saying that the object is not recognized.

I start in an .asp that displays the data from #tmpTLS in a standard database result. I then have a hyperlink that goes to a page that displays as a spreadsheet. The code I use is:

>>
...
sqlStr = "SELECT Protocol, Market, AlphaInvestigator, Tot_Calls, Tot_DQCalls, Tot_IRCalls, Tot_ApptSet from #tmpTLS"
set rsTLS = myConnection.Execute(sqlStr)
...
<<

Any suggestions?

JefB

 

[SQLSister]

No you only use #temp tables in a stored procedure. Then when you want the results in your ASP program they are just the recordset returned by the stored procedure (yes, you do select * from #temp as the last line of the sp).

The resaon you put all the steps in the one place is that the temp table is probably out of scope at the time you call it dynamically from your application. Stored Procedures are a better programming practice anyway than writing SQL code directly in the application.

Questions about posting. See faq183-874

 

[vituja]

I agree with SQLSister. We use asp (version 3) extensively as well. All if not most of our SQL calls are made through Stored Procedures. The only exception is when a simple select statement (select * from tbl order by).