SQL Server – Do You Know Row Constructors?

Hello Folks,

You may have heard this name earlier. But I am sure; many of you don’t have enough information about the Row Constructor. Well don’t worry; I am going to give you some heads up:

• The Row Constructors is a new feature for SQL Server 2008.
• It provides a convenient way to supply hard-coded values directly in a subquery.
• The VALUES clause is wrapped in parenthesis, as in every hard-coded row. It requires an alias and a column alias list, also in parenthesis.
• So, the Row Constructors can be used in the FROM clause and joined just like any other type of data source.
• The following examples will make this more clear to you…This is an example which shows Simple Use of Row Constructors-

SELECT m,n
FROM
(VALUES (10,20),
(30,40),
(50,60),
(70,80),
(90,100)) AS SimpleRowConstructor(m,n)

The results can be seen as:

• We can also use the ALL clause, where it must be true for all every value. So, you can see with the example:

SELECT 'PASS' AS 'RESULT'
WHERE 32 < ALL
(SELECT a FROM
    (VALUES (33),
    (39),
    (78),
    (67),
    (81)
    ) AS ValuesResult(a)
);

The result can be seen as:

While dealing with the ALL condition, keep in mind that if there happens any value to be null, then no values being returned.

• We can use the SOME and ANY condition, which returns true if the condition is met for any values in the subquery result set. For e.g.,

SELECT 'A' AS 'GRADE'
WHERE 81 = SOME
(SELECT a FROM
    (VALUES (33),
    (39),
    (78),
    (67),
    (81)
    ) AS ValuesGrade(a)
);

The result can be seen as:

Well this was all about row constructor.

Hope you liked it 🙂

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook