Here’s a quick tip that touches on one of the powerful SSMS tricks in my “Management Studio Level-Up” presentation. Say you have a potentially large number of database objects (procedures, functions, views, what have you), and you need to make a search-and-replace kind of change to all of those objects.
You could, of course, put the database in source control and use a proper IDE to replace everything, then check your code back into source control and commit it to the database. That’s obviously the grown-up solution. Thanks for reading this post.
But let’s say for the sake of argument that you haven’t put your database in version control. What’s the lazy option here?
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.
Date and time values are not entirely intuitive to aggregate into averages in T-SQL, although the business case does arguably exist. Suppose, for instance, that you have a production log with a “duration” column (in the “time” datatype), and you want to find the totalt or average duration for a certain group of items.
It’s possible, but I would still call it a workaround.
I’ve been working on a little gadget for a while now, and today I finally got around to completing it and so now I’ve published it for everyone to try out. It’s a web API (wait, wait, don’t go away – it’s for database people!) that creates a randomized list of names, addresses, etc.
In this post, I’ll show you how easy it is to use this service to anonymize a development or test database so you don’t have all that personally identifiable information floating around.
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.
I came upon this issue when I was building some views to support legacy integrations to an app that I was refactoring. The view is supposed to have exactly the same column definitions as a table in the old database that I am redesigning, so to make SSIS packages and other integrations run smoothly, I want the view’s columns to have the same datatypes, nullability, etc.
But there are some gotchas to watch out for with CAST and CONVERT.
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.
Typically, the advice on fill factor is “if it ain’t broke, don’t fix it”. But occasionally, you’ll find a database or even a server with a crazy default setting that just fills your disk and buffer pool without any real benefit. Here’s a nifty script to rebuild any tables and indexes to a fill factor of 100%.
We need to talk about the nullable columns in your database. Specifically, because of how NULL values are compared, they can dramatically affect how some lookup operations perform.
Regular readers of my blog will know that I occasionally share some useful scripts on my Downloads page. And even though I update some of those scripts regularly when new versions of SQL Server come out, or if I run into a bug feature, there really hasn’t been a practical way for readers to subscribe to those updates or to contribute with good ideas.
I recently attended the annual PASS Summit conference in Seattle, and as part of my personal goal to try to learn new (and scary) things, I took a precon on working with Git.
So as of now, a bunch of downloads are available on GitHub (which is, really, a much better place to host scripts than a shared Dropbox link). You can download them as usual, and if you want, you can add your improvements and send me a pull request. I know I’ve received a ton of good ideas and suggestions over the years, but more often than not, I haven’t had the proper environment to test those changes in, or I just haven’t had the time to dig into my old code.
But now you can: