Add support for ANSI standard row value constructors by Hugo Kornelis

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(col1, col2, col3) would work as per Itzik's suggestion.

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

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

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.

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

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

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(ROW(c1, c2, c3)). 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(ROW(c1, c2, c3)) AS (myc1, myc2, myc3).

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

To me MIN(c1,c2,c3) 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.

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: <equals operator> AND <not equals operator> 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: <less than operator>, <greater than operator>, <less than or equals operator> AND <greater than or equals operator> 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(c1, c2, c3)
FROM dbo.T1
GROUP BY grp;

-- Logically equivalent to:

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

-- with explicit ROW keyword
SELECT grpcol, MAX(ROW(c1, c2, c3))
FROM dbo.T1
GROUP BY grpcol;

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