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?
With the original 125 upvotes, it would have been among top 10 requests.
Fixing this would have made partitioned tables much more powerful. Working around it manually in SQL is not impossible, but very verbose.