Watching Brent Ozar’s 2017 PASS Summit session on Youtube the other day, I learned that the Top N Sort operation in SQL Server behaves dramatically differently, depending on how many rows you want from the TOP.Continue reading
Metadata changes, like modifying a clustered index, or many types of column changes, will create locks in SQL Server that will block users from working with that table until the change is completed. In many cases, those locks will extend to the system objects, so you won’t even be able to expand the “Tables” or “Views” nodes in Management Studio.
I want to show you how you can perform those changes using a copy of the table, then instantly switching the table with the copy. The secret is partition switching, and contrary to popular belief, you won’t need Enterprise Edition, or even partitions, to do it.Continue reading
I’ve solved this puzzle a number of times, but I’ve never really been quite happy with the result. It was either too slow, too much code, too hard to understand. So here’s a fresh take at computing the time-to-payment on a large amount of invoices, with multiple, overlapping, partial payments.Continue reading
This insanely cool new performance-related update is one of nicest features in SQL Server 2019, and certainly one I’ve been looking forward to for a long time.
If you’ve done any work around performance tuning and user-defined scalar functions, I’m pretty sure you’ll love this.Continue reading
Some operations in SQL Server will turn your entire query plan serial (single-threaded), others will just reserve a so-called “serial zone”. I read up on this stuff a number of years ago (including a great post by Paul White), and thinking that some things must have changed since, I decided to go see for myself.
I’m trying a new type of blog post, and if it works out, I would be happy to post more of the same going forward. The format is a real-world troubleshooting mystery, and I’ll clue you in to the details along the way.
How quickly can you crack it?
Inspired by an actual customer scenario: what if you have a legacy app that doesn’t schema-prefix its database objects, but you want it to work with a specific assigned schema? There’s a quick and easy solution.
SQL Server Management Studio allows you to view effective permissions on an object, but it’s limited in a few important respects. To work around some of those limitations, I’ve built a stored procedure to display all the defined and effective permissions across an entire SQL Server database.
With good naming and datatyping conventions, an automated script can help you with the process of creating foreign key constraints across your database, or actually, suggest table relations where you’ve forgotten to implement them.