One of the most common causes of errors in SQL Server is trying to insert the wrong type of data into a column. If a column expects an integer but you push in a string, SQL Server will either throw an error or attempt an implicit conversion that may not work the way you expect. This is more common when data comes from external sources like APIs, flat files, or user inputs, where you don’t always control the formatting. Validating data types before inserting not only prevents runtime issues but also keeps your tables clean and predictable.
how to
How to Export SQL Server Query Results as JSON with FOR JSON AUTO
SQL Server makes it surprisingly simple to generate JSON directly from a query. If you’ve ever had to send data to a web service, feed an API, or just save query results in a structured text format, JSON is a natural choice. Instead of writing complex formatting logic in your application, you can let SQL Server do the heavy lifting with the FOR JSON clause. And you can use the AUTO keyword to have the JSON formatted automatically by SQL Server.
In this article, we’ll look at how FOR JSON AUTO works, along with some examples to demonstrate its usage.
Common Causes of “Multi-Part Identifier Could Not Be Bound” in SQL Server
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.
Format Current Date as DD/MM/YYYY in SQL Server
If you’re in a country that uses DD/MM/YYYY format for your dates, then you’ll likely find yourself needing to display the current date in that format when you generate reports or data that’s intended to be read by humans.
Here are four methods to format the current date as DD/MM/YYYY in SQL Server.
Split and Re-Aggregate Delimited Strings in SQL Server
Working with delimited strings in SQL Server can be messy. Maybe you inherited a table where a column holds multiple values separated by commas, or you need to take a list and break it apart before putting it back together in some aggregated form. While best practice is to normalize data into proper relational tables, sometimes you don’t control the schema. Or perhaps you’re just solving a one-off reporting need.
This article walks through how to split delimited strings into rows, process them, and then re-aggregate them back into a single string, all within SQL Server.
Handling Unix Timestamps in SQL Server
Unix timestamps (also known as epoch time) are a simple way of representing a point in time: the number of seconds that have passed since 00:00:00 UTC on January 1, 1970 UTC. They’re popular in APIs, logs, and systems that need a compact, language-neutral way to store time.
If you’re working with SQL Server, you’ll almost certainly run into Unix timestamps eventually. Either you’re getting them from an external system or you need to produce them for one. Let’s walk through how to handle them in SQL Server.
7 Ways to Extract Data from JSON in DuckDB
Most DuckDB distributions come with the JSON extension, and this extension is loaded upon first use. That means we can go ahead and run queries against JSON data right out of the box. One common task we’ll face when working with JSON is extracting data from within the JSON documents. This can include extracting scalar values, or extracting nested JSON from within the outer document.
DuckDB provides us with multiple ways to extract such data. The option we use will largely depend on our use case. Either way, here are seven options for extracting data from JSON documents in DuckDB.
OPENJSON() vs JSON_VALUE() When Parsing JSON in SQL Server
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.
2 Ways to Format Numbers with Thousand Separators and Decimals in SQL Server
When you need to make numbers easier to read in SQL Server, adding thousand separators and controlling decimal places can make all the difference. This is especially true in reports or user-facing queries. Instead of squinting at a long string of digits, formatted output lets values like 1234567.89 appear as 1,234,567.89, making them much quicker to understand.
SQL Server offers a few ways to do this, but two stand out for their simplicity and reliability. The first is with the FORMAT() function, which gives you full control over how numbers look (and even supports different cultures). The second is the CONVERT() approach, which works well for quick, no-frills formatting when using the money data type. Both are easy to use, and I provide examples of each in this article.
Top 5 Data Conversion Errors in SQL Server and How to Avoid Them
Data conversion errors can be a frequent source of frustration when working with databases. And SQL Server is no exception. Such errors can interrupt workflows and lead to inconsistent results. While data conversion errors often happen during explicit conversions, they aren’t unique to this. Oftentimes the error can be due to an implicit conversion.
This article outlines five of the most common data conversion errors and provides practical steps to avoid them.