Code doesn’t tend to generate emotions in the same way that a painting might. The architecture of a piece of software doesn’t make us marvel in the same way as the architecture of a building. As a consultant I see plenty of examples of code that make me react with “No… that can’t be intentional…” or “Why on earth would they…?”, that’s not really the kind of thing Erik Darling (@erikdarlingdata) is going for in this month’s T-SQL Tuesday topic. He’s more interested in the former. Code that we admire, feel inspired by, all that.
The emotion I’m going to reminisce about is that feeling of “Oh, just imagine the possibilities…”, and relates to the SQL Server 2005 release, nearly 20 years ago.
To set the scene, I’d been working with database since the late 90s. I hadn’t done the database subject at university (yes, there was only one, and it was simple, and everyone said it was easy marks, and I just wasn’t interested) – I’d been more interested in subjects like Machine Learning, Neural Networks, or even the one about real-world modelling, where we did things like flocking behaviour, modelling the behaviour of a bird in flight, or a cyclist in a pack, and then expanded on that to see how these things would respond in a group. Fascinating stuff. I enjoyed Natural Language Processing, and still find a curiosity in how different languages work. There was a parallel to programming languages with Programming Paradigms, where we studied different approaches, such as logic programming or functional programming. We used languages like LISP and Prolog. I digress, but the point was that I hadn’t got into relational databases at uni. My first proper database work was on day one of my consulting job.
One of the things I learned from uni was about taking a small idea and imagining how it could apply in a whole range of situations. I was never going to take a piece of marble and imagine Michelangelo’s David, or consider a brick and picture the Palace at Westminster. But the building blocks of code was different. I appreciated the SQL patterns (using PL/SQL for Oracle and T-SQL for SQL Server), at my earliest clients, using SQL Server 6.0 and Oracle 7.3.4, and over time got to know query plans and started to see the relationship between the queries I was writing, the indexes that were in place, the statistics, and the subsequent plans that would come from my choices.
And then the features of SQL Server 2005 started to be shown.
SQL Server 2005 was released, of course, in 2006, and I had been running the Adelaide SQL Server User Group since September 2005. Information about the new features had been coming through, and I’d been at TechEd Australia 2005 – my first since 1999. I was still an application developer at the time (well, a manager, but still getting my hands dirty), but saw the data as the most important part of my applications. When the T-SQL enhancements in SQL Server 2005 came through, there were two things that caught my eye (I know they were available in Oracle before SQL Server, but I was focusing more on the Microsoft platform by then). They were the OVER clause, and APPLY.
The presenter showed brief examples of them, and they immediately struck me as amazing building blocks.
The OVER clause meant that I could do aggregates without having to join to a separate sub-query, which was handy in itself, but when it combined with the new ranking functions, so much more opened up. The simple example like this:
|
1 2 |
SELECT *, ROW_NUMBER() OVER (ORDER BY CreateDate DESC) as rownum FROM ... |
…well, it blew me away. Work that I had previously done outside the database, relying on being able to process rows in order, could now be done within the database, and I could be so much more creative with this amazing feature. And the awful query plans I’d produced when trying to do something similar got replaced with tight, elegant ones, letting me solve things in a single pass of the data for the first time.
And APPLY gave me so much as well. The base form was to allow functions to be called using values from an outer set. Like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
FROM dbo.Table1 t CROSS APPLY dbo.Function(t.Col1, t.Col2) f [sql] <!-- /wp:shortcode --> <!-- wp:paragraph --> <p>But when they said it could also be used with sub-queries, like this next sample, which is the same as doing a regular join, and was dismissed by the presenter as not being as significant as using it for calling functions.</p> <!-- /wp:paragraph --> <!-- wp:shortcode --> [sql] FROM dbo.Table1 t CROSS APPLY ( SELECT * FROM dbo.Table2 t2 WHERE t1.Col1 = t.Col1 ) t2 |
But for me, the sub-query example was when my eyes went wide and I wanted to start trying ideas. I could see the potential for using it with TOP 1 to get the whole row instead of joining to a sub-query using GROUP BY and MAX. I could see the potential for creating aliased expressions in APPLY clauses that I could reference in the WHERE clause. I think the idea of using it for unpivotting came later, after I’d explored it a little more, but the seed was sown and I still use it (and give presentations about it) regularly today.
Reading code is like looking at the buildings in a city you’re visiting for the first time. You see a lot of ordinary stuff, but every so often you see something done in a different way and you want to admire it. I don’t design buildings, so the admiration of city architecture only goes so far. They don’t inspire me to creativity. Seeing code though, particularly demo sessions about upcoming features, can often light something up inside me and get me exploring the possibilities.
I know that reading books will help you spell beter. Similarly, reading code helps you write better code. This is not new advice, and most coders don’t tend to do it. Not unless we’re troubleshooting someone’s code, doing a review, or some other thing that’s part of our job. But find opportunities to look at code. Learn methods for doing things. Get those building blocks appearing in your mind, and explore ways to incorporate them into your toolkit for writing better code going forward. It doesn’t matter what language you’re using, explore what people are doing. You’ll see good and bad, but either way, you’ll be learning.
@robfarley.com@bluesky (previously @rob_farley@twitter)
