Ah, the feeling when you think of a new blog post topic, only to realize that you already wrote a post about that six years ago.
Well, I’ve done the work, so I might as well have a little fun with it.
Continue readingIntermediate
How to use switching to make metadata changes online
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 readingScalar function inlining in SQL Server 2019
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 readingNullable columns and performance
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.
T-SQL Tuesday #104: Code you would hate to live without
Everyone has a script, a hack or a checklist they can’t function without. In this edition of T-SQL Tuesday, Bert Wagner challenged us to write about our favorite scripts. This is my take.
Quiz: What’s going on here, then?
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?
An alternative to data masking
Dynamic data masking is a neat new feature in recent SQL Server versions that allows you to protect sensitive information from non-privileged users by masking it. But using a brute-force guessing attack, even a non-privileged user can guess the contents of a masked column. And if you’re on SQL Server 2014 or earlier, you won’t have the option of using data masking at all.
Read on to see how you can bypass dynamic data masking, and for an alternative approach that uses SQL Server column-level security instead.
Computed columns with scalar functions
Scalar functions can be a real headache when you’re performance tuning. For one, they don’t parallelize. In fact, if you use a scalar function in a computed column, it will prevent any query that uses that table from going parallel – even if you don’t reference that column at all!
Effective permissions on SQL Server
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.
Fun with random names
Just for laughs, we’re going to take a look at a slightly-too-complex yet elegant solution to a seemingly simple task: creating a list of 100 fictional company names for a demo database.
