SQL Server中YEAR函数与日期范围查询的性能差异及优劣对比

Great question—this is a super common pitfall when working with date indexes in SQL Server, so let's break it down clearly.

Performance Differences #

• Query 1 (OrderDate >= '2000-01-01' and OrderDate <= '2000-12-31'): This query is optimized to use the nonclustered index on OrderDate. Since we're comparing the indexed column directly to a date range, SQL Server can perform an index seek—it jumps straight to the start of the 2000 date range in the index, then scans only the relevant rows. Even though we're selecting * (which requires pulling data from the clustered index via bookmark lookup), the initial index seek keeps IO and execution time low, especially on large tables.
• Query 2 (YEAR(OrderDate) = 2000): This query breaks index usage entirely. When you wrap an indexed column in a function like YEAR(), SQL Server can't use the index's sorted structure to filter rows. Instead, it has to do an index scan or full table scan: it reads every single row, calculates YEAR(OrderDate) for each one, then checks if it equals 2000. This is way slower on large datasets—you're forcing the database to do unnecessary work for every row.

Core Differences Between the Two Queries #

The biggest distinction boils down to SARGability (short for "Search Argument Able"):

• Query 1 is SARGable: It compares the raw indexed column to static values, so the database engine can leverage the index to quickly locate matching rows.
• Query 2 is non-SARGable: Applying a function to the indexed column modifies its value before comparison, which invalidates the index's sorted order. The database can't use the index to narrow down results, so it has to scan all rows.

Other key differences:

• Index Usage: Query 1 uses an efficient index seek; Query 2 resorts to a scan (full table or index).
• CPU Overhead: Query 2 adds extra CPU load from running YEAR() on every row, while Query 1 has no such overhead.

Which Query is Better for Performance? #

Without a doubt, Query 1 is the better choice. It fully utilizes the existing nonclustered index on OrderDate, minimizing the number of rows the database needs to read and process. As your Orders table grows, the performance gap between the two queries will become more and more pronounced—Query 2 could end up taking seconds (or minutes) while Query 1 finishes in milliseconds.

内容的提问来源于stack exchange，提问作者Beshoy Hindy