I have a table that includes a single-column primary key called IdValue. Given a particular value for IdValue, I want to find the table rows that are immediately before and after my target value (assuming the result set is sorted by IdValue). How can I avoid using cursors and get the result I want with a set-based approach?
T-SQL is a set-based language and doesn't easily lend itself to solutions that assume positioning of particular rows within a result set. However, the performance of server-side ANSI T-SQL cursors is usually much worse than the performance of a set-based solution, so learning different techniques for solving problems such as this is important.
Let's use the Northwind database's Orders table for an example. We'll restate the problem like this: How can I find the row immediately before and immediately after a particular row in the Orders table? We'll assume we're ordering the result set on the OrderId column.
Creative use of SQL Server's MIN(), MAX(), and TOP capabilities can help you deal with positional result set questions such as this one. Listing 1 and Listing 2 show two similar but slightly different approaches for solving the problem. Listing 1 provides a general solution because it works with MIN() and MAX() end points for @TargetOrder. However, I wanted to demonstrate the flexibility that TOP can provide in some cases. Note that the query in Listing 2 returns an empty set when you provide the minimum OrderId (10248) from the Orders table as an argument because the query assumes a row will always exist before the @TargetOrder row.
You typically have more than one way to generate a result set, and one approach is usually more efficient than the others. When you evaluate SHOWPLAN and SET STATISTICS IO information for both of these examples, you'll see that Listing 2, which uses the TOP statement, is slightly more efficient than Listing 1. The differences are small because the sample data sets are small, but it's important to test the performance of different query approaches when more than one option exists.