What are the main differences between #temp tables and @table variables and which one is preferred ?

1. SQL Server can create column statistics on #temp tables
2. Indexes can be created on #temp tables
3. @table variables are stored in memory up to a certain threshold.

For many other question and more information on temporary table and variable.

Temporary tables are a useful tool in SQL Server provided to allow for short term use of data. These are like other User table where we can store data but only for temporary purpose. These temporary tables data, structure and related objects like Indexes keys etc are automatically gets drop by SQL Server, once the user get disconnect from the Server.

These temporary tables are like other tables in SQL Server where you can

• Add/drop constraints except foreign key
• You can perform DDL statements (Alter, Drop)
• Create clustered and non-clustered indexes
• Use identity columns
• Use it in transaction and it support transaction
• Perform any DML operations (SELECT, INSERT, UPDATE, DELETE)
• Create the table with same name using different session; make sure constraint name must be different in the table.

Temporary tables are similar to temporary Tables but with following restrictions

• You can not create non-cluster index and statistics on table variable but you can create it on temporary table.
• You can not use DDL statement on table variable but you can use it on temporary table.
• Table variable doesn’t support transaction whereas temporary table supports.