Microsoft SQL Server 2008 R2 Unleashed- P172:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1654 CHAPTER 43 Transact-SQL Programming Guidelines Tips and Tricks UNION Versus UNION ALL Performance You should use UNION ALL instead of UNION if there is no need to eliminate duplicate result rows from the result sets being combined with the UNION operator. The UNION statement has to combine the result sets into a worktable to remove any duplicate rows from the result set. UNION ALL simply concatenates the result sets together without the overhead of putting them into a worktable to remove duplicate rows. Use IF EXISTS Instead of SELECT COUNT You should use IF EXISTS instead of SELECT COUNT when checking only for the existence of any matching data values and when determining the number of matching rows is not required. IF EXISTS stops the processing of the select query as soon as the first matching row is found whereas SELECT COUNT continues searching until all matches are found wasting I O and CPU cycles. For example you could replace if SELECT count FROM WHERE stor_id 6380 0 with an IF EXISTS check similar to if exists SELECT FROM WHERE stor_id 6380 Avoid Unnecessary ORDER BY or DISTINCT Clauses When a T-SQL query contains an ORDER BY or DISTINCT clause a worktable is often required to process the final result of the query if it cannot determine that the rows will already be retrieved in the desired sort order or that a unique key in the result makes the rows distinct. If a query requires a worktable that adds extra overhead and I O to put the results into the worktable in tempdb and do the sorting necessary to order the results or to eliminate duplicate rows. This can result in extended processing time for the query which can delay the time it takes for the final result to be returned to the client application. If it is not absolutely necessary for the rows returned to the application to be in a specific order for example returning rows to a grid control where the contents can be re-sorted by any column in the grid control itself you .
