Views, indexes, and query performance

Searchable predicates and SARGability

Write key ranges that can use an index search condition.

Lesson 75 of 84

Reading: about 2 min

Lesson + practice: about 15 min

Difficulty: Advanced

On this page4 sections

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Write key ranges that can use an index search condition.

Run the example

sql

SELECT OrderID, OrderDate
FROM dbo.Orders
WHERE OrderID >= 100 AND OrderID < 105
ORDER BY OrderID;

Read-only T-SQL against dataset v1.0.0

The predicate keeps the indexed OrderID column unwrapped.
The half-open range includes IDs 100–104 and excludes 105.
This shape is searchable, but the optimizer still chooses its plan based on cost.

The result order comes from ORDER BY, not from index storage.

Return Orders 200–202 using a half-open OrderID range.

Check your answer and expected result

Reveal answer

sql

SELECT OrderID, OrderDate
FROM dbo.Orders
WHERE OrderID >= 200 AND OrderID < 203
ORDER BY OrderID;