Select into #temp_table and deadlocks...

  • 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?

  • 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

  • Steve - Thanks for the information.

    Do you think it would still be problematic if the temp tables where created explicitly using a create statement?

  • 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.


  • 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