Home Dashboard Directory Help
Search

Add support for ANSI standard row value constructors by Hugo Kornelis


Status: 

Active


184
2
Sign in
to vote
Type: Suggestion
ID: 299231
Opened: 9/22/2007 2:26:29 PM
Access Restriction: Public
Duplicates: 127024 173788 262284 288772 598364 624533
0
Workaround(s)
view

Description

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
Details
Sign in to post a comment.
Posted by Adam Tappis on 2/20/2013 at 9:14 PM
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.
Posted by Rakesh Mishra on 1/4/2013 at 8:17 AM
Any idea if we will get this in SQL 2012 or any of the SP of that?
Posted by SAinCA on 11/8/2012 at 11:34 AM
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...)
Posted by AdamAnt on 3/12/2012 at 8:51 AM
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.
Posted by Martin Smith on 1/1/2011 at 6:30 PM
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]
Posted by Itzik Ben-Gan on 8/11/2010 at 9:15 AM
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).
Posted by Phil Brammer on 8/11/2010 at 8:23 AM
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.
Posted by Itzik Ben-Gan on 8/11/2010 at 8:03 AM
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;
Posted by Microsoft on 11/13/2007 at 12:37 AM
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
Sign in to post a workaround.