What are temp tables. Diff bet temp tables and CTE

Question

Accepted Answer

Temp tables are temporary tables used to store intermediate results, while CTEs are temporary result sets defined within a query.

Temp tables are physical tables that are created in the tempdb database and can be used multiple times within a session.
CTEs (Common Table Expressions) are temporary result sets that are defined within a query and can only be referenced within that query.
Temp tables persist until they are explicitly dropped or the session ends, while CTEs exist only for the duration of the query execution.
Temp tables can have indexes and statistics, while CTEs cannot.
Example of temp table: CREATE TABLE #TempTable (ID INT, Name VARCHAR(50));
Example of CTE: WITH CTE AS (SELECT * FROM Table) SELECT * FROM CTE;

Top Saksoft Senior Consultant interview questions & answers