Avoid Using Cursors

Provided by: Molly Pell, Technical Project Manager and Total SQL Analyzer PRO

Transact-SQL cursors are used mainly in stored procedures, triggers, and Transact-SQL scripts in which they make the contents of a result set available to other Transact-SQL statements. A cursor can contain one, some, or all records from an executed SQL statement.

Cursors generally use a lot of resources and reduce the performance and scalability of applications. If you need to perform row-by-row operations, try to find another method to perform the task. For example, consider the following options:

1. Perform the task at the client

2. Use tempdb tables

3. Use derived tables

4. Use a correlated sub-query

5. Use the CASE statement

More often than not, there are non-cursor techniques that can be used to perform the same tasks as a SQL Server cursor.

If you have no choice but to use a server-side cursor in your application, try to use a FORWARD-ONLY or FAST-FORWARD, READ-ONLY cursor. When working with unidirectional, read-only data, use the FAST_FORWARD option instead of the FORWARD_ONLY option, as it has some internal performance optimizations to speed performance. This type of cursor produces the least amount of overhead on SQL Server.

If you are unable to use a fast-forward cursor, then try the following cursors, in this order, until you find one that meets your needs. They are listed in the order of their performance characteristics, from fastest to slowest: dynamic, static, and keyset

Return to the tips page