Contrary to popular belief, table variables can and do write to disk. SQL 2005 and above perform compilation at the statement level so if only one statement utilizes a temp table then that statement is the only one that gets recompiled. This was more of an issue with SQL 2000 when compilation was performed at the procedure level instead of the statement level. The downfall to temp tables is that they will frequently cause recompilation. Generally, temp tables perform better in situations where an index is needed. Here is a quick example from my earlier article SQL SERVER Insert Data From One Table to Another Table INSERT INTO SELECT SELECT INTO TABLE. You can read about the same over here in books online Inserting Rows by Using SELECT INTO.

To do so, just add the clustered reserved word. CTAS stands for ‘Create Table As Select’. You can also create the equivalent of a clustered index. One potential way to circumvent this however, is to create a composite unique constraint: The drawback is that the indexes (or constraints) need to be unique. The primary key will represent a clustered index, while the unique constraint a non clustered index. Creating an index on a table variable can be done implicitly within the declaration of the table variable by defining a primary key and creating unique constraints. You can create a new table by using the CREATE TABLE SQL statement.

Sql server create table