How To Get Rid Of NOLOCK Hints Forever
Thanks for watching!
Video Summary
In this video, I delve into a clever workaround for managing nolock hints in SQL Server queries—a technique that can help maintain database consistency and prevent potential issues related to dirty reads. By transforming your tables into views with specific locking mechanisms (like read committed snapshot isolation), you effectively discourage developers from using explicit nolock hints. This method not only simplifies code management but also ensures more controlled data access, making it a valuable addition to any SQL Server best practices toolkit. So, whether you’re dealing with team collaboration or just want to streamline your query writing process, this tip will surely come in handy!
Full Transcript
Erik Darling here with Darling Data. I’m going to show you a little secret to help you keep nolock hints out of all your code. All you have to do is replace all your tables with views and just sort of swap the names. So we’re going to create a view here called ViewMe, and then we’re going to create a view on that table with this readCommit. the view on that table with this readCommit.com. And then the next time some developer comes in and tries to run a query with a nolock hint, they’re going to get an error because there are conflicting locking hints. Pretty cool, huh? All right. It’s our secret.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
This is dastardly and marvelous. I love it.
HAHAHA, glad you like it! It’s kinda cute, isn’t it?
It’s like a power move on the Wacky Races cartoon show. Half expecting Muttley the dog to do his evil laugh.