Quick Check

1.	How do you remove a cursor from server memory?
2.	What are some commonly used alternatives to using a cursor?
3.	Which concurrency option requires the least amount of resources?
4.	Which cursor type also includes the READ_ONLY concurrency option?
5.	What function enables you to monitor cursors in real time?

Answers

1.	You need to perform a CLOSE <cursorname> to release any locks associated with the cursor, but the DEALLOCATE <cursorname> command will actually release the cursor from server memory.
2.	In place of a cursor, you might want to consider using the built-in functionality available with SQL Server 2005 and combine the logic into a single SELECT statement. You might also be able to use a WHILE loop, CASE function, or even a recursive query.
3.	The READ_ONLY concurrency option indicates that no updates will occur within the cursor. This is the fastest and most efficient concurrency option.
4.	The FAST_FORWARD cursor type indicates that the cursor will scroll in only one direction, but it also indicates that the cursor will not include any updates and therefore utilizes the READ_ONLY concurrency option.
5.	The sys.dm_exec_cursors dynamic management function enables you to monitor currently allocated cursors. This enables you to easily identify any orphaned cursors in your SQL Server.