Why cannot we create a temp table in a view

This question is asked by interviewer can we create a temp table in view. If not then why and what are the alternate of it. So the Answer will be no. We can not use temp table local or global table.

A view is a relational expression that the optimizer must be able to inline, reorder, and reason about. Temp tables introduce procedural state and side effects, which makes optimization and correctness impossible so SQL Server forbids them.

A view is not executed like a stored procedure. A view is a stored SELECT expression that is merged into the calling query by the optimizer. A temp table is not a relational expression it is procedural state.

See the below example

CREATE VIEW vTest AS BEGIN    CREATE TABLE #T (x INT);    INSERT INTO #T VALUES (1);    SELECT * FROM #T; END

Getting the below error.

Let’s see the view with global temp table.

CREATE TABLE ##T (x INT);    INSERT INTO ##T VALUES (1);    SELECT * FROM ##T;

See the record in the table

Let’s use this table in other session.

We are getting the value.

Now using the Global table in the view.

CREATE VIEW vTest AS   SELECT * FROM ##T;

Getting error.

So we cannot use temp table in View.

Instead of using temp table in view we can use CTE. If it is bit complex the we will go for the SP.