[T-SQL] Please provide temporary views - by Jamie Thomson

Status : 

  Duplicate<br /><br />
		This item appears to be a duplicate of another existing Connect or internal item.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 714617 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 12/19/2011 5:38:00 AM
Access Restriction Public


A previous suggestion for temporary views (i.e. CREATE VIEW #ViewName AS ...) here: https://connect.microsoft.com/SQLServer/feedback/details/640863/please-allow-creation-of-temporary-views has been closed with the justification that a workaround is to CREATE a view in tempdb and then DROP it later.

In my opinion this is a very poor justification. Would you make the same argument for temporary tables? Of course you wouldn't, because they already exist in T-SQL. 

Temporary views would be very useful where the definition of a SQL statement would be utilised multiple times in a stored proc but would not be required *outside* of the stored proc. If temporay tables are considered useful (as they clearly are - because they exist) then the same should apply to temporary views.
Sign in to post a comment.
Posted by EricLeBouffon on 7/13/2016 at 7:29 AM
Of course it would be very useful...
Posted by Dmitry Otblesk on 9/20/2013 at 6:32 PM
Temporary views (or temporary table functions) would be extremely useful.

The suggestion
"...use perhaps table variable or temporary table for reusing query expression across different statements..."
does not work because temporary table is not a view, this is container of data. Whereas view can be used many times to retrieve DIFFERENT data every time by using, for example, different Where clauses in each call.
The suggestion
"... why can't you just create a permanent view..."
also does not work as the whole point of temporary views is the ability to create different views depending, for example, on input parameters. So permanent view, which always has the same code, would not help.

Module-level query expressions may help as well. But they are not supported either... Are the going to be supported at least by SQL 2014?
Posted by Microsoft on 1/24/2012 at 12:54 PM
Hello Jamie,
I resolved your request as duplicate of the module level table expresions item.

Umachandar, SQL Programmability Team
Posted by Jamie Thomson on 1/24/2012 at 12:56 AM
Hi UC,
Thanks, as always, for the detailed reply.
I think I've talked above and elsewhere (in a place that you frequent) about why I would prefer temporary views rather than maintaining a seperate view so I won't divulge again here (in a word - clutter).

Module-level query expressions (http://connect.microsoft.com/SQLServer/feedback/details/343067/module-level-table-expressions) would be fine. I don't care about what the syntax is here (CREATE VIEW #... or DECLARE @query AS (SELECT-statement) ) ... I'm really not fussy. Both would provide what I'm after so I'm hoping module-level query expressions arrive one day.

In fact, I think you should resolve this submission as effectively being a duplicate of http://connect.microsoft.com/SQLServer/feedback/details/343067/module-level-table-expressions


Posted by Microsoft on 1/23/2012 at 4:40 PM
Hello Jamie,
Thanks for your feedback. We have no plans to extend the temporary objects to support other types than what we have today. So we won't add the ability to create temporary view. Temporary objects binding is a mess and if we want databases to be fully contained, having more constructs with different object resolution semantics makes it harder. It is also confusing in terms of usage - for example, temporary table reference can bind to object created outside the current context too like batch or calling SP.
If you want temporary views then use perhaps table variable or temporary table for reusing query expression across different statements. This will give more control actually since views are always in-lined in query expressions today. CTE will also work if you reuse query expression multiple times within a statement. Lastly, if the query expression is static then why can't you just create a permanent view? The cost of maintaining a separate object vs. specifying the DDL (CREATE VIEW #... definition) inside a procedure is miniscule. And I would argue that creating a separate object is better - creating more type of objects on the fly is not an easy to use or understand feature. Also, once we support CREATE VIEW for temporary objects then we need to worry about CHECK OPTION, indexing of view or restrict them. Either way it is lot of work for little benefit.
Having said this, we are open to extending the language to support module-level query expressions. This is something we explored in the past but there are other high-priority requests that we need to address first. Our approach in general has been to add primitive features that are hard to work around or alternatives provide poor performance or harder to use syntax. Hope this helps.

Umachandar, SQL Programmability Team
Posted by Valentino Vranken on 12/20/2011 at 10:35 AM
Jamie, I agree with your reasoning of using them in a stored proc, one more up vote! I would think it shouldn't be too much trouble to implement, as temp tables already exist, but that's just my opinion of course...