DBMS

Database Management Systems

Pivoting JSON Data in SQL Server

by

JSON has become a common format for data exchange, and SQL Server’s built-in JSON support makes it straightforward to work with JSON data directly in your queries. But what happens when you need to pivot JSON data – transforming nested structures or array elements into a columnar format for reporting or analysis?

SQL Server provides functions like OPENJSON(), JSON_VALUE(), and JSON_QUERY() that let you extract and manipulate JSON data. Combined with standard pivoting techniques, you can reshape JSON data into whatever format you need. This can be particularly useful when you’re receiving JSON from APIs, storing semi-structured data, or working with configuration data that doesn’t fit neatly into traditional tables.

Read more

Complete Guide to SQL Server Data Types

by

SQL Server provides a solid set of system data types that handle everything from storing tiny integers to massive text blobs. Understanding these types is an important part of designing efficient databases, mainly because picking the right data type can save storage space and improve query performance.

This article breaks down all the data types available in SQL Server (as of SQL Server 2025), organized by category. Each type includes its max length, precision, scale, and whether it can be nullable.

Read more

Fix Error 4901 “ALTER TABLE only allows columns to be added that can contain nulls… etc” in SQL Server

by

If you’re getting an error in SQL Server that reads something like “ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or… etc etc“, it’s probably because you’re trying to add a NOT NULL column to a table that already contains data.

This error occurs due to the fact that the NOT NULL constraint will be violated for every row in the table. Think about it for a second. When you first add the column, there’s no data. It’s not until you run a subsequent INSERT statement (or some other process that populates the table) that you will get data. In the meantime, all values in your new column will be NULL. And that, of course, violates the NOT NULL constraint.

Read more

Using Subqueries with SQL Server’s DATEDIFF() Function

by

While SQL Server’s DATEDIFF() function is relatively straightforward when you’re comparing two known dates, it becomes more flexible when the dates you compare are sourced directly from your tables. Instead of hardcoding dates, you can embed subqueries directly into the DATEDIFF() function to dynamically retrieve the dates you need.

This approach can be especially useful when you’re working with aggregate functions like MIN() and MAX(), or when you need to pull specific dates based on certain conditions. The subqueries execute first, return their date values, and DATEDIFF() uses those results to perform the calculation.

Read more

PIVOT vs GROUP BY in SQL Server

by

If you’re new to SQL or just starting to explore data transformation techniques, you might be confused about when to use PIVOT versus GROUP BY. They may seem similar at first. After all, both aggregate data, both can summarize information, and both produce condensed result sets. But they serve fundamentally different purposes and produce very different output structures.

Understanding the distinction between these two operations will help you write more effective queries. GROUP BY aggregates data vertically, keeping your results in a row-based format. PIVOT transforms data horizontally, turning row values into column headers. Let’s break down exactly what each does and when you’d use one over the other.

Read more

SQL Server SUBSTRING(): A Complete Guide to Extracting Strings

by

The SUBSTRING() function in SQL Server lets you pull out specific portions of a string. Whether you’re cleaning data, formatting output, or parsing complex text fields, this function is one you’ll use constantly.

If you’ve ever needed to extract an area code from a phone number, grab the domain from an email address, or parse product codes into their component parts, SUBSTRING() is your go-to tool. It’s pretty straightforward, and once you understand how it works, you’ll find yourself using it all the time.

Read more

Installing the Netflix Sample Database in SQL Server

by

The Netflix sample database is a sample database that can be used for learning and practicing SQL. It uses data from the Netflix Engagement Report and the Netflix Global Top 10 weekly list.

This guide covers the installation process and gets you running queries quickly. For more background on what this database is and why it’s useful for learning, see my introduction to the Netflix sample database.

Read more