t-sql

Simple CONCAT() Usage vs Manual String Building in SQL Server

by

When working with SQL Server, string concatenation is one of those everyday tasks that’s easy to take for granted. It can feel like second nature to reach for the trusty old + operator to piece together strings, but SQL Server also provides an alternative way to handle concatenations. Yes, I’m referring to the CONCAT() function.

And there’s a subtle difference between the two approaches that might sway you towards using one or the other.

Let’s compare these two approaches to building strings in SQL Server.

Read more

Step by Step Guide to Calculating and Formatting Percentages in SQL Server

by

When you’re writing reports in SQL Server, one of the first little annoyances you’ll probably bump into is how percentages show up. By default, SQL Server doesn’t have a built-in “percent” data type. Percentages are usually stored as decimals (for example, 0.25 for 25%), and if you just throw those into a report, they won’t look the way people expect.

So you’ll need to do a bit of work to get it nicely formatted into a percentage format that people expect to see.

Also, if you’re calculating percentages from raw values then that will require some more work.

In this article we’ll walk through an example of how to calculate and format percentages in SQL Server.

Read more

Cleaning Numeric Strings Before Conversion in SQL Server

by

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.

Read more

Format Different Currencies (such as USD, EUR, AUD) in SQL Server

by

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.

Read more

How to Get the Name of the Current Database in SQL Server (T-SQL)

by

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.

Read more

Using NULLIF() to Handle the “Divide by Zero” Error in SQL Server

by

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.

Read more

Fixing Invalid Date Conversions in SQL Server

by

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.

Read more

Why Your SQL Server Averages Keep Losing Decimals

by

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.

Read more

Using SQL Server’s MAXRECURSION to Guard Against Infinite Loops in a CTE

by

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.

Read more

Using a Common Table Expression (CTE) to Filter, Count, and Average Customer Feedback Scores in SQL Server

by

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.

Read more