Avoid Using Cursors in SQL Server
Provided by: Molly Pell, Technical Project Manager
Transact SQL cursors are used mainly in stored procedures,
triggers, and Transact-SQL scripts where they make the contents of a
result set available to other Transact SQL statements. A cursor may 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:
- Perform the task at the client
- Use tempdb tables
- Use derived tables
- Use a correlated sub-query
- 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.