Writing, reviewing, debugging, and maintaining T-SQL and database code.
478 associated posts232 primary posts
This Query Exercise was very different: I didn't ask you to solve a particular problem. I pointed out that I've heard advice that SELECT MAX is faster than SELECT TOP 1, and that's not quite true. I asked you to find factors that would cause these two queries to get different execution plans: [crayon-6a6f0859b27ff460372354/] In the…
What a comfy morning to sit down with a breakfast margarita and tackle your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=E97eDEDLQU8
While taking a dip in Cabo, I went through your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=OaHImrIMM6Y
In last week's Query Exercise, we added a user-defined function to the Users table to check whether their WebsiteUrl was valid or not. I noted that even with an index on Reputation, SQL Server 2022 simply ignored the index, did a table scan, and spent 2 minutes of time calling the user-defined function on a row-by-row basis.
Take any size of the Stack Overflow database and check out the WebsiteUrl column of the Users table:
Sometimes it's null, sometimes it's an empty string, sometimes it's populated but the URL isn't valid.
At the PGConf.dev, where Postgres developers get together and strategize the work they wanna do for the next version, I attended a session where Matthias van de Meent talked about changing the way Postgres stores columns. As of right now (Postgres 17), columns are aligned in 8-bit intervals, so if you create a table with alternating columns:
I'm not talking just about Microsoft SQL Server specifically here, nor T-SQL. Let's zoom out a little and think bigger picture for a second: is the SQL language itself a problem?
Sometimes when I talk to client developers, they gripe about the antiquated language.
I went through your top-voted questions from https://pollgab.com/room/brento before heading out to PGConf.dev in Vancouver.
https://youtu.be/sd6yO-nHDx0
For this week's Query Exercise, I asked you to write a better query than ChatGPT wrote. Your goal was to find the best days and times to post questions on Stack Overflow.
I found it interesting that a lot of the initial answers focused on the times when there were the most questions, or which questions were the most highly upvoted. For me, the best time to post a question is when you have the highest likelihood of getting the right answer, quickly.
What are the best days of the week and times of the day to post a question at StackOverflow.com? It seems like a simple question, but it's surprisingly nuanced. I asked ChatGPT's latest version, 4o, and its answer made me laugh out loud. First off, the T-SQL is terrible: it creates a completely unnecessary temp…
In last week's post, I gave you a trigger that populated a history table with all changes to the Users.AboutMe column. It was your job to write a T-SQL query that turned these Users_Changes audit table rows:
Into the full before & after data for each change, like the earlier query from the blog post:
Your challenge for this week was to find out who keeps mangling the contents of the AboutMe column in the Stack Overflow database.
Conceptually, there are a lot of ways we can track when data changes: Change Tracking, Change Data Capture, temporal tables, auditing, and I'm sure I'm missing more. But for me, there are a couple of key concerns when we need to track specific changes in a high-throughput environment:
Back at home in the office, time to settle in with a nice caffeine-free Diet Coke and go through your top-voted questions from https://pollgab.com/room/brento. Why caffeine-free? Because I slug multiple coffees first thing in the morning when I wake up (usually around 3am-4am), and by the time I stream with y'all, I don't need any more go juice.
For this week's Query Exercise, your challenge is to find out who keeps messing up the rows in the Users table.
Take any size version of the Stack Overflow database, and the Users table looks like this:
For this month's T-SQL Tuesday, Pinal Dave asked us if AI has helped us with our SQL Server jobs. For me, there's been one instant, clear win: code reviews. I usually keep a browser tab open with ChatGPT 4, and I paste this in as a starting point: You are an experienced, diligent database developer…
You posted a lot of great questions at https://pollgab.com/room/brento and I spent almost an hour covering these:
https://youtu.be/hBoAhZd6M4s
Not all of the questions y'all post at https://pollgab.com/room/brento require long-winded answers. Let's conquer 22 questions in 15 minutes!
https://www.youtube.com/watch?v=XGvqkIRwupk
This week's query exercise asked you to find two kinds of locations in the Stack Overflow database:
Locations populated with users who seem to be really helpful, meaning, they write really good answers
Locations where people seem to need the most help, meaning, they ask a lot of questions, but they do not seem to be answering those of their neighbors
For this week's query exercise, let's start with a brief query to get a quick preview of what we're dealing with:
[crayon-6a6f0859bf551840129495/]
That query has a few problems, but hold that thought for a moment. (You're going to have to solve those problems, but I just wanted to show you the sample data at first to give you a rough idea of what we're dealing with.)
Your query exercise for this week was to write a query to find users created in the last 90 days, with a reputation higher than 50 points, from highest reputation to lowest. Because everyone's Stack Overflow database might be slightly different, we had to start by finding the "end date" for our query. I'm working with the 2018-06 export that I use in my training classes, so here's my end date:
