What is the advantage of using FAST_FORWARD for defining a cursor?

Question

What is the advantage of using FAST_FORWARD for defining a cursor? Is it better for performance? why?

Answer 1

FAST_FORWARD - specifies that cursor will be FORWARD_ONLY and READ_ONLY cursor. The FAST_FORWARD cursors produce the least amount of overhead on SQL Server.

Source: Click Here

Answer 2

The FAST_FORWARD specifies that it's FORWARD_ONLY and READ_ONLY, meaning it uses the least amount of server resources to handle it...so yes, for performance.

MSDN has a full rundown of cursor options here.

FAST_FORWARD

• Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.

Answer 3

The definition from MSDN is:

Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified the other cannot be specified.

I've boldened the key bit. It can support these "performance optimisations" because it does not need to support multi-direction iterating through the cursor (FORWARD_ONLY) and does not support modifications (READ_ONLY).

Of course, if you don't really need to use a cursor at all - then using a cursor even with this option is not going to perform as well . If you can do the same task using a set-based approach, do that instead - this is the bit I really wanted to stress.