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 of the night was a report that Microsoft is putting the kibosh on HoloLens 2: Son of HoloLens. We talked a bit about the possible business angles, as well as the technical challenges that augmented reality (AR) devices face. Along the way, we also covered a story around using Meta AR glasses to dox people.
The next story was an opportunity for me to rant about IoT insecurity and unethical business practices: Deebot robot vacuums collecting photos and audio. Oh, and the vacuum cleaners have a major security vulnerability. Basically, this hit a bunch of hot-button issues for me all at once.
We then spent a few minutes talking about Grant Fritchey’s public service announcement around testing backups. A backup isn’t valuable unless you’ve tested it. One easy way to do that is with the Test-DbaLastBackup cmdlet in dbatools.
Our last story was me bouncing off of the start of a blog post around transforming the test of a SQL query to match user intent. We didn’t get into the specifics of the post itself, but rather I focused hard on the question of whether the query optimizer should make optimizations to a query or take the query as-is. My answer is a firm ‘yes’ because it is in the nature of a 4th generation language. In a 4GL, users do not write procedural code explaining how the system should operate. Rather, they define the desired end state and leave it to the system to find the best way to get there.
Our primary topic for the evening was an article in the Wall Street Journal (thanks, Marshall) bemoaning the state of searching for a job in the tech space, or in the tech-adjacent space, or in the not-quite-tech-but-also-not-quite-working space. I drop some spicy takes, and if I end up in a ditch somewhere, it’s almost certainly not because of Boeing. Remember: Boeing. They get you places, one piece at a time.
The other topic was a blog post from Martin Schoombee, which I have been trying to talk about on Shop Talk for a good month. Martin talks about some of the challenges in rating the quality of a dashboard, namely that you have to understand what it is that the user needs to get out of a dashboard. Shiny gee-gaws and pretty widgets are great and all, but at the end of the day, a dashboard’s purpose is to provide relevant information to its users, allowing them to act upon that information in a timely manner. Did you do this? Congratulations: you have at least a minimally successful dashboard. Now, from there, aesthetically pleasing is fine. But even there, a majority of the aesthetic advice has as its fundamental purpose ensuring that users can quickly and easily learn information. That’s why we spend so much time talking about types of graphs, ensuring that we consider accessibility, etc.
Our primary topic for the evening was a review of Red Gate’s State of the Database Landscape 2024 survey. Red Gate received 3849 responses and put together a great deal of information, so we covered a fair amount of it. I also got sidetracked a couple of times on specific choices for visuals, but that complaining aside, I appreciate all of the work Red Gate put into the survey and their findings were interesting enough that we discussed them for quite some time.
Along the way, ENHANCE_QC brought up a comment about operating expenditures versus capital expenditures in chat and I dove into the topic a bit, referencing a couple of slides from a talk I deliver on saving money in the cloud.
The first topic of the night was a blog post by Pete Warden, asking why the Internet of Things phenomenon never got past the trough of disillusionment in Gartner-speak. Pete brings up several good points, though I spend a long time railing on one point I didn’t see from Pete: the utter lack of security. But in fairness, I’m also not sure that the utter lack of security is actually a factor in why the mass market generally rejects IoT objects, because I’m very cynical about this topic.
Anders and I also spent a bit of time covering industrial IoT, where there’s a much stronger use case. And I went half-ranty on the desire for Intranet of Things and not Internet of Things.
Topic number two comes from a David Engel announcement: Microsoft is deprecating the System.Data.SqlClient package. For the most part, this shouldn’t be a big deal to switch over because Microsoft.Data.SqlClient has been around for several years and has significantly grown in capability. It did remind me of FSharp.Data.SqlClient, which needed System.Data.SqlClient back when I used it.
Mala wanted us to talk about a Microsoft article on improving full-text index performance. This article brought back memories, many of which were not good. For example, it reminded me that AWE was a thing. For those who are blissfully unaware, prior to SQL Server being a 64-bit installation, the x86 version was limited to 2GB of RAM. Unless, of course, you enabled the Address Windowing Extensions capability, which allowed you to use 3GB of RAM for the database engine. Given that this article prominently talks about AWE, I think it gives you an idea of how quickly advice changes around full-text indexing.
I will give the article major kudos on one point: it provides an explicit formula for “How much memory will I need for this?” Too often, the answer to that question is, “Dunno, how much you got?” And that’s not a particularly satisfying answer. In fairness, the reason for this is there are so many factors that affect memory needs, including database sizes, indexing strategy, expected query response times, other services running on the machine, etc. That makes it really hard to give a satisfying a priori answer. This article does that, at least with respect to how much memory the fdhost.exe service will require. I think it definitely falls short on amount of memory left over, especially for modern servers, but I still applaud its attempt to get to a conclusive answer.
The first topic of the night was EightKB, an online conference for advanced data platform topics. Their annual event happened recently and you can see all of the videos on their YouTube channel. If you want to dig into topics around SQL Server and other data platform technologies, EightKB is one of the best places to go.
Our next question came via e-mail and was around good training resources for a person learning R. In this case, the person was not necessarily that technical but did want to learn about data analysis. Mala, Mike, and I all know R and so we put our heads together and came with the following as resources we can recommend:
Our third major topic of the night was a post from View from the Wing (via the Brent Ozar Newsletter) around airlines getting closer to having 10,000 flight numbers and some of the challenges around it, especially for places that still have analog boards displaying flight information. None of us hosts has enough knowledge of airline industry software to know how easy or difficult it would be to change (though probably absurdly difficult, given that this seems to be the norm), though Anders in chat did share some interesting thoughts on the topic. We did digress a bit to discuss other problems companies run into, such as running out of identity integers.
The first topic of the night covered the recent CrowdStrike incident. Mala pointed us to one article and I brought up an article in The Register. There’s a fair amount that we know about the story, so we covered what we had, including a brief discussion of travel issues. I was fortunate in that my travel happened after American Airlines figured out their issues, but Friend of the Show Marshall had some tales of woe in Atlanta.
Our next topic came from Friend of the Show Mark Gordon, who asked what languages developers are into these days. I shared my list: C# and Java are common but not necessarily popular languages. Python, Rust, and Kotlin are popular languages (as is Go, though I forgot to mention that one), with Python really dominating the mainstream in a bunch of fields. And, of course, for any front-end development, there’s the JavaScript Framework of the Month Club: React, Vue, Express, etc.
This also happened to coincide with Stack Overflow’s 2024 survey of nearly 50,000 developers. We looked at the languages on this list and talked about which languages are over-represented (due to the survey being a sample of Stack Overflow users, who are themselves a biased sample of the overall developer population). A few languages like COBOL are underrepresented, I argued, as people aren’t going to Stack Overflow to answer COBOL questions. We also talked about longevity in systems: C++ and C are both still high on the list, even though I’d imagine that most new development isn’t happening in those languages. But that’s the effect of legacy in programming languages.
Our final topic was an article from Joe Leon at Truffle Security, looking at accessing deleted and private repo data on GitHub by use of forks. My stance on this is, it’s worth knowing that there’s a risk from forks, but the behavior makes sense and enables open-source projects to offer the ability for people to make pull requests without needing to grant write permissions to randos. GitHub can also rightly say RTFM, where they clearly indicate the consequences of enabling forks on a repository. So if you are administrator of GitHub repos in an enterprise, read the article, understand what it means, and carry on.
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.