Help Your Distributed Team Develop Quality Code – Microsoft Webinar featuring Redgate Tools – 1 hour, free – Register

Why Speed and Quality are No Longer Trade Offs – 1 hour Redgate Streamed session, free – Register

Use Chocolatey to Install Multiple SQL Server 2019 Instances for Testing

I’m working on a project where it’s useful to automate environment setup and teardown for testing some devops deployment scenarios for databases using transactional replication. To make this easier, I’m using: Octopus Deploy Runbooks to organize a series of commands (they have a free tier, FYI)Chocolatey to install multiple SQL Server 2019 Developer Edition instances (I’ve written about choco before, it’s also free) While I’m not at all a fan of “stacking” multiple SQL Server Instances into one Windows Installation in production, it’s fine for this testing scenario. But I came across a little puzzle when trying to get this to work: when I tried to use choco install or choco upgrade for my second instance, it saw that SQL Server 2019 Developer Edition was already installed and did nothing. There is a –force option which can be used to install additional instances, but the SQL Server Installer will throw…
Read More

Learn TSQL for free online – starting today

Thanks to the support of Redgate, I’ve launched a new course which teaches you the basics of TSQL. The course is totally free, no logins required — we don’t even ask for the email address. Check out the course on Redgate University The course is here: Each week has an embedded video for the course, along with a link to the syllabus and scripts. The videos also have a timeline in case you wish to jump to a particular part of the discussion. Want to join live? I’m publishing a new module each week, and we have five weeks to go. There’s a calendar reminder in the course if you want to join me live on Wednesdays at 3 pm BST / 11 AM EST, or you can catch the videos each week and follow along. Want to learn Query Tuning, Reporting Services, or PowerBI? Redgate’s Community Circle initiative…
Read More

Learner’s Guide to SQL Server Query Tuning

Following on from my Learner’s Guide to SQL Server Performance Triage, I’m tackling Query Tuning. In this guide, I’m experimenting with an outline style rather than expanding each paragraph. In this post… Why do we need query tuning?Who needs to do query tuning? Who doesn’t?What skills are involved in query tuning?Popular built-in query tuning tools and how they work togetherTough problemsIs testing in production required?Automated query tuning: history and evolutionCommon mistakes and pitfalls in query tuning Why do we need query tuning? We’ve seen incredible improvements over the last 15 years: Storage speed has massively increased due to advances in both storage technology and network bandwidth, CPUs have become much faster, and prices for memory have dropped dramatically.Database optimizers are constantly improving and finding ways to make queries more adaptable and to conquer areas of poor optimization. Yet there are still professionals who make a good living tuning queries,…
Read More

Join the Best Worst Code Contest today at 5pm BST / Noon Eastern #BestWorstCode

I have an idea! Let’s have a “Worst Code Contest.” Why do this? Bad code is really fun — at least, when you write it on purpose. And other people’s bad code is even more fun. But seriously, I’ve needed to write a lot of bad code over the years when creating demos and reproducing various scenarios. It uses your brain in an unusual way, so it’s a fun thing, but it also teaches you a lot! How to play The game starts at 5pm BST / Noon Eastern / 9 AM Pacific today. I’ll be livestreaming the contest here, but you can play whether or not you join the stream. Here’s how it works: I will post a challenge on Twitter to write your best worst code that either does a specific thing in TSQL or produces a specific result set when run against a SQL Server I will…
Read More

Make Database Code Reusable in SQL Source Control with Deployment Filters

When I first began working with databases, I was lucky to land a job at a little start-up which had solid development and operations processes: all our code, including database code, was in version control. We had a strong database architect who instilled good coding practices into the way we managed database code as well: code was expected to be reusable whenever possible. For example, there was one set of code which managed error handling and logging for all our databases. It lived in one repository in version control, and was deployed into many databases. This code resided in its own schema, in this case the “EVT” schema. Similarly, there were other bits of “reusable” database code for managing configuration, transferring data between instances, documenting objects and procedures, and other common tasks. There are huge benefits to establishing reusable pieces of code for your databases The top five benefits I…
Read More

Your team can’t stop writing crappy code. Will Intelligent Query Processing fix it?

People have been writing about how evil scalar functions are in SQL Server for ten years. But people still use scalar functions in their code all the time. And not dumb people, either: smart people do this, too. I bet you they’re in your codebase. Good people write bad database code for many reasons: Bad code patterns often look simple and elegantSmart people re-use code patterns, so when a bad pattern gets into your code, it tends to spreadMost people, even smart people, aren’t great at remembering loads of gotchas, especially if they need to work in multiple languages or in multiple areas of specialization This is why I’m so excited about Microsoft’s new Intelligent Query Processing features for SQL Server: they are upping their game and working to make your existing code run better. But, with all new things, you may need to be careful. If you’re curious to…
Read More