what is

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

Comparing COL_LENGTH() and DATALENGTH() in SQL Server

by

SQL Server has a COL_LENGTH() function and a DATALENGTH() function that could easily be confused for doing the same thing. They both have “length” in their name, and they do indeed return a “length”. But the length returned is different for each function.

If you’ve ever wondered why DATALENGTH() gives you different numbers than COL_LENGTH(), read on to find out.

Read more

A Quick Look at SQL Server’s MIN() Function

by

The MIN() function in SQL Server returns the smallest value from a set of rows. It’s commonly used to find earliest dates, lowest prices, or in general the minimum of any column. While the function itself is simple, you may encounter it written with options like DISTINCT, ALL, or as a window function with OVER(). Some of these options don’t actually change the result in SQL Server but exist for standards compatibility, so it’s worth understanding what they mean if you ever see them in code.

Let’s take a look at a few simple examples to see how it works.

Read more

Using MAX() in SQL Server

by

The MAX() function is one of SQL Server’s simplest aggregate functions. It returns the largest value from a column. While it’s usually straightforward, there are a few useful ways to apply it depending on whether you’re using it as a plain aggregate or as a window function with OVER().

You might also see MAX() that use a DISTINCT clause. Truth be told, this doesn’t actually change the results. That clause is only for standards compatibility.

In any case, let’s walk through some examples to see how it all works.

Read more

A Quick Look at SQL Server’s DATETRUNC() Function

by

SQL Server 2022 introduced the DATETRUNC() function, which makes working with date and time values much easier. It trims (or “truncates”) a date/time value down to a specified part (like year, month, or week) while setting all smaller units to their starting value. This helps avoid the common hack of mixing DATEADD() and DATEDIFF() just to snap a timestamp to the beginning of a period.

In this article we’ll look at some examples that demonstrate how it works.

Read more

Common Causes of “Multi-Part Identifier Could Not Be Bound” in SQL Server

by

If you’ve worked with SQL Server for a while, you’ve probably run into the dreaded 4101 error that looks something like Msg 4104, Level 16, State 1, Line X: The multi-part identifier “X.Y” could not be bound.

It’s one of those vague errors that doesn’t immediately tell you what’s wrong. Basically SQL Server is complaining because it doesn’t know how to resolve the reference you wrote. This is usually a column or alias.

Let’s take a look at the most common causes, with examples to make them easier to spot.

Read more

Understanding FORMATMESSAGE() in SQL Server

by

When you’re working with SQL Server, sometimes you don’t just want to throw an error. Sometimes you want to build a message you can actually use elsewhere. That’s where FORMATMESSAGE() comes in. Instead of immediately printing a message like RAISERROR does, FORMATMESSAGE() gives you the formatted string back so you can decide what to do with it. This could include logging it, storing it, displaying it, or simply passing it along.

In simple terms, you can think of it as a way to take a predefined message from sys.messages (or even a custom string you provide) and turn it into a neatly formatted output. This can be quite handy when you need more control over how messages are handled in your SQL workflows.

Read more

OPENJSON() vs JSON_VALUE() When Parsing JSON in SQL Server

by

Working with JSON in SQL Server often comes down to choosing the right function for the job. Two of the most common options are OPENJSON() and JSON_VALUE(). Both are designed to pull data out of JSON documents, but they work in very different ways and are suited to different scenarios. Knowing when to use each one can save time and simplify your queries.

This article breaks down how OPENJSON() and JSON_VALUE() handle JSON parsing, what makes them different, and the situations where one might be a better fit than the other. Whether you are cleaning up nested JSON or just extracting a single value, understanding these functions will help you work more effectively with JSON data in SQL Server.

Read more

The Difference Between LIST_SELECT() and LIST_SLICE() in DuckDB

by

DuckDB has a list_select() function and a list_slice() function, and both do a similar thing. They allow us to extract values from lists based on their index in the list. But they’re quite different in the way they work. One function allows us to select elements based on a range, while the other function allows us to handpick each element we want returned.

Read more