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:

1_SQL_Server_Do_You_Know_Row_Constructors

  • 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:

2_SQL_Server_Do_You_Know_Row_Constructors

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:

3_SQL_Server_Do_You_Know_Row_Constructors

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

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

3 Comments on “SQL Server – Do You Know Row Constructors?”

  1. Not sure why have you mixed up ANY,SOME and ALL clauses with Row construtors.These clauses are not Row construtor dependent.

  2. @Sarab

    The reason I raised this up was because the blog content might give an impression to a novice that all these clauses are only row constructor dependent.

    Anyways no issues…

Leave a Reply to Sachin.Nandanwar Cancel reply

Your email address will not be published. Required fields are marked *