When you’re writing defensive T-SQL, you have to write a lot of code. Or, rather, you have to write a lot of code to write the same code over and over again. That’s what we’re going to talk about today. By this, we mean that the code you have to write is the same code over and over again. We’ll talk you through some of the tips that will help you to write defensive T-SQL. These tips might seem a little obvious, but when you’re trying to do it by yourself, it can be easy to forget. Read on to see if these help!
Define the business purpose
First and foremost, you have to define the business purpose of your script. If you’re writing a script to find all the tables with more than 10,000 tables, then that’s not defensive. The business purpose could be to find tables with more than 10,000 tables, but that’s not the only reason for doing it.
Defining the business purpose will mean writing your script with a reason in mind. That’s why we call it defensive. There’s nothing wrong with finding tables with more than 10,000 tables, but when you’re finding more than you need to, you’re just wasting resources.
So, if the business purpose of your script is to find tables with more than 10,000 tables, then you know exactly why you’re doing it.
Every query should have a reason
Your queries don’t all have to have a reason, but, as we’ve said, they all should have a reason. A query can be used for a variety of purposes, but the most common are: Finding records that match a certain condition, finding records that don’t match a certain condition, finding records that are associated with another record, and finding records that won’t be shown to a user.
You have to understand what you’re doing what you’re doing with each query you write. You have to understand why you’re doing what you’re doing. It can be easy to forget all this when you’re writing the code. So just think about your queries. You can write your queries down and work through what they’re doing. This will help you remember why you’re doing what you’re doing.
Set up your defensive analysis first
What do we mean by this? Well, there are a few different methods and tools that can help you find and fix issues in your code. While you should absolutely use all of these, you should start with the ones that help you to understand your code before you start writing it.
First, you should always log your queries and the products that are being used. The context for the queries is important as well as the products being used. You should log queries along with the context, too. What information was being shown at the time the query was used? This context will help you understand what you’re doing, and logging it will help you understand what happened.
Next, you should use your application’s monitoring tools to find issues in the code. This can be done with tools like Azure Application Monitoring or with a T-SQL program like SQL Sentry.
You should then check your logs to see if the issues you found in the monitoring tools are there in the logs. You should have a monitoring strategy that runs through every aspect of your code.
Avoid NULL values and common mistakes
These are pretty self-explanatory. Avoid using NULL values, and make sure that you use the right type of data when you’re saving data. This will help to avoid issues in the future. When you’re writing the code, make sure that you know what you’re doing. Know what you’re doing when you’re using variables, strings, numbers, and other objects.
Record your analysis
This is a simple one, but it’s an important one. Make sure that you record your testing and your analysis. This will help you to remember what you found, and, if things go wrong, you’ll know what to look for to fix them.
Make sure you’re only targeting what you want to target
This is a tip that will help you with every script you write. When you’re creating your scripts, make sure that you’re only targeting the data and features that you actually want to use.
For example, when you’re creating a script that runs through all the objects in a database table, make sure you’re only targeting the objects that you actually want to target. You should only be targeting information that is actually relevant to your application. For example, if you’re creating a script that runs through all the objects in a customer table, you shouldn’t be targeting the customer’s address, the customer’s current balance, or the customer’s first name. These will just increase the script’s duration and CPU usage.
Never assume the WHERE clause conditions will be evaluated in the order you list them. Your code can still work if this assumption is made, but it’s not a safe query.
- Use CASE expressions instead of relying on WHERE clause conditions being evaluated in a specific order
- Make sure variable assignments are unconditional, and use SET rather than SELECT to avoid infinite loops.
- Don’t rely on data being returned in some default order. If order is required, use ORDER BY.
- Using NOT EXISTS leads to more resilient queries than NOT IN.
Summing up
Writing defensive T-SQL is a lot of work, especially when you need to write the same code over and over again. These tips will help you to write defensive T-SQL, but you’ll have to work hard to make best use of them!