September 5, 2001 at 8:32 am
I just noticed that some stored procedure code creates temporary tables on the fly using select into #temp_table.
In my old Sybase days, this was prone to deadlocks in the system tables because it locked up the system tables for the duration of the select statement.
Is this true in SQL Server?
We have 80+ users, who run stored procedures that make heavy use of temporary tables.
Would we be less prone to deadlocks if the temporary tables where created explicitly?
September 5, 2001 at 10:00 am
In v6.5, sysobjects in tempdb would get locked and cause problems. This is les problematic in v7/2000, but you should still avoid this if there is another way to rewrite the solution.
Steve Jones
September 5, 2001 at 11:44 am
Steve - Thanks for the information.
Do you think it would still be problematic if the temp tables where created explicitly using a create statement?
September 5, 2001 at 3:26 pm
Shouldnt make a difference how you create the temp tables. If it turned out that everyone was using the same table structure, then you'd probably see a gain by creating a permanent table that included a userid column (maybe use the spid). Slightly more work up front, but you can index and set constraints which is sometimes helpful.
Andy
September 5, 2001 at 3:27 pm
never had any problems with creating temp tables explicitly , i prefer it to the select into method , as Steve pointed out this was a problem with 6.5
personally i try to use the table variable as far as possible because this is more efficient than using temp tables , but this is available only in 2000 and you're still forced to used temp tables in some cases
for e.g
insert into @table
EXEC sp_name
would'nt work so the only alternative is a temp table
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply