Home
Dashboard
Directory
Help
Sign in
SQL Server Home
Downloads
Feedback
Surveys
Search
Feedback
All Connect
Add support for ANSI standard row value constructors
by
Hugo Kornelis
Status:
Active
189
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
Product Language
English
Category
SQL Engine
Proposed Solution
Add support for row value constructors to T-SQL.
Benefits
Other (please provides details below)
Improved User Interface
Faster Development
Other Benefits
Adherence to standards; Improved portability
Comments (9)
Workarounds (0)
Attachments (0)
Sign in
to post a comment.
Please enter 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.
Please enter a workaround.
Please wait...