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 big news is around SQL Saturday Raleigh, which we’ve officially launched. It will take place on May 17th at Duke Health in Durham. You can register at the SQL Saturday website. There are free tickets that do not include lunch, and paid tickets that include lunch. We will not have lunch available for sale on the day of the event.
We’re also hosting Brent Ozar for a pre-con the day before. You can sign up for this on Brent’s website.
Our primary question for the day came in via e-mail from a viewer:
I currently use PowerShell scripts to pull data from source APIs for my company’s data pipelines. Our BI department is nascent, thus our scripts are mostly just pulling data and loading it into a tabular format in SQL Server. We don’t do much in the way of transformations. This has caused issues with large memory grants and implicit conversions in our SQL Server.
I’m curious if PowerShell is an appropriate or even adequate tool for performing transformations? If not, what other options are there?
We are trying to keep as much “processing” as possible outside of SQL Server to reduce licensing costs.
Mike and I spend a bit of time talking about viable options, areas of focus, and industry trends.
Mike wanted to know about the QUALIFY() clause that exists in Teradata and Snowflake. I talk a bit about its purpose and reference a Stack Overflow post that shows some T-SQL equivalents. QUALIFY() looks pretty neat and I certainly wouldn’t mind it being available to us in T-SQL, but I won’t hold my breath on it, as there are functional alternatives.
Our other topic of the night was a rant from Mike about how Oracle’s date formatting functions are annoying. I have a tiny amount to add, but this was mostly Mike’s time to shine.
Our big news is around SQL Saturday Raleigh, which we’ve officially launched. It will take place on May 17th at Duke Health in Durham. You can register at the SQL Saturday website. There are free tickets that do not include lunch, and paid tickets that include lunch. We will not have lunch available for sale on the day of the event.
We’re also hosting Brent Ozar for a pre-con the day before. You can sign up for this on Brent’s website.
Mike brought up our next topic: what are some tools and techniques for handling large-scale data export from databases for data scientists? We talked a bit about classic ETL tools like Informatica, SQL Server Integration Services, and Ab Initio. I also talked a bit about file formats, dealing with Parquet files as a standard whenever possible, and more.
The final topic had us look at the highlights of Red Gate’s 2025 State of the Database Landscape. The full report is available but I stuck to the notes on Red Gate’s website. If you want the full report, go check it out.
Mala sent in the first link, an article from Joe Fleming regarding NOLOCK. In it, Joe makes the argument that ceteris paribus, NOLOCK is not a good thing, but that there’s little to no value in eliminating it in an environment. I agree with the former and strongly disagree with the latter. Along the way, we talk for a bit about Read Committed Snapshot Isolation as the solution in most circumstances.
The other point of discussion was Brent Ozar’s 2025 Data Professional Salary Survey results. I popped open Excel and did a little bit of pivot table work. We talked a bit about data cleanliness, salary extremes, trends, and why mean can be misleading for things like salary.
Our TriPASS elections have wrapped up and our intrepid board members have survived for another two-year term.
Mala then talked a bit about her experiences from PASS Summit 2024. She’s been traveling for a bit and this is her first episode back, so we had a chance to catch up with her a bit.
We spent a lot of time this episode on SQL Server 2025, talking about some of the new functionality based on what we heard at Ignite in 2024 and thoughts for how this affects data engineers. Along the way, I generated a cocktail napkin level drawing of how vectorization works and we spent quite a bit of time talking about the new JSON data type and how this is a bit of a reversal from “the XML data type was a mistake and we should just keep JSON in NVARCHAR” thoughts in the past.
It’s election season for TriPASS and we have a slate of electors up for vote. If you are a TriPASS member, you’ll have received an e-mail about this.
I learned something from Erik Darling. I’ve learned many things from Erik Darling, but this one was around NOT EXISTS versus 0 = (SELECT COUNT(*)...). It turns out that SQL Server has an optimization that makes both return the same execution plan. After the recording, I went and checked Postgres, which does not have the same optimization. This is yet another case of how behaviors in one platform may subtly differ from others.
Mike successfully side-tracked me and we talked a bit about the three approximation functions in SQL Server: APPROX_COUNT_DISTINCT, APPROX_PERCENTILE_CONT, and APPROX_PERCENTILE_DISC. I explained a bit about how they work, linked to some of the original academic papers, and described the difference between the continuous and discrete functions.
Our final topic of the night was from a Koen Verbeeck blog post, covering tenant switching in Microsoft Fabric and the Power BI Service. This is another example of the classic Microsoft pattern of developing something without designing in the ability to switch tenants / subscriptions / accounts.
It’s election season for TriPASS and we have a slate of electors up for vote. If you are a TriPASS member, you’ll have received an e-mail about this.
A question came in from chat:
Hi Kevin, I got a question as a beginner in SQL. When I am writing queries that requires a combination of joins inside multiple CTE’s, I get a timeout error in my application. Individually, the CTE’s work, and work until a number of CTE’s are joined. Is there a way to optimize this type of query?
Mike and I talked a bit about the topic and dug in further. The good news is that the chatter followed up with me afterward and we did, in fact, help with this problem. There’s a first time for everything, I suppose.
Our other topic was what’s coming in SQL Server Management Studio 21. We talked about blog posts from Brent Ozar, Reitse Eskens, and Vlad Drumea. Along the way, I outed myself as a Light Mode beacon of brilliance.
The TriPASS 2025 call for speakers is open. We are accepting sessions for all three of our groups: the advanced DBA group (2nd Tuesday of each month), main meeting (3rd Tuesday of each month), and data science & business intelligence group (4th Tuesday of each month).
Our topic for the night was from Alex. Repeating the question:
In a recent shop Talk Mike Chrestensen mentioned that he is working on a project to migrate from Oracle to SQL Server. I’m curious what led him to choose SQL Server instead of PostgreSQL or some other open source ($0 licensing cost) DBMS.
Mike talked about the specific circumstances around his company. From there, I dove into the topic and explained my reasoning for why someone might want to choose SQL Server or PostgreSQL. I didn’t use the obvious “We’re already using this technology” answer, but instead focused on some of the key items in favor of each. I also talked about a bit of history at a prior employer.
We also talked about a great tool for comparison testing of hardware & relational database products called HammerDB. This is an open-source solution that uses the TPC-C (OLTP-style queries) and TPC-H (OLAP-style queries) workloads. The UI is a bit rough, but the tool is powerful and is a lot easier than trying to figure out how to replicate your own workload in a non-production environment.
Mala shared an article on the 50th anniversary of SQL as a language. And I tossed in an article concerning software liability in the EU.
Our topic for the night was from an anonymous user. Summarizing the question:
I am currently an infrastructure DBA for an MSP. I’m wanting to gain experience with Performance Tuning SQL Server. My job is an old-school infrastructure DBA job; we handle Data protection and High availability/DR.
What’s the best next step in my career to gain experience in performance tuning? Would trying to get on a consulting firm be the best bet for gaining experience in performance tuning? Or would I already need to be very good at performance tuning to even get hired on at a consulting firm? Also, what’s life like working for a consulting firm—that is, work-life balance, or working nights and weekends?
We covered quite a few points in this discussion. First up, some recommendations on books that I’d recommend for people really digging into performance tuning, as well as one video series that Mala recommended.
We also covered life in four stereotypical types of roles: full-time employee, contractor, consultant for a large firm, and independent consultant (with some points around boutique consulting). As always, the specifics of any role are critical and we could only cover generic statements of concept.
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.