SQL tip #3: consider a table variable

As an alternative, consider table variables. Of course, table variables (preceded by “@”, like @table) have their own set of special needs and are ‘private’, so only the process that creates them can access them. But they are really fast on smaller data loads (generally less than 2500 rows) and they lead to far fewer recompiles on your queries. They also get disposed of after an execution, so they clean up after themselves quite nicely. Table variables also go quite nicely with Table-Valued Parameters when you are working with sp_executesql. As the Dynamic SQL statements are generated in a different context than the source statement you will need to be clever in passing data into the Dynamic SQL statement. The reading bit is easily done with an INSERT INTO @table (xx) EXECUTE sp_executesql @SQL.

Try a table variable next time you want to use a temporary storage space in a query, or see if you have queries with a bad mixture of recompile counts and temporary tables and see if you can rework them.

‍