How PIVOT Works in SQL Server

by

In SQL databases, a pivot operation transforms rows into columns, making it easier to summarize or compare data across categories. It’s commonly used to convert long, vertical datasets into a wider, more readable format. For example, turning a list of monthly sales records into a table where each month becomes its own column.

By applying aggregation functions like SUM(), COUNT(), or AVG() during the pivot, SQL can reorganize and summarize data for reporting or analysis.

In this article, we’ll take a look at SQL Server’s PIVOT operator, which is designed specifically for pivot operations.

Read more

What is the Query Store in SQL Server?

by

Query Store is SQL Server’s built-in query performance tracking system. It captures a history of queries, their execution plans, and runtime statistics, storing everything in the database itself. It constantly records what’s happening so you can analyze performance issues after the fact.

Query performance issues can be notoriously hard to debug. A query runs fine for weeks, then suddenly it’s slow, and by the time you check, the problem has vanished or the execution plan is no longer in cache. SQL Server 2016 introduced Query Store to address this. Once enabled on a database, it continuously records query execution history, giving you the data you need to investigate performance problems after they happen. It won’t tell you what’s wrong or how to fix it, but at least you’ll have evidence to work with instead of flying blind.

Read more

What is a Query Hint?

by

A query hint is a directive you add to your SQL statement that tells the database optimizer how to execute that query. You’re basically overriding the optimizer’s judgment with your own instructions.

Most of the time, your database’s query optimizer does a pretty solid job figuring out the best execution plan. It analyzes statistics, indexes, and table structures to determine the most efficient path. But sometimes you know better (or at least you think you do) and that’s where query hints can be useful.

Read more

Using DATEDIFF() with Window Aggregate Functions to Calculate Time from Event Baselines in SQL Server

by

When you combine SQL Server’s aggregate functions like MIN() and MAX() with the OVER clause, you can use them as window functions that calculate across partitions while still maintaining individual row data. When combined with DATEDIFF(), it lets you calculate how much time has elapsed from a baseline date within each partition. This can be useful for doing stuff like measuring durations from the start of a process, tracking time since the first event in a group, or calculating age from an initial reference point.

The main advantage of using MIN() or MAX() as a window function is that you can compare every row in a partition against the earliest or latest date in that same partition without needing a self-join or subquery. Each row gets access to the aggregate value while still maintaining its individual row data.

Read more

What is a Query Execution Plan?

by

A query execution plan is a detailed roadmap that shows exactly how a database will execute your SQL query. When you submit a query, the database doesn’t just start grabbing data randomly. Rather, it creates a step-by-step strategy for retrieving and processing your data in the most efficient way possible.

The query execution plan is that strategy made visible.

Basically, the SQL you write tells the database what you want, but the execution plan shows you how it’s actually going to get it. This includes which tables it’ll scan, what indexes it’ll use, how it’ll join tables together, and in what order everything will happen.

Read more

Building Dynamic Reports with Month and Weekday Labels in SQL Server

by

When you’re building reports in SQL Server, there’s a good chance you’ll need to display dates in a more human-readable format than the ISO 8601 standard that will likely be returned in the absence of any formatting. Nobody wants to see “2024-03-15” when “March” or “Friday” would make the report instantly clearer. SQL Server gives you several ways to extract and format these labels, and knowing which approach fits your situation can save you time and make your queries cleaner.

Read more

What is an Index Scan?

by

An index scan is a method databases use to retrieve data by reading through an index from start to finish. The database reads every entry in the index sequentially, checking each one to see if it matches your query conditions.

This is different from an index seek, where the database jumps directly to specific values in the index. Index scans happen when the database determines it needs to examine a large portion of the index, or when it can’t use the index’s sorted structure to go directly to the data you need.

Read more

What is an Index Seek?

by

An index seek is the fastest way a database can use an index to find data. When you perform a seek, the database jumps directly to the exact location in the index where your data lives, grabs what it needs, and moves on. No scanning, no reading through irrelevant entries. Just a precise lookup using the index’s sorted structure.

This is fundamentally different from an index scan, where the database reads through the index sequentially. Seeks are only possible when your query conditions allow the database to pinpoint specific index entries without examining others.

Read more

Using Subqueries Inside DATEADD() for Dynamic Date Calculations

by

SQL Server’s DATEADD() function doesn’t just accept literal values or column references – it can work with subqueries too. This means you can calculate date offsets based on aggregated data, lookups from other tables, or any scalar subquery that returns a single numeric value. The technique is particularly useful when you need to derive both the base date and the offset from your data rather than having them readily available in the current row.

The main requirement is that each subquery must return exactly one value. DATEADD() expects a scalar for both the interval amount and the base date, so your subqueries need to use aggregation functions, TOP 1, or other methods to ensure a single-row result.

Read more

What is Query Optimization?

by

Query optimization is the process of finding the most efficient way to execute a database query.

When you write a SQL query, you’re basically telling the database what data you want, but the database has to figure out how to actually retrieve it. That’s the main job of the query optimizer. The query optimizer is a dedicated component of the database management system (DBMS) that evaluates various possible execution paths and selects the most efficient one.

But there are also things that we can do to help the query optimizer, such as writing efficient SQL, properly indexing tables, maintaining up-to-date statistics, etc.

Understanding how the optimizer works and knowing how to steer it toward better execution plans is what we mean by query optimization.

Read more