Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Add support for ANSI standard row value constructors

The ANSI standards for SQL define a concept of row value constructors. These make it possible to write, for instance,

WHERE (col1, col2) NOT IN (SELECT col1, col2 FROM SomeOtherTable)

SQL Server does not currently support this constructions

33 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    unplanned  ·  AdminMicrosoft SQL Server (Admin, Microsoft Azure) responded  · 

    Upvotes: 176

    <=-=Nov 13 2007 12:37AM=-=>

    Hello

    Thank you for your feedback. We’re certainly considering row value constructors for a future release of SQL Server.

    - Sara Tahir
    Microsoft SQL Server

    <=-=Aug 11 2010 8:03AM=-=>

    I think row constructors would be a great and important addition to T-SQL. Just wanted to point out a few more cases that I’d love to see implemented:


    - Assignment
    -
    —————————————————————————————————-

    UPDATE dbo.T1
    SET (c1, c2, c3) = (@p1, @p2, @p3)
    WHERE keycol = @key;

    — Logically equivalent to:

    UPDATE dbo.T1
    SET c1 = @p1,
    c2 = @p2,
    c3 = @p3
    WHERE keycol = @key;

    — Or with a subquery:

    UPDATE dbo.T1
    SET (c1, c2, c3) = (SELECT T2.c1, T2.c2, T2.c3
    FROM T2
    WHERE T2.keycol = T1.keycol)
    WHERE keycol = @key;

    — Logically equivalent to:
    UPDATE dbo.T1
    SET c1 = (SELECT T2.c1
    FROM T2
    WHERE T2.keycol = T1.keycol),
    c2 = (SELECT T2.c2
    FROM T2
    WHERE T2.keycol = T1.keycol),
    c3 = (SELECT T2.c3
    FROM T2
    WHERE T2.keycol = T1.keycol)
    WHERE keycol = @key;


    - Comparison Predicates
    -
    —————————————————————————————————-

    — The operators: AND follow the rules for Equality operations in the standard.

    — equals operator:

    SELECT keycol, c1, c2, c3
    FROM dbo.T1
    WHERE (c1, c2, c3) = (@p1, @p2, @p3);

    — Logically equivalent to:

    SELECT keycol, c1, c2, c3
    FROM dbo.T1
    WHERE c1 = @p1
    AND c2 = @p2
    AND c3 = @p3;

    - Consider NULL comparison as TRUE
    -
    For details see: https://connect.microsoft.com/SQLServer/feedback/details/286422/add-language-and-optimizer-support-for-iso-distinct-predicate?wa=wsignin1.0
    WHERE (c1, c2, c3) IS NOT DISTINCT FROM (@p1, @p2, @p3);

    — With composite joins:

    SELECT T1.keycol AS T1_key, T2.keycol AS T2_key
    FROM dbo.T1 JOIN dbo.T2
    ON (T1.c1, T1.c2, T1.c3) = (T2.c1, T2.c2, T2.c3);

    — not equals operator:

    SELECT keycol, c1, c2, c3
    FROM dbo.T1
    WHERE (c1, c2, c3) <> (@p1, @p2, @p3);

    — Consider NULL vs. non-NULL comparison as TRUE
    WHERE (c1, c2, c3) IS DISTINCT FROM (@p1, @p2, @p3);

    — Logically equivalent to:

    SELECT keycol, c1, c2, c3
    FROM dbo.T1
    WHERE c1 <> @p1
    OR c2 <> @p2
    OR c3 <> @p3;

    — The operators: , , AND follow the rules for Ordering operations in the standard.

    — less than operator:

    SELECT keycol, c1, c2, c3
    FROM dbo.T1
    WHERE (c1, c2, c3) < (@p1, @p2, @p3);

    — Logically equivalent to:

    SELECT keycol, c1, c2, c3
    FROM dbo.T1
    WHERE (c1 < @p1)
    OR (c1 = @p1 AND c2 < @p2)
    OR (c1 = @p1 AND c2 = @p2 AND c3 < @p3);

    — greater than operator:

    SELECT keycol, c1, c2, c3
    FROM dbo.T1
    WHERE (c1, c2, c3) > (@p1, @p2, @p3);

    — Logically equivalent to:

    SELECT keycol, c1, c2, c3
    FROM dbo.T1
    WHERE (c1 > @p1)
    OR (c1 = @p1 AND c2 > @p2)
    OR (c1 = @p1 AND c2 = @p2 AND c3 > @p3);

    — less than or equals operator:

    SELECT keycol, c1, c2, c3
    FROM dbo.T1
    WHERE (c1, c2, c3) <= (@p1, @p2, @p3);

    — Logically equivalent to:
    SELECT keycol, c1, c2, c3
    FROM dbo.T1
    WHERE (c1 = @p1 AND c2 = @p2 AND c3 <= @p3)
    OR (c1 = @p1 AND c2 < @p2)
    OR (c1 < @p1);

    — greater than or equals operator:

    SELECT keycol, c1, c2, c3
    FROM dbo.T1
    WHERE (c1, c2, c3) >= (@p1, @p2, @p3);

    — Logically equivalent to:

    SELECT keycol, c1, c2, c3
    FROM dbo.T1
    WHERE (c1 = @p1 AND c2 = @p2 AND c3 >= @p3)
    OR (c1 = @p1 AND c2 > @p2)
    OR (c1 > @p1);


    - IN AND BETWEEN Predicates
    -
    —————————————————————————————————-

    — IN:

    SELECT keycol, c1, c2, c3
    FROM dbo.T1
    WHERE (c1, c2, c3) IN (SELECT c1, c2, c3
    FROM T2);

    — if the subquery returns the rows R1, R2, �, Rn, the above query is then equivalent to:

    SELECT keycol, c1, c2, c3
    FROM dbo.T1
    WHERE (c1, c2, c3) = R1
    OR (c1, c2, c3) = R2
    OR …
    OR (c1, c2, c3) = Rn;

    BETWEEN:

    SELECT keycol, c1, c2, c3
    FROM dbo.T1
    WHERE (c1, c2, c3) BETWEEN (pl1, @pl2, @pl3) AND (pr1, @pr2, @pr3) ;

    — Logically equivalent to:

    SELECT keycol, c1, c2, c3
    FROM dbo.T1
    WHERE (c1, c2, c3) >= (pl1, @pl2, @pl3) AND (c1, c2, c3) <= (pr1, @pr2, @pr3) ;


    - MIN/MAX Aggregates
    -
    —————————————————————————————————-

    SELECT grpcol, MAX
    FROM dbo.T1
    GROUP BY grp;

    — Logically equivalent to:

    WITH C AS
    (
    SELECT grpcol, c1, c2, c3,
    ROW_NUMBER() OVER AS rownum
    FROM dbo.T1
    )
    SELECT grpcol, c1, c2, c3
    FROM C
    WHERE rownum = 1;

    — with explicit ROW keyword
    SELECT grpcol, MAX)
    FROM dbo.T1
    GROUP BY grpcol;

    <=-=Aug 11 2010 8:23AM=-=>

    I like Itzik’s ideas, all but the MIN/MAX construct.

    To me MIN would behave similar to COALESCE in that it perhaps aggregates c1 & c2 & c3, then chooses the min result of the three.

    I’m also not sure how column aliases would work when row constructs are used in the SELECT statement.

    <=-=Aug 11 2010 9:15AM=-=>

    There are platforms that support scalar MIN/MAX-like aggregates called LEAST/GREATEST. Actually, it would be nice to see those in SQL Server too. But I think that the potential confusion can be resolved with an explicit use of the ROW keyword: MAX). As for aliasing, normally the construct would return the original column name, but could be nice to see such a feature allows assigning our own aliases. Something like: MAX) AS (myc1, myc2, myc3).

    <=-=Jan 1 2011 6:30PM=-=>

    There is a bit of a clunky way currently in which aggregates can be used across columns.

    SELECT [name]
    ,[number]
    ,(SELECT MAX FROM (SELECT low AS n UNION ALL SELECT high) d) n
    FROM [master].[dbo].[spt_values]

    <=-=Mar 12 2012 8:51AM=-=>

    Is this still a feature under consideration? It’s been 4.5 years since this request was created, and there are duplicates of this request too.

    <=-=Nov 8 2012 11:34AM=-=>

    Having used these in ORACLE over EIGHT YEARS AGO it’s stunning that this has been on the very distant back-burner for OVER FIVE YEARS.

    Would Microsoft care to update loyal community members on the status of this request, please?

    (So many highly useful T-SQL functions could have been added at the expense of ruining SSMS in SQL2012 – just observing…)

    <=-=Jan 4 2013 8:17AM=-=>

    Any idea if we will get this in SQL 2012 or any of the SP of that?

    <=-=Feb 20 2013 9:14PM=-=>

    Can we have an update on this suggestion please?

    Echo Phil Brammer, really like Itzik’s suggestions below. Although rather than messing with the well established MIN/MAX aggregate functions, I would suggest adding a new functions for evaluating the MIN or MAX over a value list (not set) e.g. MIN_VAL(val1, val2, val3), whereas MIN would work as per Itzik’s suggestion.

    <=-=Apr 2 2015 2:25PM=-=>

    What is the status of this suggestion?

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Peter Larsson commented  ·   ·  Flag as inappropriate

        Meanwhile, you can use this type of code to do the same thing.

        WHERE EXISTS (SELECT t.col1, t.col2 EXCEPT SELECT q.col2, q.col2 FROM dbo.SomeOtherTable AS q)

      Feedback and Knowledge Base