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.
What is an Index Scan?
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.
What is an Index Seek?
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.
Using Subqueries Inside DATEADD() for Dynamic Date Calculations
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.
What is Query Optimization?
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.
Creating Calendar View Reports in SQL Server
Calendar views are one of those report formats that instantly make data more digestible. Instead of scrolling through rows of dates, you get a grid that shows patterns or trends at a glance. You can instantly see which days of the week are busiest, which months see the most activity, or how different time periods compare. SQL Server doesn’t have a built-in calendar view function, but with pivoting techniques and a bit of creativity, you can build exactly what you need.
What is a UNION ALL in SQL?
UNION ALL is SQL’s way of combining result sets from multiple queries without worrying about duplicates. If you’ve used UNION before, UNION ALL is its faster, less fussy sibling that keeps every single row from all your queries, even if some rows are identical.
So UNION removes duplicate rows automatically, while UNION ALL keeps everything.
Using DATEDIFF() with LAG() to Calculate Time Between Events
Window functions and date calculations make a powerful combination when you need to analyze patterns over time. One interesting pairing is DATEDIFF() with the LAG() function, which lets you compare each row’s date against the previous row’s date within an ordered dataset. This can be handy for calculating time gaps between sequential events like maintenance intervals, customer order frequency, or processing delays.
The LAG() function retrieves a value from a previous row in the result set without requiring a self-join. When you combine it with DATEDIFF(), you can measure the time elapsed between consecutive events in a single pass through your data. This approach is both more readable and more performant than traditional self-join methods.
What is a UNION in SQL?
If you ever find yourself needing to combine the results from multiple SELECT statements into a single result set, UNION is probably going to be the tool for the job. By “UNION“, I mean the UNION operator.
The UNION operator takes the output from two SELECT queries and stacks them on top of each other. It basically merges two lists into one, removing any duplicates along the way.
How to Pivot Rows to Columns in SQL Server (5 Methods)
Pivoting takes data stored vertically in rows and spreads it horizontally into columns. This is something you’ll likely encounter regularly when building reports or reshaping data for analysis. Basically, you’ve got data stored in rows, and you need to flip it so those row values become column headers. Maybe you’re building a report, maybe you’re feeding data to another system, or maybe the client just wants to see things the other way around.
SQL Server gives you several ways to handle this. Let’s walk through five different approaches, from the dedicated PIVOT operator to more flexible techniques that work when you need extra control.