If you’ve been working with SQL Server for a while, you’ve probably come across the term “compatibility level” at some point. Maybe you’ve seen it in database properties or heard someone mention it during a migration discussion. But what exactly does it mean, and why should you care?
SQL Server
Using NULLIF() to Handle the “Divide by Zero” Error in SQL Server
If you’ve written SQL long enough, you’ve probably run into the dreaded “Divide by zero error encountered” message. This happens when you try to perform a division and the denominator turns out to be zero. SQL Server throws an error immediately, which stops your query. This can be annoying, especially if the zero values are expected in the data but you don’t want them breaking your query.
One simple way to deal with this is by using the NULLIF() function.
Fixing Invalid Date Conversions in SQL Server
When you work with dates in SQL Server, you’ll often run into situations where a value can’t be converted directly to a datetime or date. This usually happens because the source data isn’t in a format SQL Server recognises, or because the value itself is out‑of‑range (e.g., “2025‑02‑30”). Fortunately, the built‑in conversion functions CAST() and CONVERT() provide us with enough flexibility to clean up those problematic values without resorting to messy string manipulation.
Below we’ll look at the most common scenarios, show how to diagnose the issue, and demonstrate how to fix it.
Why Your SQL Server Averages Keep Losing Decimals
At first glance, calculating an average in SQL Server seems straightforward. Just wrap a column in the AVG() function and you’re done. But there’s a subtle catch when working with integer columns. If you pass an integer to AVG() the result will be an integer, even if the actual average includes a fractional part. If you’re not aware of this when calculating averages, you could potentially draw the wrong conclusion from your query results.
Let’s unpack the behavior and then see how we can fix it.
Using SQL Server’s MAXRECURSION to Guard Against Infinite Loops in a CTE
When you work with recursive common table expressions (CTEs) in SQL Server, the engine will keep feeding rows back into the CTE until there’s nothing left to process. In most cases that works fine, but if the recursion logic is flawed, or the data contains a cycle, the query can spin forever. That’s where the MAXRECURSION hint comes to the rescue. It tells SQL Server to stop after a certain number of iterations, protecting you from runaway queries and giving you a clear error if something goes wrong.
Using a Common Table Expression (CTE) to Filter, Count, and Average Customer Feedback Scores in SQL Server
When you need to calculate an average that depends on a filtered subset of rows, a Common Table Expression (CTE) can keep the query tidy and readable. CTEs are a temporary result sets defined within a SQL query that can be referenced by the main query or even recursively within themselves. They provide a way to structure queries for improved readability, making them a great tool for handling complex queries.
Get First and Last Day of Month in SQL Server for Reports
When building reports in SQL Server, it’s common to filter or group data by month. A frequent requirement is to get the first and last day of a given month, often so you can build ranges for your WHERE clauses or generate summary tables. SQL Server gives us several ways to do this, and once you know the patterns, it’s quick to adapt for different reporting needs.
Fix “The specified schema name either does not exist or you do not have permission to use it” in SQL Server
There are a couple of obvious reasons you might be seeing an error that reads something like “The specified schema name “XYZ” either does not exist or you do not have permission to use it” in SQL Server. This typically happens when creating an object on a schema that doesn’t exist or you don’t have permissions.
This article shows an example of the error and a couple of ways to fix it, depending on the underlying cause.
A Quick Look at SQL Server’s MIN() Function
The MIN() function in SQL Server returns the smallest value from a set of rows. It’s commonly used to find earliest dates, lowest prices, or in general the minimum of any column. While the function itself is simple, you may encounter it written with options like DISTINCT, ALL, or as a window function with OVER(). Some of these options don’t actually change the result in SQL Server but exist for standards compatibility, so it’s worth understanding what they mean if you ever see them in code.
Let’s take a look at a few simple examples to see how it works.
How to Use DEFAULT Constraints to Avoid NULL Insert Errors in SQL Server
When working with SQL databases like SQL Server, one common headache that can rear its ugly head from time to time is handling NULL values during inserts. Sometimes you just want a column to have a sensible default value if nothing is provided, without throwing errors or forcing developers to remember to include it every time. That’s where DEFAULT constraints come in.
A DEFAULT constraint automatically inserts a predefined value into a column when no explicit value is provided. This helps ensure consistency, prevents unwanted NULLs, and makes inserts cleaner.