Fix Errors 5133 & 3156 When Restoring a Database in SQL Server

by

If you’re getting error Msg 5133 and error Msg 3156 in SQL Server, it would seem that you’re trying to restore a database to a different location, but you’re not specifying the different location. This can happen when you restore a database to a different environment that uses a file paths. A common cause of this error is when restoring a backup from a Windows environment to a Linux or Mac environment.

Fortunately, the fix is easy. All you need to do is map the logical file names to paths that actually exist in the new environment.

Read more

Using Multiple Window Calculations with DATEDIFF() in SQL Server

by

SQL Server’s window functions allow you to perform calculations across sets of rows that are related to the current row, without collapsing those rows into a single result like traditional GROUP BY aggregates would. When combined with the DATEDIFF() function, they provide a great way to analyze temporal patterns in your data.

Window functions can be especially useful when you need to perform multiple different calculations across the same dataset. Instead of writing separate subqueries or self-joins for each calculation, you can combine multiple window expressions in a single query. This approach is cleaner, more maintainable, and often more performant than traditional alternatives.

Read more

How to View Historical Execution Plans for a Query in SQL Server

by

SQL Server has a feature called Query Store that maintains a history of all execution plans generated for each query over time. Unlike examining the current plan in cache with SHOWPLAN or execution plan tools, Query Store lets you see every plan the optimizer has chosen historically, allowing you to compare performance across plan changes and investigate regressions.

The following examples assume that Query Store is enabled on your database.

Read more

How to Handle “Violation of UNIQUE KEY Constraint” Errors in SQL Server

by

If you’ve spent any time working with SQL Server, you’ve almost certainly run into the “Violation of UNIQUE KEY constraint” error.

It’s one of those errors that can show up in a few different ways depending on how your data flows into the database. Sometimes it’s a data quality issue. Sometimes it’s a race condition. Sometimes it’s just a gap in the logic that nobody caught until production. Whatever the cause, there are quite a few different ways to handle it. Some reactively, some proactively. This article walks through the main ones.

Read more

Fix Error 13683 “Invalid JSON paths in JSON index” Due to Overlapping Paths in SQL Server

by

If you’re getting SQL Server error 13683 stating “Invalid JSON paths in JSON index“, it sounds like you could be specifying overlapping paths in your JSON index definition.

A JSON index cannot contain overlapping paths.

To fix this, remove the overlap from the specified paths that make up the index definition.

Read more

Understanding JSON_CONTAINS() in SQL Server 2025

by

JSON_CONTAINS() is a new function introduced in SQL Server 2025 that lets you check whether a value exists at a specific path in a JSON document. It returns an integer. This is 1 if the value is found, 0 if not, and NULL if any argument is null or the path doesn’t exist. That makes it a natural fit for WHERE clauses when you’re filtering rows based on JSON content.

It’s currently in preview (although this will almost certainly change soon) and only available from SQL Server 2025.

Read more

JSON Data Type in SQL Server: What It Is and How to Actually Use It

by

SQL Server added native JSON support back in SQL Server 2016, and as of SQL Server 2025, it officially introduced a dedicated JSON data type. If you’re on an older version, you’ve probably been storing JSON in NVARCHAR(MAX) columns and parsing it with functions like JSON_VALUE() and OPENJSON(). That still works, but the new JSON type gives you validation, better storage, and cleaner semantics.

And along with the JSON type, we get some new JSON related features. Let’s take a quick walk through.

Read more

How to Pivot Data in SQL Without the PIVOT Operator

by

Not every DBMS includes a dedicated PIVOT operator. And even in DBMSs like SQL Server that do provide a PIVOT operator, you might prefer alternatives for better readability or more control over the transformation logic. Fortunately, you can pivot data using standard SQL techniques that work across virtually any relational database.

The main approach is conditional aggregation. This is where you use CASE statements within aggregate functions to selectively pull values into specific columns. It tends to be more verbose than using a PIVOT operator, but it’s also more transparent, more flexible, and completely portable across database platforms.

Read more

What is Database Normal Form?

by

When you’re designing a database, you need some way to organize your data that makes sense. You could just throw everything into one massive table, but that leads to problems pretty quickly. Duplicate data everywhere, weird update issues, and a general mess that’s hard to maintain.

Normal forms give you a framework for organizing data in a way that avoids these problems. They’re a series of rules or guidelines that help you structure your database tables properly.

This process of organizing data according to normal forms is called normalization, and it’s one of the fundamental concepts in relational database design.

Read more