Consider using table variable instead of temporary table

These are much easier to work with, are pretty secure, and they trigger fewer recompiles in the routines where they’re used than if you were to use temporary tables.

Available in

SQL Prompt

SQL Code Guard

There are many factors involved in choosing whether to use a temporary table or a table variable, and a few simple ‘rules’ to follow, to get the best performance. Assuming you follow the basic rules-of-engagement, then you should consider table variables as a first choice when working with relatively small data sets. They are easier to work with and they trigger fewer recompiles in the routines in which they’re used, compared to using temporary tables. Table variables also require fewer locking resources as they are ‘private’ to the process and batch that created them.

See Choosing Between Table Variables and Temporary Tables for more details.

ST012:
If you are doing more complex processing on temporary data, or need to use more than reasonably small amounts of data in them, then local temporary tables are likely to be a better choice than table variables.

See Choosing Between Table Variables and Temporary Tables for more details.

Learn more about temporary tables.