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.
Our first topic was me talking about some of the audio silliness I put up with for years because I didn’t know any better. I blame nobody but myself for hiding all of those decibels for so long. Things are better now, by which I mean I’m a lot louder. It also means that people who don’t speak English will understand me better. Because I’m louder.
Mala brought up our primary topic for the day, a post on X from Grant Fritchey regarding version-specific functionality. My official ranking of responses is:
TRY_CAST(), PARSE() and TRY_PARSE(), CONCAT(), OFFSET/FETCH, and the use of sp_describe_first_result_set. Those are available in 2012, not 2008.Our next topic was a good article from Daniel Calbimonte on normalization. I covered this on Curated SQL, but wanted to get a bit further into detail on why I argue that atomicity is not actually a pre-requisite for 1st Normal Form. In Daniel’s defense, a lot of references on database design and normalization include atomicity as a pre-req, but I base my argument on CJ Date’s definition of 1NF and his critique that “atomic” doesn’t actually have a precise meaning, whereas each rule of normalization must have a precise mathematical definition.
I also touched upon a fantastic essay from Phil Factor about the entity-attribute-value anti-pattern. I don’t know that I’ve ever met Phil (though if you’re out there, let me know—I can keep a secret!) but this is a must-read essay.
Our first topic was a SQL Saturday survey that Steve Rezhener put together and that Steve Jones has promoted. I do recommend filling out the survey, and you can also view the results. Some of the questions on the survey were a bit of a challenge to understand, so I think that has limited the number of responses (in fact, Anders quit about halfway through the survey for that reason). But the information is potentially useful for getting a pulse of where SQL Saturday attendees are in terms of their current capabilities, as well as an idea of what types of sessions might be interesting to include.
Along the way, I talked a little bit about designing surveys, took some potshots at DB2, learned from Solomon that some of my cheap shots at DB2 around 8-character column names are no longer valid, and dove into how ranking mechanisms work for sites like DB-Engines and why its results might so radically differ from the Stack Overflow survey. Populations matter a lot for surveying.
The other major topic came from Tracy, despite her not being on the show this time around. She wanted to talk a bit about pagination and OFFSET/FETCH in particular. I happen to have a talk that includes pagination techniques and went into detail on how each works and the expected performance profile for each one. OFFSET/FETCH is fine for systems where you don’t have a huge number of pages, or where people rarely go past the first few pages. But it performs steadily worse the farther in a person goes, as it requires scanning all of the offset records and then pulling in the fetch records. I also lamented how much of a challenge pagination is, and how there are so few really good patterns for it given how common a technique it is and how important it is for websites.
Our first topic was a series via Straight Path Solutions on 30 SQL Server security checks in 30 days. There are some good things to keep in mind while going through this series. That said, I’d highly recommend Solomon Rutzky’s Module Signing over anything to do with cross-database ownership chaining.
The second topic was a blog post about two new additions to T-SQL in Azure SQL Database: UNISTR() and ||. The || operator acts very similarly to the + operator and serves to concatenate two items together. || is the ANSI SQL standard, so that makes it a perfectly reasonable addition to the syntax, though I’ll use CONCAT() and FORMATMESSAGE() over || or + most days of the week.
The other addition is UNISTR(), which lets you escape Unicode characters. Solomon had a good explanation of why he’s not the biggest fan of this change.
Our final major topic was thanks to Tracy: linting in SQL Server. Tracy recommended super-linter as a project for linting in a wide variety of languages. In this broader project, there are a pair of linters for relational databases, and Tracy’s preference is SQLFluff. We explained what the purpose of a linter is, looked at some linting rules, and got derailed on “usp_” and passive voice, as is my wont.
Our first topic came from Brent Ozar’s Monday links newsletter and covers a report that Vista Equity is writing off the entire equity value in Pluralsight, which they bought for $3.5 billion a few years back. I ranted a bit about private equity and talked about some of the challenges that learning platforms have in our modern era. I really liked Pluralsight and was a subscriber for years, only cancelling the subscription after I moved to a totally different role and really didn’t have time to take any more courses.
Our other big topic was an amazing article covering a terrible idea. Kevin Beaumont has written a top-notch post describing the many ways that Copilot+ Recall is not ready for prime time. And one cynical take I read after broadcast summed up the problem quite nicely: if Copilot+ Recall does become generally available, you have to assume that any document you ever send to a user running Windows 11 (or later) will be stolen.
Speaking of Copilots, we also talked about the various Copilots Microsoft has available right now, based on this Ginger Grant article. Ginger lists 10 of them and we might even have quibbles about there being additional ones (e.g., PowerPoint and Word being separate Copilots rather than one Office 365 Copilot) in practice if not in pricing.
Our first topic was a quick recap of SQL Saturday Richmond. It was good to see the RTP area turnout, as well as some people we don’t get to see very often, like Anders. I also talked about how the SQL Saturday Richmond people gave me 2 hours and 15 minutes to talk about normalization and I still went long. It’s a mad world.
Our next topic was OpenAI’s release of GPT-4o, a cheaper, faster, multi-modal model. It’s also available in Azure OpenAI, though only two regions as of right now (East US and West US 3). That got us on a winding discussion of Azure regions, including a discussion of what Azure OpenAI resources are available in which region, how Sweden Central gets all the nice stuff, and then a dive into Azure regions as a whole. I brought up several references, including an article on Azure regions.
From there, I had to show the Nuke map, which gives you an idea of the effective zone of a nuclear explosion. Marshall brought up the Mars Bluff atomic weapon blast that affected his grandparents.
Back on Azure regions, I showed one of my favorite sites for pricing; CloudPrice.NET. This started as a discussion of relative region prices and then went into discussion of specific VM costs. Searching across regions is a great way to save money, assuming you have isolated resources and aren’t sending a whole lot of data cross-region.
Bruce Schneier named a security issue I’ve always had some challenge describing: data and control path admixture. This is a real challenge with LLMs today, though it is a solvable problem. It may just take some time to solve.
We also dug into a linked article on prompt injection and had a bit of fun with other topics tangentially related, as happens from time to time (by which I mean “every time”) on Shop Talk.
First up, we wished Tracy Boggiano a happy birthday, as one does. After that, Mala announced that she’s graduating from a Master’s program in a couple of weeks, so we wished her an early happy graduation day.
I gave a sneak preview of this month’s T-SQL Tuesday, which I am hosting. The theme is, what is your favorite job interview question? I talked at some length about questions as the interviewee or candidate, because asking good questions is something a lot of candidates don’t think to do. You’re interviewing the company just as much as the company is interviewing you, after all.
Mala brought up for discussion an article about the private preview for Azure SQL Database Copilot. Anders mentioned that his company is in the private preview and he was pretty happy with how it worked.
Mala also shared a link to this article on the Structured and Unstructured Query Language, otherwise known as SUQL. I had far too much fun mocking the acronym. As an idea? I dunno, maybe it’ll work out great. But as a name? No, no, no, please talk to the marketing department first.
Anders used Suno to generate a song for the DBA, lyrics, chords, and all. You can and should enjoy it for what it is.
Our first topic came from Mala, covering the EU’s new Artificial Intelligence Act. We covered a press release on the topic and I engaged maximum cynical overdrive on this one. We also walked through a fairly recent article about the EU AI Act’s timeline, and I spent an inordinate amount of time talking about how Germans use passive voice far too often for my taste.
There are a few upcoming events that we highlighted:
Our final article of the night came from Bleeping Computer, where Lawrence Abrams wrote about an interesting malware distribution vector. The way this works is really tricky because GitHub repo owners won’t know about file uploads associated with their repositories. The attacker isn’t actually messing with repo code or anything of the like, but rather uploading files as part of comments, and the file upload happens even if the attacker doesn’t post the comment. From there, the attacker rides on the reputation of the GitHub repo or account owner to try to sneak one over on people.
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.