Ian

Using JSON_MODIFY() to Update Nested JSON in SQL Server

by

Working with JSON data in SQL Server has become quite common as more applications are relying heavily on semi-structured data. When your JSON includes nested objects or arrays, it’s useful to know how to make precise updates without rewriting the entire document. Fortunately, SQL Server makes this relatively simple with the JSON_MODIFY() function. This function lets you update specific values inside JSON text stored in a table, even deep within the structure.

In this article, we’ll walk through an example of how to use SQL Server’s JSON_MODIFY() function to update nested JSON.

Read more

Extracting Substrings Dynamically in SQL Server

by

String manipulation in SQL Server can sometimes be tricky, especially when you don’t know exactly where the piece of text you need begins or ends. You might have data where the structure isn’t perfectly consistent, and you can’t rely on fixed positions. That’s where dynamic substring extraction comes in handy.

In this article we’ll look at how we can dynamically extract substrings from a string in SQL Server when we don’t know the start or end positions of those substrings, or their lengths.

Read more

Generating Test Data Quickly with the VALUES Clause in SQL Server

by

When you need to test queries, stored procedures, or reporting logic, having quick access to realistic data is imperative. While tools like INSERT...SELECT from other tables or importing CSV files are common, sometimes you just want to spin up a set of rows on the fly without messing with permanent tables. That’s where the VALUES clause can help.

The VALUES clause is usually seen in simple INSERT statements, but it can also be used directly in a SELECT statement to create inline datasets. This can be especially handy for mocking up scenarios, testing joins, or building quick prototypes.

Read more

Handling International Date Formats When Casting to DATETIME in SQL Server

by

Working with dates in SQL Server is usually quite straightforward. There’s a good range of date types and functions that we can use to manipulate date/time values.

But international date formats can undo all that simplicity in a heartbeat. Something as simple as casting a string into a DATETIME type can blow up depending on how the server interprets the input. This often happens when you’re dealing with applications or imports that don’t stick to a single culture or regional setting.

Let’s walk through an example and see why SQL Server behaves this way, and more importantly, how to handle it correctly.

Read more

Using AVG() with DISTINCT in SQL Server

by

When working with averages in SQL Server, it’s easy to assume that AVG() just takes all rows into account and calculates a simple mean. And that’s true. By default, AVG() includes every value in the column you point it to. But sometimes, you may want to average only unique values in that column, which is where DISTINCT comes into play.

Let’s explore this with a simple example.

Read more

Why You Should Consider More than Expected Maximum Values When Choosing SQL Server Column Datatypes

by

When designing a SQL Server database, one of the first tasks is deciding what datatype to use for each column. A common rule of thumb is to choose a datatype that fits the largest value you expect to store. For example, if you know an INT will comfortably hold all expe1cted order quantities, then it seems like a safe choice.

But that logic can fall short in some cases. It completely ignores an important consideration – aggregate queries.

Read more

How to Identify Dependencies Before Dropping a Column in SQL Server

by

Dropping or modifying a column in SQL Server can look straightforward, but it often isn’t. That column might be referenced by other objects in the database, and removing it without checking can break things silently. Unlike dropping a whole table, where SQL Server is very strict about dependencies, column-level references are not always enforced or even tracked. That’s why it’s important to do some homework before making the change.

Read more

What Does “Idempotent” Mean in SQL Programming?

by

In programming, the word idempotent describes an operation that produces the same result no matter how many times it is executed. When applied to SQL, idempotence refers to queries or commands that don’t introduce unexpected changes if you run them repeatedly.

The whole idea is that after the first execution, additional executions should leave the database in the same final state. Not just error-free, but stable and predictable. This concept is especially important when writing scripts that may be re-executed, such as database migrations or automated deployments.

Read more

Finding Foreign Keys that Use SET NULL for Deletes and Updates in SQL Server

by

When you set up foreign key relationships in SQL Server, you have a choice for how changes in the parent table affect related rows in the child table. One option is SET NULL, which replaces the foreign key value with NULL whenever the parent row is deleted or updated.

This behavior is useful in scenarios where you’d rather keep the child record around but cut the link once the parent no longer exists. For example, if a project is deleted, you might want to keep related tasks but mark their ProjectId as NULL.

The problem is that it’s not always obvious which foreign keys are configured with SET NULL, especially in large databases. Fortunately, SQL Server’s system views make it possible to query this information directly.

Read more