Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    11

    Unanswered: Select into temporary tables

    on sql-server-performance.com i read :
    Do not create temporary tables from within a stored procedure that is invoked by the INSERT INTO EXECUTE statement. If you do, locks on the syscolumns, sysobjects, and sysindexes tables in the TEMPDB database will be created, blocking others from using the TEMPDB database, which can significantly affect performance. [6.5, 7.0, 2000] Added 9-1-2000

    I have a question does this negative effect also include simple SQL commands apart from stored procedures.
    For example if from vb i execute a "Select into" temporary table. Will this have the same negative impact as with executing this from a stored procedure ?

    Thank you very much

  2. #2
    Join Date
    Dec 2003
    Posts
    31
    Yes,
    if you work with SQL 2000 use the Table data type variable
    it match faster then temporary tables

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes, table local variables are cool...but you can't SELECT INTO Them...

    I belive the reason they're saying that is because it establishes a lock for the entire lenght of ht e transaction...

    Just create the table first then do INSERT INTO TABLE SELECT...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •