Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2012
    Posts
    5

    Unhappy Unanswered: Drop command issues

    Hi,
    I am using a Stored procedure in which I am creating a table and droping that table at the end of execution. This SP is calling every 10 second (but no concurrent access) from my application. Is there any issue using the drop table command in the SP? will it create any memeory fragmentation issue in SQL server?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should be using a temporary table, not a standard table.
    With a temporary table it is good policy to drop it at the end of your code, but is not necessary. The table will disappear when the procedure completes.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2012
    Posts
    5

    drop command

    Using standard table

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Stop.
    Use a temporary table (prefixed with "#").
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Mar 2012
    Posts
    5

    drop command

    But I found that performance is better using the physical table than # table.
    Do u know the reason for this?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There should be no appreciable difference between the performance of a temporary table and a dynamically created permanent table on SQL Server.
    Oracle is a different matter.
    If necessary, you can index your temporary table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman View Post
    There should be no appreciable difference between the performance of a temporary table and a dynamically created permanent table on SQL Server.
    Oracle is a different matter.
    If necessary, you can index your temporary table.
    well......that's not really true....the answer is, it depends

    post the sproc
    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.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sure, it depends. If tempdb is on a crappy drive, then that could affect performance.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

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