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?
Lots of things end up with names like BILLING-APP-DATABASE-PROD-VM-02 these long names get cut off in most of the grids.
So update the grid layouts based on the names, and squeeze the other columns to display long names.