Advertisements
Home > SQL Server Questions > Cannot use Temp Table and Table Variable in Views, why? – MSDN TSQL forum

Cannot use Temp Table and Table Variable in Views, why? – MSDN TSQL forum


–> 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.


Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: