Thursday, February 10, 2011

TSQL Constraints on Temp Tables

Very quick and simple question. I am running a script to import data and have declared a temp table and applied check constraints to that table. Obviously if the script is run more than once I check whether the temp table already exists and if so, I drop and recreate the temp table. would that also drop and recreate the check constraints I placed on the temp table?

Logic says yes but I have been known to be wrong about such things.

  • Yes - dropping the temp table will drop any internal dependencies as well, including your constraints.

    From rwmnau
  • Yes, dropping the table will drop constraints, indexes etc that you may have created on it.

    Also, if this is something you're doing repeatedly you may want to truncate it instead of drop/recreate it - that is typically a lot faster. (And sometimes, creating a "permanent" temp table can be a good idea for frequently occuring ops)

    FailBoy : thanks for the tip, but this is generally a once off when migrating clients to our software from other vendors software

0 comments:

Post a Comment