#temp table v.s. @table
Database is all about tables. Apart from normal tables, there are two kinds of tables: Temporary tables (#temp table) and table variables (@table). We need to decide which one to use and when.
Based on Dipendra Singh Shekhawat, we use Table variable, if we have less than 1000 rows otherwise go for Temporary tables (https://www.c-sharpcorner.com/article/temporary-tables-and-table-variables-in-sql/), and the main points are
Temporary Tables are physically created in the tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.
Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. It is created in the memory database but may be pushed out to tempdb.
Sometimes, table variable is regarded as one kind of temp table along with local and global temp tables (see https://www.sqlshack.com/when-to-use-temporary-tables-vs-table-variables).
People argued which types of table offer better performance. for example, Ben Snaidero carried out a test and find that temporary table generally provides better performance than a table variable (https://www.mssqltips.com/sqlservertip/2825/sql-server-temp-table-vs-table-variable-performance-testing/).
On the other hand, Phil Factor showed that table variable could perform better (https://www.red-gate.com/hub/product-learning/sql-prompt/choosing-table-variables-temporary-tables).
Also, some others say that use a table variable if for a very small quantity of data (thousands of bytes) and use a temporary table for a lot of data (https://stackoverflow.com/questions/11857789/when-should-i-use-a-table-variable-vs-temporary-table-in-sql-server).
One insightful article by Derik Hammer points out that table variables use tempdb similar to how temporary tables use tempdb. Table variables are not in-memory constructs but can become them if you use memory optimized user defined table types (https://sqlperformance.com/2017/04/performance-myths/table-variables-in-memory)
Here is an excellent summary by Derik Hammer:
The bottom line is that some of these feature differences may change over time – for example, in recent versions of SQL Server, you can create additional indexes on a table variable using inline index syntax.