In the last Query Exercise discussion, we hit a performance wall when we were trying to quickly find questions with a specific tag. We threw up our hands and said it's time to make database changes, but we had a few restrictions: We have a lot of existing code that uses the Posts.Tags column That…
Your query exercise was to take this Stack Overflow query to find the top-voted questions for any given tag:
[crayon-6a7b46c6ef1ce390375048/]
That's currently using this index in its execution plan:
[crayon-6a7b46c6ef1dc928190099/]
And answer 3 questions:
What kinds of tags will perform worse than others for this query?
Could you change the query to perform better?
Could you change the indexes to perform better, without changing the table structure?
For this week's Query Exercise, we're going to start with an existing query that has performance problems from time to time, depending on how it's used.
This query's goal is to find the top-voted Stack Overflow questions for any given tag - for example, here are the top-voted questions tagged "sql-server".
Your Query Exercise was to find the best time to do database maintenance by querying the Users table, looking for a one-hour window with the lowest number of created users. We kept this one pretty simple by looking at the data in just one table, and we didn't hassle with time zones. We just wanted the 3 lowest-load hours, in this format:
If we've gotta take the database down for maintenance - perhaps a version upgrade, perhaps upgrading our own code, maybe scaling up the hardware - when's the best time to do it?
For this week's query exercise, the business has asked us to identify 3 1-hour periods with the least user activity, in this format:
Your Query Exercise was to find denormalization accuracy problems: checking the accuracy of a reporting column, Posts.CommentCount. There were two parts: finding the top 100 most problematic Posts with the biggest variances, and thinking about a long term solution to keep the CommentCount accuracy as high as practical. Question 1: Finding the Problematic Posts Your…
For 2024, I'm trying something new: weekly homework challenges! For this week's query challenge, we're going to do some data validation. Pop open the Stack Overflow database and check out the CommentCount column on the Posts table: In theory, that's a fast way to check how many Comments a particular Post has, without querying the…
Your Query Exercise was to find foreign key problems: data that might stop us from successfully implementing foreign keys in the Stack Overflow database.
Question 1: Write queries to find the problematic data.
For 2024, I'm trying something new: weekly homework challenges! For this week, let's say we've decided to implement foreign keys, and we need to find data that's going to violate our desired keys.
We're going to use the Stack Overflow database, and we'll focus on these 3 tables:
The first time I saw FOR XML PATH being used to generate a comma-delimited list, I think I stared at it, shook my head to clear the cobwebs, stared at it some more, and then closed the code editor thinking it was complete witchcraft.
And that same thing probably happened the next several times, too.
To find out, let's set up a simple status log table:
[crayon-6a7b46c6f2cad131438194/]
And then let's try a two-part transaction:
[crayon-6a7b46c6f2cb5509247957/]
Right now, SQL Server shows that I have 2 open transactions:
What Happens If I Roll Back?
But what does "2 open transactions" mean, really? If I do a rollback, what gets rolled back? Let's find out:
[crayon-6a7b46c6f2cb9575717125/]
The results:
Aaron Bertrand posted a challenge:
We’re going to use the AdventureWorks sample database (get your copy here), where the folks in marketing requested a list of users to e-mail a new promotional campaign. The customers need to meet at least one of the following criteria:
last placed an order more than a year ago
placed 3 or more orders in the past year
have ordered from a specific category in the past two weeks
Writing new code = bugging. That part's easy.
Taking those bugs back out, that's the hard part.
When someone says, "Find all the rows that have been deleted," it's a lot easier when the table has an Id/Identity column. Let's take the Stack Overflow Users table: It has Ids -1, 1, 2, 3, 4, 5 ... but no 6 or 7. (Or 0.) If someone asks you to find all the Ids…
SQL Server 2022 improved the STRING_SPLIT function so that it can now return lists that are guaranteed to be in order. However, that's the only thing they improved - there's still a critical performance problem with it. Let's take the Stack Overflow database, Users table, put in an index on Location, and then test a…
SQL Server 2022 introduced a new T-SQL element, DATETRUNC, that truncates parts of dates. For example:
[crayon-6a7b46c7017dd947429847/]
Truncates everything in that date other than the year, so it returns just 2017-01-01 00:00:
You might ask, "Well, why not just use YEAR()?" That's a good question - there are times when you need a start or end date for a date range, and this could make it easier than trying to construct a full start & end date yourself.
I was helping a client with a query, and I'm going to rework the example to use the Stack Overflow database for easier storytelling.
Say we need to:
When you write dynamic SQL, start like this:
[crayon-6a7b46c702008074842331/]
Right after the SELECT (or INSERT or UPDATE or whatever), immediately put a comment - using /*, of course, because you're not a terrible person.
That way, when you're looking at the plan cache or monitoring tools, you can see what generated the dynamic SQL, and where you need to go if you need to performance tune or fix it.
Anybody can write a stored procedure with a little help from Google. This session is about how to write stored procedures that have a high likelihood of performing well and are easy to troubleshoot.
This fast-paced, all-demo session from SQLBits will NOT cover how to write a query, syntax, or performance tuning. This is about good best practices after you've written the first one - things like how to catch errors, how to pass in multiple values, how to debug without the debugger, and more.
You've been working with Microsoft SQL Server for yeeears, and you're pretty confident that you know how to write a query.
So when I tell you about a book called T-SQL Fundamentals, you're all, "nah, man, I got this covered."
