mssql

Detecting and Filtering Special Characters Using PATINDEX() and LIKE in SQL Server

by

Working with real-world data often means dealing with messy strings. It’s common to find values that contain unexpected special characters. Sometimes this is due to user input, sometimes it’s from imports or third-party sources.

Either way, when we need to find and filter these special characters, SQL Server gives us some handy tools to work with. For starters, there’s the LIKE operator, which anyone who’s used SQL would be familiar with. But there’s also the PATINDEX() function, which performs a slightly different task.

Read more

Writing Valid ORDER BY Queries for Views and CTEs in SQL Server

by

If you’ve worked with SQL Server long enough, you’ve probably hit the dreaded error when trying to use ORDER BY inside a view or CTE. It usually shows up as something like:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

This rule can come as a bit of a surprise because it feels natural to want a query “pre-sorted” when wrapping it in a view or CTE. The problem is that SQL Server doesn’t allow the ORDER BY clause in this context unless it’s in conjunction with the clauses mentioned in the error message. Without such clauses, you need to explicitly request it at the outermost SELECT.

Let’s walk through an example of how to handle this.

Read more

How to Prevent Overflow Errors When Aggregating Data in SQL Server

by

When working with aggregate functions in SQL Server, it’s easy to overlook that certain datatypes have strict limits. If you’re summing values in large tables (or even just summing very large numbers), you might run into an arithmetic overflow error. This happens when the result of an aggregate exceeds the maximum value the datatype can hold.

Understanding how this works and how to prevent errors will help you write reliable queries.

Read more

How to Use Window Functions to Find Duplicates in SQL Server

by

When you’re working with real-world data, duplicates can sometimes sneak in more often than we’d like. Maybe an import process didn’t filter things properly, or users managed to submit the same information twice. Whatever the reason, finding and handling duplicates is a common task.

A neat way to tackle this problem in SQL Server is by using window functions. Specifically, we can use ROW_NUMBER() or RANK() to find duplicate rows in a table. These functions let you assign a sequence number to rows within a group, which makes it easy to spot duplicates.

Read more

Convert UTC to Local Time in SQL Server

by

When dealing with applications that span multiple time zones, you’ll often want to store timestamps in UTC. That’s usually the best practice – it avoids confusion and ensures consistency. But sooner or later you’ll need to show users dates and times in their own local time zones. There are a few ways to handle this in SQL Server.

Read more

How to Choose Appropriate NUMERIC Precision to Avoid Overflow in SQL Server

by

When working with SQL Server, numeric precision can become a silent troublemaker if you’re not careful. Overflow errors happen when a number exceeds the storage capacity of the column data type. Unlike other errors that are easy to catch in testing, numeric overflow often shows up unexpectedly in production, which can be costly. Understanding how to pick the right precision and scale for your NUMERIC or DECIMAL columns can save you headaches down the road.

Read more

How to Handle Server vs Session Time Zone Settings in SQL Server

by

Working with dates and times in SQL Server can get tricky, especially once you add time zones into the mix. One common point of confusion is the difference between the server’s time zone and the session’s time zone. If you’re not careful, you can end up with inconsistent data or timestamps that don’t line up with what your users expect.

Let’s look at how SQL Server treats time zones and how you can handle server versus session time zone differences cleanly.

Read more

When to Use TRY_CONVERT() vs CONVERT() in SQL Server

by

Both CONVERT() and TRY_CONVERT() in SQL Server are used to convert data types, but they behave quite differently when something goes wrong. Understanding that difference can save you a lot of debugging time, especially when dealing with messy or unpredictable data.

Let’s look at when you should use each, and walk through an example that you can run yourself.

Read more

Comparing ISO Week and US Week Numbers in SQL Server

by

When working with dates in SQL Server, you’ll quickly find that there are different ways to calculate the “week number” of a given date. Two of the most relevant systems are the ISO week (ISO 8601 standard) and what we’ll call the US week numbering system. If you’re working in a US environment, the latter is often the default approach (due to your session’s language setting), while ISO weeks are the international standard. These different week numbering systems will often produce different results, which can be confusing if you don’t know why.

Let’s walk through the difference, and then we’ll test it with some simple examples.

Read more

Building Readable Dates for Reporting Dashboards in SQL Server

by

When you’re putting together reporting dashboards, raw datetime values like 2025-09-23 13:45:32.000 don’t do much for the average business user. People want to see “Sep 2025” or “Tuesday, September 23, 2025” rather than a timestamp that looks like it came straight from the database.

In many cases, formatting can also be handled in the reporting or application layer, which may be better for things like localization and display preferences. But there are plenty of situations where it makes sense to do this work in SQL Server itself. For example, maybe you need consistency or business-specific date logic. Fortunately, SQL Server gives us several tools for shaping dates into clear, readable labels that work well in dashboards.

Read more