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.
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…
Hallelujah. With current versions of Entity Framework, when developers add a mix of parameters and specific values to their query like this: [crayon-6ab9f17566e24300782139/] See how part of the filter is hard-coded (".NET Blog") while the other part of the filter is dynamically generated, an ID the user is looking for? That causes Entity Framework to…
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:
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:
I'm kinda weird. I get excited when I'm troubleshooting a SQL Server problem, and I keep hitting walls.
I'll give you an example. A client came to me because they were struggling with sporadic performance problems in Azure SQL DB, and nothing seemed to make sense:
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…
Sometimes when you do GROUP BY, the order of the columns does matter. For example, these two SELECT queries produce different results:
[crayon-6ab9f17568d0c007269811/]
Their actual execution plans are wildly different:
They both use the index to retrieve their data, sure, but:
I didn't say that - Guy Glantser did.
Guy Glantser is an Israeli SQL Server guru with a ton of great presentations on YouTube. I've had the privilege of hanging out with him in person a bunch of times over the year, and I'll always get excited to do it again. He's not just smart, but he's friendly and funny as hell.
An interesting question came in on PollGab. DBAmusing asked: If a query takes 5-7s to calculate the execution plan (then executes <500ms) if multiple SPIDS all submit that query (different param values) when there's no plan at start, does each SPID calc the execution plan, one after the other after waiting for the prior SPID…
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-6ab9f17569d50307243403/]
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:
For this week's Query Exercise, we're working with the Stack Overflow database, and our business users have asked us to find the new superstars. They're looking for the top 1000 users who were created in the last 90 days, who have a reputation higher than 50 points, from highest reputation to lowest. In your Stack…
In last week's Query Exercise, our developers had a query that wasn't going as fast as they'd like:
[crayon-6ab9f1756b9f7436439419/]
The query had an index, but SQL Server was refusing to use the index - even though the query would do way less logical reads if it used the index. You had 3 questions to answer:
For this month's TSQLTuesday, I asked y'all to describe the most recent issues you closed.
If you ask someone in IT, "What do you do for a living?" they struggle with job titles. They say things like:
