Writing, reviewing, debugging, and maintaining T-SQL and database code.
478 associated posts232 primary posts
This entire blog post is driven by the #1 feature in this list. I think about the #1 feature a lot, like at least once a week. I think about it so much that I had to stop and think about what other similar great things Microsoft has done over the years, and be thankful for what a nice platform this is to work with. Let's go through 6 of my favorite Microsoft decisions.
I originally took these questions from https://pollgab.com/room/brento poolside at the Trixie Motel (really), but I forgot to use the good microphone, so I re-recorded 'em from my home studio when I got back:
https://www.youtube.com/watch?v=8booXqUYY-Y
When I see horrific code, stuff that would take a lot of manual labor to fix, I like to lob it over to an LLM like ChatGPT just to see how it does.
For example, on a call with a client, I opened up one of their slowest pieces of code to find that it was running a cursor, processing data row by row rather than working in sets. To simulate it, I've written up a few stored procedures against the Stack Overflow database:
Most of the time, I love Entity Framework, and ORMs in general. These tools make it easier for companies to ship applications. Are the apps perfect? Of course not - but they're good enough to get to market, bring in revenue to pay salaries, and move a company forwards.
However, just like any tool, if you don't know how to use it, you're gonna get hurt.
My Asian vacation continues in Kyoto, the City of a Thousand Temples. They're not playing around - the town is chock full of gorgeous historic temples, and it was tough to pick a scene for today's Office Hours. Let's go through your top-voted questions from https://pollgab.com/room/brento:
https://youtu.be/6zpEXYeDH1Q
Kon'nichiwa! My vacation tour continues in Osaka, Japan, a city famous for its street food, nightlife, and host and hostess clubs. I'm usually the kinda guy who does classy dinners at 5PM and then hits the sack by 9PM at the latest, so you might wonder how I got here. The answer is that Yves planned this trip out, hahaha, so here we are. As long as we're here, let's go through your top-voted questions from https://pollgab.com/room/brento.
SQL Server Management Studio v21 added native Git support, making it easier to use source control natively inside SSMS. This feature is for developers who are already used to working with Github to manage specific object changes as they work, and who are already accustomed to Git terminologies like branches, commits, pushes, and pulls. This…
So you're writing T-SQL code and you've decided that you want to send emails to customers, employees, or the general public. Perhaps you need to send order updates or low stock notifications.
Stop right there. You don't really wanna do that with SQL Server. Let's talk about why.
I'm in Seattle this week for the PASS Data Community Summit. Before the pre-conference sessions started, I sat down to take your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=fqKEvcJ3hlU
It's spooky time again! I visited Salem, Massachusetts again this year, this time for a friend's wedding. I ended up in the same spot next to the Charter Street Cemetery that I filmed at last year! I went through your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=Q4WtTm3O4bA
I'm back in the home office to take your database questions from https://pollgab.com/room/brento.
https://youtu.be/H70Iy6bX970?si=6UZJbG89FC6bVni4&t=15
Let's say we have a couple of update statements we need to run every 15 minutes in the Stack Overflow database, and we've built indexes to support them:
[crayon-6a6ef3422b91c299099489/]
While in beautiful Haines, Alaska along the Chilkoot River, I stopped to take your top-voted questions from https://pollgab.com/room/brento.
https://youtu.be/esrXsRZo_gU
I'm on a boat! We took an Alaska cruise with some friends aboard the Norwegian Jewel. En route to Icy Strait Point, Alaska, I took your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=VzlKubfSaDs
I took your top-voted questions from https://pollgab.com/room/brento while recovering from a late night of partying.
https://www.youtube.com/watch?v=_iS1uJCDmMA
If you're using TRY/CATCH to do exception handling in T-SQL, you need to be aware that there are a lot of things it doesn't catch. Here's a quick example.
Let's set up two tables - bookmarks, and a process log to track whether our stored proc is working or not:
This week's Query Exercise challenged you to figure out why these 3 estimates went so badly:
[crayon-6a6ef3422bfbd751772614/]
Our prior Query Exercise introduced SQL Server's 201 buckets problem: its inability to accurately estimate rows for more than 201 outliers in a table. I followed up with a solution using filtered statistics to help with the next 200 outliers, and I talked about how that's really overkill for the simple problem we were facing in that initial challenge.
Last week, we had an abrupt intro, and this week, we have an abrupt ending when my camera overheats, hahaha:
https://www.youtube.com/watch?v=pQOk2N3ljCc
In this week's Query Exercise challenge, I explained SQL Server's 201 buckets problem. SQL Server's statistics only handle up to ~201 outliers, which means that outliers ~202-300 get wildly inaccurate estimates.
In our example, I had an index on Location and perfectly accurate statistics, but even still, this query gets bad estimates because Lithuania is in outliers ~202-300:
