Welcome to Shop Talk, a regular Q&A-style broadcast hosted by the Triangle Area SQL Server Users Group (TriPASS).
You can watch each episode on the TriPASS Twitch channel, and get information on this show and everything else TriPASS has to offer on our Meetup.
After each episode airs, you will see the show notes as well as an embedded link below.
There was an eclipse, though the Research Triangle area didn’t have a good view of it, so Mala and I riffed on it a little bit.
Our first real topic was a great article by Marco Russo, with input from Kurt Buhler, on Direct Lake and where it actually fits in the Power BI world. I think it’s perfectly fair to talk up Direct Lake as a very nice capability, while still recognizing that it’s not a one-size-fits-all solution or a total replacement for anything currently in place.
Mala gave us the heads up on our other topic of the night, an article by David Tate on how to create a legendarily bad database. I was concerned that the advice would create a merely bad database, but I think David does a good job of taking it the extra mile and making me want to nuke the setup from orbit, because it’s the only way to be sure.
Our first topic was a bit of an interview, one that Mala put together with Bart Vernaillen, a Belgian consultant who has developed a couple tools around shredding execution plans, finding good index candidates, and so on. He showed off a bit of his work and talked about potential future plans. If you want to learn more about his tools, reach out to him on LinkedIn and say TriPASS sent you.
Microsoft has introduced a private preview of regular expression support in Azure SQL Database. Anders and I went back and forth a bit on the value of this, with Anders being strongly opposed and me being generally in favor.
We wrapped the show up with a quick discussion of why Mike really like Azure Data Studio. If you haven’t used Azure Data Studio in the past, the discussion may be of interest to you. The product isn’t for everybody—if you’re a classic DBA, you’re going to find the product more limiting than SQL Server Management Studio. But for developers or people with hybrid roles, Azure Data Studio has matured into a good tool.
Our first topic comes from Mike and is all about how to tune queries that result in large execution plans. I went through a couple of techniques but mostly focused on using Solarwinds Plan Explorer. This free tool is fantastic for analyzing SQL Server queries and I talk about some of its coolest functionality. I used an old Brent Ozar post as an example of a, uh, large execution plan.
Anders mentioned a pair of Grant Fritchey books that are also quite helpful. SQL Server 2022 Query Performance Tuning is a powerhouse of a book and I highly recommend it. And his SQL Server Execution Plans book is free from Red Gate, so grab a copy of that for free.
After that, I talked about a book review of Peter Carter’s book 100 SQL Server Mistakes and How to Avoid Them. I have a lengthier blog post that covers my full thoughts so check that out. Short version: I recommend buying the book, even though I don’t agree with everything in it.
Our next topic involved a bit of trivia: in SQL Server, you can have columns in your ORDER BY clause that do not appear in your SELECT clause. For a long time, this was not possible in Oracle, as Mike points out, but by Oracle 19C, you now have that ability, too.
The final topic we hit was a bit of terminology, where I helped explain to Mike the difference between a few terms. As I noted on the video, I’m definitely not the poster child for terminological exactitude, but I did have the answers for this one. My answers are:
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, etc.UPDATE statement. It updates my table but does not return anything. In T-SQL, CASE is not a statement because it returns something. In F#, case is not a statement because it returns something. In C#, case is a statement because does not return anything.CASE is an expression because you have to return something: that’s the THEN part of a CASE expression, as in, CASE WHEN <predicate> THEN <thing to return> END. Speaking of predicates:TRUE, FALSE, or (in T-SQL) UNKNOWN. The contents of your WHERE clause is a predicate, but so is each bit within your WHERE clause: T1.Col1 > 3 is a predicate, because, for each row, T1.Col1 is either greater than 3, less than 3, or NULL. And the combination of T1.COL1 > 3 AND T2.Col2 < 17 is a predicate: the whole returns one of true, false, or unknown. Butt so do each of the parts making up the whole predicate.Our first topic was a viewer request from the prior show and was about HYCU and Azure Backup. I wasn’t familiar with HYCU and so I spent a little bit of time looking at their Azure offering and their data protection documentation. I still haven’t tried it out but that didn’t stop me from talking about it!
This was a departure from the normal data platform fare, but I’ve been using Openpilot and the Comma 3X a lot for assisted driving. Behaviors are a lot smoother than my stock driver assist options, especially when I’m not on highways. It’s definitely not perfect for me, especially in how it tries (and fails) to make smooth turns, but a surprisingly large percentage of my driving involves driving approximately in a straight line, and this does a fantastic job of that.
We opened up with a note that the Live! 360 call for speakers is currently open. I’ve had the fortune to be able to speak there several years now and I think it’s an excellent conference covering a wide variety of tech tracks.
Friend of the show Chris Voss chimed in with the major theme for the night: the modern data stack.
Summarizing Chris’s question in my own words:
We spent a lot of time talking about Microsoft Fabric, data lakes and lakehouses, and a variety of other topics in and around the “modern data stack” idea.
The main topic of the night was reviewing a great blog post by Hugo Kornelis on the current status of the MERGE operator in T-SQL. Hugo builds this on top of work that Aaron Bertrand pioneered and Michael J. Swart walked through a couple years back.
I have no problem with any of Hugo’s conclusions and appreciate the level of effort he went through. In fact, after reading the blog post, I’ve changed my mind on MERGE. Early on, I was gung-ho about it because I knew about Oracle’s UPSERT and wanted an equivalent for SQL Server. But once all the bugs and horror stories came out about MERGE, I stopped using it altogether. Now, it’s good to know that most of the problems have been fixed. There are still some cases in which you don’t want to use MERGE and Hugo highlights those instances, but for the rest of the time, it’s just as safe as having separate operations for insert, update, and delete operations.
Our other topic was around a ticket I’ve been watching for a few years to support F# in the SQL Server CLR. I got a rush of excitement when it was closed as fixed, but after a bit of confusion, the ticket was re-opened and it’s still awaiting work. Hopefully this will make its way out to the world someday. It would have been a great addition 15 years ago, when the CLR was still novel, as F# is an excellent language for the small types of functions that work well as CLR functions. But still, better late than never.
Our first topic of the night was TriPASS election results. With a resounding percentage of the vote, Tracy Boggiano and Mike Chrestensen are still stuck doing work for me TriPASS board members. I congratulate them and am glad to have them (as well as all of the board members) aboard.
Mala brought up the first real topic of the night, in which we talked a bit about how exciting SQL Server is. My belief is that yes, it’s not exciting for most people, but that’s okay: it works and it works well. That said, even within that answer, I’d still say that there are plenty of things in the product which do excite me. Of course, I’ve also focused a large portion of my career around SQL Server, so you’d hope that it would interest me.
I have a longer answer in the segment but basically, relational databases aren’t exciting and that’s generally a good thing. Exciting data platform technologies tend to lose your important data, even if they’re web-scale.
The other article was an interesting blog post from Nick Scialli on low-code products. I agree with the crux of the article and we spent a good amount of time talking about it, reminiscing about FoxPro, doing the opposite of reminiscing about PowerBuilder, and plenty more.
Mike started us off with a neat lab to learn how to write SQL, specifically using the SQLite syntax. The story is a murder mystery and you figure out whodunnit based on data in the tables. There’s also a walkthrough if you don’t know where to begin.
This kind of reminds me of the Kusto Detective Agency project, though that one ramps up really fast into complicated solutions rather than staying at a beginner level.
Our primary topic was around a series of blog posts Erik Darling has released. Erik is responsible for maintaining some rather large open-source stored procedures and is a clear writer, so this has been quite interesting to follow. I don’t agree with 100% of what he has to say and talk a bit about some of the disagreements, but these are usually minor quibbles rather than “He’s totally wrong” disagreements.
Along the way, Solomon dropped in a link to a DBA Stack Exchange comment he wrote about transactions and XACT_ABORT, and I recommend taking the time to read through that.
This was a fairly packed episode of Shop Talk. We first talked about the three major conferences that happened the prior week: PASS Summit, Microsoft Ignite, and Live! 360. I went to Live! 360, so spent the most amount of time talking about that, but Anders went to Summit and shared some experiences. We also spent some time going through the Microsoft Ignite book of news.
One of the major announcements at Ignite was that Microsoft Fabric is now generally available. I shared some thoughts about the product: to wit, that there’s a lot still coming, and I hope it doesn’t develop a reputation of “too much broken, too much missing” before it stabilizes as a really useful service. The problem is, I think that it’ll take about 1 1/2 to 2 years before it is a really useful service for most companies, and a lot of opinion solidification can happen in the meantime.
Our other major topic was some of the news happening at OpenAI right then. As of the time of recording, the OpenAI board booted CEO Sam Altman, and chairman of the board Greg Brockman resigned in protest. Then, Altman and Brockman went to work for Microsoft heading their AI team and it seemed a lot of OpenAI employees were headed that way. The news is still in flux as I post this recap—for example, Altman is back as CEO of OpenAI, while the board members who ousted him are now gone.
There will be an election for two TriPASS board positions this year. Tracy Boggiano and Mike Chrestensen currently hold these positions and are re-running. If you are a TriPASS member in good standing (basically, you’ve signed up on Meetup and haven’t broken any of the bylaws), you can also throw your hat into the ring and run for office. If so, please let me know, either via e-mail (if you have it) or reaching out to me on Meetup.
The first topic of the night is that there are two major conferences coming up next week: PASS Summit and Live! 360. Tracy will be at the former and I’ll be at the latter, so if you’re going to either, come say hello.
Our next topic covered a great blog post by Tomaz Kastrun, who showed us how to play Connect 4 in T-SQL. Along the way, Anders pointed out an older Itzik Ben-Gan article about solving the 8 Queens problem in T-SQL. Unfortunately, the links in there don’t work anymore, though here’s another solution.
Somehow, we got sidelined on using Visual Studio Code extensions in Azure Data Studio. I used the example of the excellent Rainbow CSV extension for Visual Studio Code and showed how to get it working in Azure Data Studio. It failed the first time because my version of Azure Data Studio was a bit old, but after updating, the second time was the charm.
The next topic covered some pain Mike is going through right now as his organization does a big migration from Oracle to SQL Server. I don’t have a lot of great advice around this, sadly. Or maybe happily, as it means I haven’t had to do one of those.
The final topic of the night also came from Mike and it has to do with optimizing some nasty-sounding views. We had to stick to high-level ideas because there was no example to troubleshoot live, but here’s some of the key guidance: