Does Sort Order Affect Performance?

[Sunite]

Does Sort Order Affect Performance?

Q

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



A


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 does not 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 not use an index to search for data if a function such as UPPER() uses an indexed column. You will 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 will 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.