Professional Web Applications Themes

mssql, duplicate code, and the dearth of correlation - Microsoft SQL / MS SQL Server

#1 on my wishlist for future mssql support: The ability to correlate on either a) table-based udfs, or b) parametrized views. I would even be happy with c) some sort of t-sql macro preprocessor or possibly even d) the ability to return multiple fields from a subquery, or e) aggregate function support for udfs If you are experienced and wise enough to understand the relationship between these seemingly disparate feature requests, then I salute you. The problem is that I have huge amounts of duplicate code because of these limitations, most of which could be eliminated if 1 or more ...

  1. #1

    Default mssql, duplicate code, and the dearth of correlation

    #1 on my wishlist for future mssql support:

    The ability to correlate on either

    a) table-based udfs, or
    b) parametrized views.

    I would even be happy with

    c) some sort of t-sql macro preprocessor

    or possibly even

    d) the ability to return multiple fields from a subquery, or
    e) aggregate function support for udfs

    If you are experienced and wise enough to understand the relationship
    between these seemingly disparate feature requests, then I salute you.

    The problem is that I have huge amounts of duplicate code because of these
    limitations, most of which could be eliminated if 1 or more of the above
    were supported.

    Does anyone know if a future version will address any of these issues?

    --
    Lucas Fletcher
    [email]lucasdealersinnotions.com[/email]
    [url]http://dealersinnotions.com[/url]


    Lucas Fletcher Guest

  2. #2

    Default Re: mssql, duplicate code, and the dearth of correlation

    > The ability to correlate on either
    >
    > a) table-based udfs, or
    > b) parametrized views.
    In a correlated subquery there should be no need for a parameterized view
    since a view a subquery which can be operated on within the outer part of
    the query using any required parameters. IMO table-based UDF's are too
    easily used to implement procedural code where a simple subquery could do
    the job. I don't really see that parameterised views would add any extra
    capability. Can you give an example of the type of thing you mean?

    > c) some sort of t-sql macro preprocessor
    MS intends to integrate Yukon (the next version of SQLServer) more closely
    with .NET which may help in this regard.

    > d) the ability to return multiple fields from a subquery, or
    Definitely useful. SQL92 permits these row-value expressions as well as
    scalar expressions. I hope that feature will be included in Yukon.

    > e) aggregate function support for udfs
    Developing aggregate functions is a very complex problem which is very
    different from user-defined scalar functions. Most of the commonly sought
    after aggregates (e.g. the string-, product-, mode- and median- aggregates)
    have set-based solutions anyway. What sort of user-defined aggregate did you
    have in mind?


    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  3. #3

    Default Re: mssql, duplicate code, and the dearth of correlation


    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in message
    news:uyPQUkUQDHA.2768tk2msftngp13.phx.gbl...
    > > The ability to correlate on either
    > >
    > > a) table-based udfs, or
    > > b) parametrized views.
    >
    > In a correlated subquery there should be no need for a parameterized view
    > since a view a subquery which can be operated on within the outer part of
    > the query using any required parameters.
    Yes, the outer part but not the rest of it... Or do parametrized views only
    support horizontally partioning? (don't know much about them) If so, then is
    there a term for views that support arbitrary parameters?
    ("really-parametrized views" ? :)
    > IMO table-based UDF's are too
    > easily used to implement procedural code where a simple subquery could do
    > the job. I don't really see that parameterised views would add any extra
    > capability. Can you give an example of the type of thing you mean?
    Yeah, regular inline subqueries can always do the job, but my problem (and
    others I would imagine) is with all the duplicate code once your subqueries
    start getting complex.
    > > c) some sort of t-sql macro preprocessor
    >
    > MS intends to integrate Yukon (the next version of SQLServer) more closely
    > with .NET which may help in this regard.
    I certainly hope so. From what I saw it looks like a better way of doing
    procedural stuff like cursors, but the sql is still treated as strings so
    I'm not holding my breath...
    > > d) the ability to return multiple fields from a subquery, or
    >
    > Definitely useful. SQL92 permits these row-value expressions as well as
    > scalar expressions. I hope that feature will be included in Yukon.
    Hallelujia brother.
    > > e) aggregate function support for udfs
    >
    > Developing aggregate functions is a very complex problem which is very
    > different from user-defined scalar functions. Most of the commonly sought
    > after aggregates (e.g. the string-, product-, mode- and median-
    aggregates)
    > have set-based solutions anyway. What sort of user-defined aggregate did
    you
    > have in mind?
    What comes up often for me is the need to obtain distribution data, where
    different result set fields represent a histogram of a particular input
    field. If I have to use COUNT for this then I have to run a seperate query
    for every range in the histogram. Does this make sense?


    Lucas Fletcher Guest

  4. #4

    Default Re: mssql, duplicate code, and the dearth of correlation

    > Yes, the outer part but not the rest of it... Or do parametrized views
    only
    > support horizontally partioning? (don't know much about them) If so, then
    is
    > there a term for views that support arbitrary parameters?
    > ("really-parametrized views" ? :)
    In Access, any scalar value in a query can be parameterized. In SQL you have
    to do this by referencing a column from the outer query within the subquery,
    but yes, this can mean lots of repeated subquery code. The trouble is a
    parameterised view as a subquery is not a very set-based concept. It implies
    that a value is passed into a closed "black box" and makes it hard for the
    optimiser to do its work.
    > What comes up often for me is the need to obtain distribution data, where
    > different result set fields represent a histogram of a particular input
    > field. If I have to use COUNT for this then I have to run a seperate query
    > for every range in the histogram. Does this make sense?
    You mean like this?:
    [url]http://tinyurl.com/fx8z[/url]

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Lucas Fletcher" <lucasdealersinnotions.com> wrote in message
    news:OcslPxUQDHA.2676TK2MSFTNGP10.phx.gbl...
    >
    > "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in message
    > news:uyPQUkUQDHA.2768tk2msftngp13.phx.gbl...
    > > > The ability to correlate on either
    > > >
    > > > a) table-based udfs, or
    > > > b) parametrized views.
    > >
    > > In a correlated subquery there should be no need for a parameterized
    view
    > > since a view a subquery which can be operated on within the outer part
    of
    > > the query using any required parameters.
    >
    > Yes, the outer part but not the rest of it... Or do parametrized views
    only
    > support horizontally partioning? (don't know much about them) If so, then
    is
    > there a term for views that support arbitrary parameters?
    > ("really-parametrized views" ? :)
    >
    > > IMO table-based UDF's are too
    > > easily used to implement procedural code where a simple subquery could
    do
    > > the job. I don't really see that parameterised views would add any extra
    > > capability. Can you give an example of the type of thing you mean?
    >
    > Yeah, regular inline subqueries can always do the job, but my problem (and
    > others I would imagine) is with all the duplicate code once your
    subqueries
    > start getting complex.
    >
    > > > c) some sort of t-sql macro preprocessor
    > >
    > > MS intends to integrate Yukon (the next version of SQLServer) more
    closely
    > > with .NET which may help in this regard.
    >
    > I certainly hope so. From what I saw it looks like a better way of doing
    > procedural stuff like cursors, but the sql is still treated as strings so
    > I'm not holding my breath...
    >
    > > > d) the ability to return multiple fields from a subquery, or
    > >
    > > Definitely useful. SQL92 permits these row-value expressions as well as
    > > scalar expressions. I hope that feature will be included in Yukon.
    >
    > Hallelujia brother.
    >
    > > > e) aggregate function support for udfs
    > >
    > > Developing aggregate functions is a very complex problem which is very
    > > different from user-defined scalar functions. Most of the commonly
    sought
    > > after aggregates (e.g. the string-, product-, mode- and median-
    > aggregates)
    > > have set-based solutions anyway. What sort of user-defined aggregate did
    > you
    > > have in mind?
    >
    > What comes up often for me is the need to obtain distribution data, where
    > different result set fields represent a histogram of a particular input
    > field. If I have to use COUNT for this then I have to run a seperate query
    > for every range in the histogram. Does this make sense?
    >
    >

    David Portas Guest

  5. #5

    Default Re: mssql, duplicate code, and the dearth of correlation

    Sorry. Wrong thread.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  6. #6

    Default Re: mssql, duplicate code, and the dearth of correlation


    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in message
    news:e2HxIEVQDHA.2228tk2msftngp13.phx.gbl...
    > In Access, any scalar value in a query can be parameterized. In SQL you
    have
    > to do this by referencing a column from the outer query within the
    subquery,
    > but yes, this can mean lots of repeated subquery code. The trouble is a
    > parameterised view as a subquery is not a very set-based concept. It
    implies
    > that a value is passed into a closed "black box" and makes it hard for the
    > optimiser to do its work.
    Yeah, I guess you're right, it would be harder to optimize.

    So it seems simple macro replacement might be the best way for me to go
    about eliminating duplicate code. Are you aware of any products on the
    market that do this, and maybe one that is integrated into VS.NET?
    > You mean like this?:
    > [url]http://tinyurl.com/fx8z[/url]
    Thank you, that's a great idea. I didn't know SQL could allow one to
    exercise one's creativitiy to the extent it does. :)


    Lucas Fletcher Guest

  7. #7

    Default Re: mssql, duplicate code, and the dearth of correlation

    > e) aggregate function support for udfs

    Possibly you may get your wish:

    "Finally, DBAs or developers can write user-defined aggregates (UDAs) in
    T-SQL or VS.NET to enable defining aggregate expressions to support data
    mining and recursive queries."

    [url]http://www.fawcette.com/dotnetmag/2003_06/magazine/columns/sqlconnection/def[/url]
    ault_pf.asp


    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

Similar Threads

  1. Duplicate Movie Clip & Code?
    By Mhunki webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 2
    Last Post: February 4th, 11:34 AM
  2. Help with MSSQL/PHP
    By Darren in forum PHP Development
    Replies: 9
    Last Post: November 14th, 09:22 AM
  3. [PHP] php x MsSql
    By Curt Zirzow in forum PHP Development
    Replies: 0
    Last Post: September 25th, 03:18 AM
  4. [PHP] php with MsSql
    By Curt Zirzow in forum PHP Development
    Replies: 1
    Last Post: September 24th, 07:33 AM
  5. Need help to reduce the duplicate php code
    By Jason Dumler in forum PHP Development
    Replies: 4
    Last Post: July 5th, 05:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139