7 Ways to Extract Data from JSON in DuckDB

by

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.

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

2 Ways to Format Numbers with Thousand Separators and Decimals in SQL Server

by

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.

Read more

Top 5 Data Conversion Errors in SQL Server and How to Avoid Them

by

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.

Read more

How to Replace INSTR() with CHARINDEX() and PATINDEX() in SQL Server

by

If you’ve worked with Oracle or MySQL before, you may have used the INSTR() function to find the position of a substring inside a string. But when you’re porting code to SQL Server, you’ll quickly notice that INSTR() isn’t available. Instead, you’ll need to use either CHARINDEX() or PATINDEX(), depending on what you’re trying to do.

The good news is that both functions are pretty straightforward once you know the difference. CHARINDEX() handles simple substring searches, while PATINDEX() adds the ability to use patterns. In this article, we’ll walk through how each one works and how you can swap them in when you’d otherwise use INSTR().

Read more

Connect to Azure SQL Database from Your Mac

by

If you’ve set up an Azure SQL Database, you’ll almost certainly want to connect to it at some point. You’ll probably want to connect to it, and then run a bunch of SQL queries, just like you would do if you had SQL Server installed locally or across the network.

Fortunately, connecting to Azure SQL Database is relatively straight forward. Let’s look at two options for connecting via a Mac: SQL command line tools and VS Code.

Read more

How to Drop and Recreate a Primary Key Constraint in SQL Server

by

In relational database management systems (RDBMSs) like SQL Server, the primary key constraint is a fundamental element that uniquely identifies each record in a table and enforces data integrity. However, there are situations where you might need to drop and then recreate this constraint. Such situations could include modifying the primary key columns, changing data types, resolving design issues, etc.

Understanding how to safely remove and reapply primary key constraints is extremely important for maintaining database consistency and minimizing downtime during schema changes.

In this article, we’ll walk through how to safely drop and then re-add a primary key constraint without breaking your database.

Read more

Using DROP TABLE IF EXISTS When Working with Temporary Tables in SQL Server

by

In SQL Server, when working with temporary tables, it’s common to ensure a temp table with the same name doesn’t already exist before creating it. This prevents any unwanted errors. SQL Server doesn’t support CREATE TABLE IF NOT EXISTS like some other DBMSs do, so you must explicitly check and drop the table beforehand.

Read more

Converting Between Time Zones in SQL Server with AT TIME ZONE

by

Converting between time zones in SQL Server has evolved throughout the years. Prior to SQL Server 2016, there was no simple, built-in function to handle this task. We had to use a complex, multi-step approach involving functions like SWITCHOFFSET() and TODATETIMEOFFSET(), and we had to manually account for Daylight Saving Time (DST) rules for each time zone. This method was often prone to error and required constant maintenance to keep up with changing time zone and DST regulations.

Read more