Tag: t-sql

Using Subqueries with SQL Server’s DATEDIFF() Function

Posted on by Ian

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.

Continue reading →

PIVOT vs GROUP BY in SQL Server

Posted on by Ian

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.

Continue reading →

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

Posted on by Ian

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.

Continue reading →

Installing the Netflix Sample Database in SQL Server

Posted on by Ian

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.

Continue reading →

Fix Error 3234 “Logical file is not part of database” When Restoring a Database in SQL Server

Posted on by Ian

If you’re getting error 3234 that reads something like “Logical file ‘AdventureWorksLT_Data’ is not part of database ‘AdventureWorksLT2025’. Use RESTORE FILELISTONLY to list the logical file names.“, you’re referencing the wrong logical file names when restoring a database.

This issue can happen when you try to map the logical file names to a new location, but you get those logical file names wrong.

Fortunately there’s an easy fix. It involves looking up the actual logical file names, then modifying your RESTORE DATABASE statement accordingly.

Continue reading →

How to Exclude Specific Queries from Query Store in SQL Server

Posted on by Ian

SQL Server’s Query Store captures all queries by default (when using QUERY_CAPTURE_MODE = ALL) or resource-intensive queries (when using AUTO). However, you may want to exclude certain queries from being tracked. These might include monitoring queries, maintenance scripts, or queries from specific applications that add noise to your performance data.

SQL Server doesn’t provide a built-in mechanism to exclude specific queries from Query Store by query text or pattern. But you can achieve similar results through several approaches.

Continue reading →

Dynamic PIVOT in SQL Server for Unknown Column Values

Posted on by Ian

Most pivot operations in SQL tend to use hardcoded column lists. This is where you explicitly specify every column name in the IN clause. That works great when you know exactly which values will appear in your data. But what happens when those values change? When new categories get added, when you’re working with user-generated data, or when you’re building a query that needs to work across different datasets?

That’s when you might want to consider dynamic PIVOT. Instead of hardcoding column names, you query the data to discover what columns you need, build the PIVOT query as a string, and execute it dynamically. It’s more complex than static PIVOT, but it’s pretty much essential when your column values aren’t predetermined.

Continue reading →

Calculating Days Between a Fixed Date and Dynamic Dates with DATEDIFF()

Posted on by Ian

Sometimes you need to measure how many days have passed between a specific reference point and a constantly moving target. SQL Server’s DATEDIFF() function handles this elegantly by letting you combine hardcoded dates with dynamic functions like GETDATE(). This can be useful for calculating things like age, days since an event, or time remaining until a deadline.

The main point here is that DATEDIFF() doesn’t care whether its date arguments are literals, functions, or even subqueries. It just needs two date values to compare. When you use GETDATE() or similar functions, you’re telling SQL Server to calculate the difference based on the current moment, which means the result changes every time you run the query.

Continue reading →

Restoring SQL Server Backups in Docker on macOS with VS Code

Posted on by Ian

Whether you are a long-time Mac user or recently transitioned from Windows, running SQL Server on macOS brings a unique set of challenges. Most tutorials assume you’re on Windows using SQL Server Management Studio (SSMS), which is a tool that doesn’t exist for Mac. Instead, you’re likely running SQL Server inside a Docker container and using a tool like VS Code.

Continue reading →