Search

Module-level table expressions by Erland Sommarskog

Active

35
3
Sign in
 to vote
Type: Suggestion
ID: 343067
Opened: 5/9/2008 2:13:21 PM
Access Restriction: Public
0
Workaround(s)
There are some situations where you perform a lot of operations on a very
well-defined subset of a table in an SQL module. A typical case is a process-
keyed table. That is a table that is keyed by @@spid or some other process
key. see http://www.sommarskog.se/share_data.html#prockeyed for a longer
discussion. If you have a procedure that operates on such table, the
procedure may have a bunch of:

SELECT/UPDATE/DELETE ... WHERE processkey = @processkey

This is somewhat error-prone. If the procedure has 20 such operations,
you may fail to observe that the condition on processkey is missing in
one of the queries. So it would be a great improvement you could
package this WHERE clause somehow.

Views cannot do the job, since there is a paramerer. Inline table
functions could, but then you still need to repeat that parameter name
through the code.

Therefore a new feature is needed, one where you can declare a
query in the beginning of your module and work on in throughout
the procedure.

The suggested syntax is:

DECLARE @query AS (SELECT-statement)

You can then refer to @query throughout your module. @query would
work as a macro, and would be reevaluated every time, as would any
variables or expressions in the SELECT statement. The SELECT statement
could be about anything you can put in a view. It should of course be
possible to use a CTE in the SELECT-statement.

As for what to call the features, I originally thought of them as "view
variables" in analogy with table variables, but some of my MVP colleagues
found that name misleading. Maybe Module Query Expressions?

I attach two procedures as an example. originalcode.sp is the original
code for a procedure that I wrote recenly. viewvariable.sp is a
rewritten version where I have employed this suggested feature.
The reduction in number of lines of code is modest below 10%. But
the "view variable" ensures that I emply the conditions on xmlreceiveid
and status throughout.

Obviously, this suggestion is for the release after SQL 2008.
Details (expand)
Product Language
English

Category

SQL Engine

Proposed Solution

See above.

Benefits

Improved Reliability
Faster Development

Other Benefits

 
File Attachments
File Name Submitted By Submitted On File Size  
originalcode.sp (restricted) Erland Sommarskog 5/9/2008 -
viewvariable.sp (restricted) Erland Sommarskog 5/9/2008 -
Sign in to post a comment.
Posted by Luís Patrício on 4/28/2009 at 4:59 AM
Instead of enablinf module level CTEs, there is the possibility of allowing the creation of temporary views, just like a temporary table or stored procedures.
Posted by Ranga1 on 3/24/2009 at 7:23 AM
I am very interested if there is a way to have a similar approach like CTE but extend it to a batch or module level construct.

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=425605
Posted by sdassin on 8/18/2008 at 3:20 AM
The answer has been around for about forty years:) The sql cte is the pale
attempt at assignment, which is the assignment of a table/expression to
a 'variable' in the same way an integer value is assigned to a one. This is the
foundation of a 'relational' system. But sql cannot realize a peristed table as a
variable so your limited to a statment. In sql one thinks of a 'macro' instead of
a variable definition since there is no alternative. I wonder how many sql server
team members have C.J. Date on their bookshelves:) I use Dataphor (in conjunction
with sql server) to assign a table definition to a variable that can be used anywhere
and can be modified (this is distinctly different than a view).
Do you know what the Sql CTE is?
http://beyondsql.blogspot.com/2007/10/sql-whats-really-with-with.html
All tables are typed variables
http://beyondsql.blogspot.com/2007/09/dataphor-all-tables-are-typed-variables.html

To think of all the work put into LINQ could have been for a truly relational (object) system :(:)
Posted by Erland Sommarskog on 5/15/2008 at 1:34 PM
Again, I'm sorry if my use of "macro" confused you and other people. I used "macro", beause for me a view, a CTE or an inline-table function is a macro in SQL Server. The algebrizer expands the expression with the definition, and the query optimizer works with the expanded query.

This suggestion is definitely not intended to break new ground, but is very much intended to align with the current features: view, CTE and inline-function. You could say that my suggestions bridges the gap between the database-scoped views/functions and the statement-scoped CTEs.

The example with @sessionRows was not mine; that objection was raised by Steve Kass, and I don't think it's valid. If you have a "view variabale", "declared view", "local view", "batch-level CTE" or whatever we want to call it, which refers to a table T, and you then have a query that goes:

    WITH T AS (SELECT ...)
    SELECT ... FROM @myview

