Does Sort Order Affect Performance?

Editor's Note: Send your SQL Server questions and comments to SQL Server MVP Brian Moran at [email protected].

I've seen information about how sort order affects performance in SQL Server 6.5, but I can't find similar information about sort-order performance in SQL Server 2000 or 7.0. I'm trying to decide if using binary sort makes sense for my SQL Server 2000 application. What's the difference between the performance of sort orders that SQL Server 2000 supports?

Remember that the relative performance difference between sort orders is limited to the number of CPU cycles that SQL Server spends sorting and comparing data. A faster sort order doesn't necessarily translate to a performance improvement. In fact, picking a "faster" sort order, such as binary, might cause performance to degrade in other parts of your application.

For example, let's say you have a query that searches by last_name. The rules of binary sort dictate that Smith doesn't equal smith. Your application developers could implement business logic that requires all data to be either uppercase or lowercase. But a more practical solution is to use an UPPER() or LOWER() function against the data in your table when you search for last_name. Unfortunately, SQL Server can't use an index to search for data if a function such as UPPER() uses an indexed column. You'll probably have to use a table scan rather than an index. In such a case, any performance gains you achieve by using binary sort are more than offset by the huge performance hit you'll take by using the full table scan. Forget about the possible performance differences between sort orders, and decide which sort order best meets the needs of your application developers and end users.