Difference between Table variable and Temporary Table

Question

Accepted Answer

Table variables are created in memory and are scoped to the batch, while temporary tables are stored in tempdb and can be accessed across batches.

Table variables are created using DECLARE statement, while temporary tables are created using CREATE TABLE statement.
Table variables are limited to the scope of the batch or stored procedure in which they are declared, while temporary tables can be accessed across batches.
Table variables are automatically deallocated when the batch or stored procedure ends, while temporary tables need to be explicitly dropped or are dropped when the session ends.
Table variables are not logged in the transaction log, while temporary tables are logged in the transaction log.
Table variables are generally preferred for smaller datasets, while temporary tables are preferred for larger datasets or when indexes need to be created.

Top GormalOne Full Stack Software Developer interview questions & answers