Working with messy numeric data is one of those unavoidable realities in database development. Whether you’re importing data from CSV files, web APIs, or legacy systems, you’ll often encounter numeric values stored as strings with all sorts of unwanted characters mixed in. SQL Server’s conversion functions are pretty strict about what they’ll accept, so you need to clean up these strings before attempting any conversions.
t-sql
Format Different Currencies (such as USD, EUR, AUD) in SQL Server
When working with financial data in SQL Server, you may occasionally need to present numbers as formatted currency values. Storing currency amounts as DECIMAL or MONEY types is common, but these result in raw numbers like 1234.5, which don’t tell users which currency it’s in. With a bit of formatting logic, you can make query results easier to read and more meaningful.
How to Get the Name of the Current Database in SQL Server (T-SQL)
When running queries in SQL Server, you’ll need to know which database your query is running against. If you’re using a GUI tool like SSMS, you’ll usually see the database expanded in the object explorer. Perhaps you even navigated to the database before opening a query window. In such cases, there will be no doubt which database you’re querying.
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.