Every version of SQL Server brings in something new that challenges our understanding of how the software product works. For me, working with SQL Server is quite refreshing and fulfilling because every single day there is something about the product that I discover and learn. There are tons of professionals who are working on this product and they push the limits of use and bring interesting use cases which I get to learn. Let us learn about Row Constructor.
This journey of SQL Server on this blog is just my way to express all these learnings and get them documented. In a recent code review for one of my consulting assignment, I saw a code and was completely taken aback at how it even ran for such a long time.
I had to go ahead and do my homework to see how this was actually written by the developer. Assume you wanted to have some code that gave you a common value with something other value as a CROSS JOIN. The final output was looking like:
As you can see the “Kilo”, “Mega” “Giga” are repeated for Byte and Meter values. This can be done in a number of ways, but the code I wrote to get this is using the row-constructor feature of SQL Server.
SELECT [MetricsPre].[Pre] + [Measuring].[Unit] AS [Measuringment], [MetricsPre].[Multiplier], UPPER([Measuring].[Unit])
FROM (VALUES ('kilo', '10^3'),
('mega', '10^6'),
('giga', '10^9'),
('tera', '10^12')) AS [MetricsPre] ( [Pre], [Multiplier] ),
(VALUES ('byte'),
('meter')) AS [Measuring] ( [Unit] )
GO
I know it is pretty unconventional, but it still does the job for us.
Test your TSQL Skills:
- Is there any other way one can achieve the same result using SQL Server in a single query? Do let me know via comments below. I know CTE is one of the other ways too. So try something else now.
- Is there any other implementation of row-constructor that you have used in your environment that you want to share with the blog readers? Do let me know via comments.
I look forward for some interesting implementations for you, and it is going to be a learning that I will cherish and look forward to.
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
1) Other way to achieve same result can like those values are already stored in table,then join is use to retrieve desire resultset.
2) Advantage of Row Constructor : i) makes code less verbose ii) more readable iii) and easier to maintain iv) It can be use to store small and fix value like Gender,Status etc. v) It can be use to generate lot of rows for Testing purpose (Test Data) vi) It is use In famous split string function of Jeff Moden .
. My usual example is the Student’s T-distribution, since I used to be a statistician. It is used for small sample sizes that the normal distribution cannot handle. It takes two parameters, the sample size and confidence interval (how sure do you want to be about your prediction).
The probability density function is defiend with an integral . Got any idea just off the top of your head how to write this in T-SQL? How many of you can identify the Greek letters in this thing? Me neither. the nice part about using this in the real world is that you don’t need all the possible values. You work with a set of three to ten confidence intervals and since it is meant for small samples, you don’t need a lot of population values.
Go cut and pasted from Wikipedia.com into a row constructor and you are done.
They’re like table value parameters, except they’re implicitly typed and have a horrifying syntax.
I’d rather declare a TVP and then use the row constructor to populate it.
I suppose it’s a matter of preference.
I recently tried using this as a technique to work around SQL Server’s INSERT limitations, which only allow 1000 rows of VALUES per statement.
The good news is, it works. I used the Row Constructor to create 18,000 new rows of data with a single “INSERT INTO / SELECT FROM” statement.
The bad news is, it is EXTREMELY slow at that scale. It took 7 mins and 30 seconds to insert all 18,000 rows.
However, using standard INSERT statements (broken down into batches of 1000) is much faster. By using 18 different INSERT statements (and not using the Row Constructor as you’ve shown here) I can insert all 18,000 records in just 10 seconds.
So, beware the performance considerations!