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.
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.
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.
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.
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.
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.
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.
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.
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.
The ISO 8601 standard says dates should look like YYYY-MM-DD to avoid confusion between formats like MM/DD/YYYY or DD/MM/YYYY. But sometimes you might need to remove the hyphens and display the date as YYYYMMDD. Maybe your software doesn’t accept special characters, or you’re trying to save space. Whatever the case, here are some simple ways to get today’s date into YYYYMMDD format.