SQL Question
What is CTE, Temp table and Temp Variable?

Question

SQL Question

What is CTE, Temp table and Temp Variable?

Accepted Answer

CTE stands for Common Table Expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike the temporary table, its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries.

Temp tables : In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside the Tempdb database. Based on the scope and behavior temporary tables are of two types : Local temp table and Global temp table

Temp variable : It acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of the batch. This is also created in the tempdb database but not the memory. This also allows you to create a primary key, identity at the time of Table variable declaration but not non-clustered index.

Top PolicyBazaar Senior Database Developer interview questions & answers

Popular interview questions of Senior Database Developer