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

Hello Jamie,
I resolved your request as duplicate of the module level table expresions item.

--
Umachandar, SQL Programmability Team

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

regards
Jamie

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

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