Itzik Ben-Gan

My feedback

  1. 220 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    4 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Itzik Ben-Gan commented  · 

    Packing intervals with RPR:

    SELECT MR.username, MR.starttime, MR.endtime
    FROM dbo.Sessions
    MATCH_RECOGNIZE
    (
    PARTITION BY username
    ORDER BY starttime, endtime, id
    MEASURES FIRST(starttime) AS starttime, MAX(endtime) AS endtime
    PATTERN (A* B)
    DEFINE A AS MAX(A.endtime) >= NEXT(A.starttime)
    ) AS MR;

    Itzik Ben-Gan commented  · 

    Correction:

    The DEFINE clause defines row pattern variables representing the different subsequences of rows in the pattern. In the above example A represents any row as a starting point, B represents a subsequence of decreasing prices (B.price < PREV(B.price)), and C represents a subsequence of increasing prices (C AS C.price > PREV(C.price)).
    The PATTERN clause uses regular expressions to identify a pattern. In the above query the pattern is (A B+ C+), meaning (any row, followed by one or more rows with decreasing prices, followed by one or more rows with increasing prices).

    Itzik Ben-Gan shared this idea  · 
  2. 111 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    4 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 278

    <=-=Feb 1 2008 7:18PM=-=>

    Thanks for the valuable suggestion.

    This seems more like adding the sequence support which we’re seriously considering for the next major release.

    Meanwhile, would using identity column help?

    <=-=Feb 2 2008 2:11AM=-=>

    It does not seem that you understood the request. This definitely has nothing to do with
    IDENTITY. I am less versed about sequences, but I don’t think they will cut it either. If you think
    it does, maybe you could provide an example? Take this problem: For the Orders table in
    Northwind, write a query that lists the number of orders for all days in 1997. The result set should
    include all 365 days, and list zero for days without a number.

    This is a typical problem where you need a table of of numbers (or dates). While it’s easy to
    create such a table, I argue in this request that…

    Itzik Ben-Gan supported this idea  · 
  3. 146 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    8 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 72

    <=-=Mar 10 2016 11:26AM=-=>

    It’s a shame that this was submitted as just a “suggestion”. It should actually be listed as a “bug” because there’s only a comparatively small set of use cases where enumeration of the result set of elements is not important.

    <=-=Mar 11 2016 12:47PM=-=>

    I agree that an order column is required; one example use case is where two lists are passed in, and ordinal positions in one list correspond to positions in the other.

    <=-=Mar 11 2016 3:12PM=-=>

    Please see the related suggestion: STRING_SPLIT needs “RemoveEmptyEntries” option, like String.Split in .NET ( https://connect.microsoft.com/SQLServer/feedback/details/2462002/ ).

    <=-=Mar 12 2016 12:02PM=-=>

    This kind of function is primarily needed for de-serializing previously serialized arrays of values of any type format-able as text.
    I therefore recommend to have the result set of this function work excellent with this use-case.

    With de-serialized arrays there is a need to…

    Itzik Ben-Gan supported this idea  · 
  4. 203 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    5 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 8

    <=-=Mar 5 2017 2:42PM=-=>

    Thanks for this idea. This is a valid requirement and I hope that it will get more votes. Currently we cannot confirm when it will be added, but it is in our backlog.

    <=-=May 22 2017 5:03AM=-=>

    would like it very much, particularly since you already have the CONCAT / GREATEST() a variable number of paramenters and does something with it…

    <=-=Jun 5 2017 12:31PM=-=>

    GREATEST / LEAST functions would be fantastic addition.

    <=-=Nov 14 2017 3:42PM=-=>

    The workarounds using CROSS APPLY or CASE expressions are difficult to manage and read. I’d love to see these implemented.

    Itzik Ben-Gan supported this idea  · 

Feedback and Knowledge Base