Temp Table cache

When you write code that it’s using temp table, the key point here is the ability to cache the object.

When an object(temp table) it’s cached , then DROP TABLE statement, behind , will rename the table and next CREATE TABLE will bring back the right name.

There are some points that will inhibit , the caching:

• a named constraints
• a DDL statement after the initial create statement
• have with Recompile

 

Some myths about caching:

• DROP TABLE  will not prevent
• UPDATE STATISTICS will not prevent
• TRUNCATE TABLE also , will not prevent

But – manual creation of statistics will do prevent

See more here

 

Now let’s test:

What should we test ?

• DROP TABLE statement vs no DROP TABLE statement
• altering table vs creating DDL from start
• adding index vs DDL statement with inline index

 

S