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 assignments, I came across a code that ran for an unexpectedly long time. Intrigued, I decided to investigate how it was written by the developer. The code aimed to generate a common value with another value as a CROSS JOIN. The desired output looked like this:
| Measurement | Multiplier | Unit |
|---|---|---|
| kilobyte | 10^3 | byte |
| megabyte | 10^6 | byte |
| gigabyte | 10^9 | byte |
| terabyte | 10^12 | byte |
| kilometer | 10^3 | meter |
| megameter | 10^6 | meter |
| gigameter | 10^9 | meter |
| terameter | 10^12 | meter |
This can be achieved 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 [Measurement], [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] )
I know it is a pretty unconventional approach, but it still does the job for us.
Now, here’s a challenge for you: Is there any other way to achieve the same result using SQL Server in a single query? I know CTE is one of the other ways too. So try something else now. I would love to hear your thoughts and solutions in the comments below.
Additionally, if you have used any other implementations of the row-constructor in your environment, I would be interested to hear about them. Sharing your experiences and implementations will not only benefit the blog readers but also provide me with valuable insights.
Thank you for joining me on this journey of exploring SQL Server. I look forward to your interesting implementations and the learning experiences that lie ahead.