SQL with Manoj

–> Question:

I Know we cannot use Temp table, Table variable in View, but I want to know the reason behind it?
 

–> My Answer:

Because View is nothing but a simple stored query. A View definition just contains one single query, which can pull data from one or many tables by using JOINS. Every time a View is executed it is expanded upto the Query level and that Query is executed by the SQL Engine internally every time.

It is not like Stored Procedures where you have bunch of queries with control flow with loops, and you create temp-tables/variable for temporary storage. If you would like to add temp data use Stored Procedures instead of Views.

Views are just used to simplify complex queries usage, and saves us to re-write same code/query again and again.
 

–> Another Answer by Oalf:

a local temporary table is only available in the session where it has been created and only as long as the session exists, as soon as you close the session the temp table will be automatically dropped; so how should it work, creating a view on a temp table which exists for only a may very short period and at all, what should that be good for?
 

–> Another Answer by Erland:

There is certainly a good reason for views not supporting table variables. Table variables are only visible in the batch they are created in, and the CREATE VIEW statement must be in a batch of its own, so the table variable never exist when the CREATE VIEW statement runs.

For temp tables, it would certainly be possible. The view would become invalid as soon the scope where the temp table is creates is exited, but then again tables can be dropped to. However, there are some interesting questions.
 

Ref Link.