@myview would expand to something that refers to the table T, not the CTE T, just like a database-scoped view would do. The example highlights a problem with CTEs: there may be a name clash with an existing object. Notice that this problem does not really exist with my proposal, since I suggest that the module-level CTE would have a leading @ in the name.

We have had some discussion on the topic in our internal MVP forum, and a lot of it has centered by the name. I tend to favour the name "view variable", to align with "table variable". But I certainly do intend the view definition of the view to be variable; you cannot redefine the bacth-scoped view/CTE at run-time. But the result set defined by the view could change as tables or scalar variables referred by the view/CTE are updated.

If you prefer to continue the discussion over e-mail, drop me a mail. I don't have your address, so I can't drop you one. :-)
Posted by Microsoft on 5/14/2008 at 3:27 PM
Hi Erland,

Thankyou for this suggestion. I understand the issue. But I'd love to devise a solution that extends existing constructs, rather than invent a new one. So, if CTEs or Table-Valued Functions, for example, don't quite do what we need, perhaps we could extend their operation to cover.

The approach of inventing macros seems enticing. But we run into similar binding issues that you highlight with the @sessionRows example: T is not bound via parameter (technically it's a 'free' variable), so it binds to whichever T is in-scope at the callsite; which is error-prone (kinda like old LISP's dynamic scoping - clamber up-stack at runtime and grab first match!) Macros are powerfull, but again, lack control over their binding (at least, for conventional C-like syntactic macros).

I've added this to our list of candidate features for next release, so it's on our radar.

Perhaps we could exchange ideas in email about alternative approaches?

Thanks again,

Jim
Posted by rince0000 on 5/12/2008 at 12:57 PM
Can someone enumerate the reasons why CTE's are scoped only a statement level? I have a feeling that may explain why something of this nature (the specific feedback item) has not yet been implemented.

Posted by MatthewRoche on 5/10/2008 at 8:54 AM
I think this functionality would be very useful, but personally I would prefer to see it implemented as a CTE that was scoped to the batch and not to the statement, rather than as a variable.
Posted by Erland Sommarskog on 5/10/2008 at 2:31 AM
Just to make it clear, when I say "macro", I mean "macro" in the same sense
that a view, inline-table function, CTE is a macro. That is, it's expanded by the
algebrizer and the query processor sees very little difference.

And a more elaborate syntax would be:

DECLARE @query [(column-list)] AS (SELECT-statement)

to align with the definition of views and CTEs.
Posted by Steve Kass on 5/9/2008 at 7:48 PM
I browsed the SQL-2003 standard to see if there was something like this, and nothing jumped out. That makes me suspect there's a good reason. In some respects, this feature could be like Dynamic SQL, only worse.

Macro-like abilities are very handy, but they can also be tricky to implement and define. Perhaps you would learn to use the feature effectively, but the parser/preprocessor/optimizer would have to handle whatever this feature allowed, and the results might be surprising. For example (and I think there are many examples), suppose you began a long procedure with

declare @sessionRows query as
    select T.id, T.c
    from T
    where T.spid = @@spid;

If @sessionRows is expanded before query execution, these two seemingly-identical statements are completely different:

-- Expression A
with T1 as (
select spid, id, c
from Nisse
where status = 'Censored'
)
update Trades set
    details = '<removed>'
where exists (
    select * from T1
    join @sessionRows as S
    on S.id = T1.id
    where T1.id = Trades.id
)

-- Expression B
with T as (
select spid, id, c
from Nisse
where status = 'Censored'
)
update Trades set
    details = '<removed>'
where exists (
    select * from T
    join @sessionRows as S
    on S.id = T.id
    where T.id = Trades.id
)

So you might say define special name-resolution rules for query variables so that the column specification T.id inside the declaration of @sessionRows would never resolve to the CTE in Expression B. But then what if someone forgot the T. alias here or there in the declaration of @sessionRows. Then you might further decide you needed to require two-part column specifications in query declarations.

What kind of dependencies would have to be maintained for a procedure that declares and refers to a query variable?

The list goes on, I'm afraid. I'm not saying it can't be done, but the implications are much deeper than they seem at first.

SK
Sign in to post a workaround.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement