Operational SQL Server Cursors Interview Questions & Answers:

1. Explain the ways to controlling Cursor Behavior?

There are 2 ways to control Cursor behavior:

Cursor Types: Data access behavior depends on the type of cursor; forward only, static, keyset-drive and dynamic.
Cursor behaviors: Keywords such as SCROLL and INSENSITIVE along with the Cursor declaration define scrollability and sensitivity of the cursor.

2. Do you know the capabilities of Cursors?

Cursors can support various functionalities that are listed here.

Cursor allow to position at specific rows of the result set.
Cursor can retrieve one or more rows in the result set.
Cursor allows modification to the rows in the result set.

3. What is scrollable cursor?

You can use keyword SCROLL to make cursor Scrollable.
It can scroll to any row and can access the same row in the result set multiple times.
A non-scrollable cursor is also known as forward-only and each row can be fetched at most once.

4. Explain Temporary table VS Table variable by using Cursor alternative?

Temporary table

This can improve processing speed but consume disk space.

Table variable

Table variable that can be used in stored procedures, functions and batches.
Table variable get destroyed at the end of the stored procedure, function or batch in which it is defined.
Since it can be used in stored procedure, it is compiled once and can be used many times.
You can't create index on the Table variable.
Since you can create index on the temporary table, it is good where data is huge.

5. Explain the cursor lock types?

Three types of locks

READ ONLY: This prevents any updates on the table.
SCROLL LOCK: This allows you to make changes to the table.
OPTIMISTIC: This checks if any change in the table record since the row fetched before updating.
If there is no change, the cursor can update

6. How to avoid cursors?

The main purpose that cursors fulfill is traversing of the row set. Cursors can be best avoided by:

a. Using the SQL while loop: Using a while loop we can insert the result set into the temporary table.

b. User defined functions : Cursors are sometimes used to perform some calculation on the resultant row set. This cam also be achieved by creating a user defined function to suit the needs

7. Explain the disadvantages of cursors?

Disadvantages of cursors

Uses more resources because Each time you fetch a row from the cursor, it results in a network roundtrip
There are restrictions on the SELECT statements that can be used.
Because of the round trips, performance and speed is slow

8. Can you explain the disadvantages/limitation of the cursor?

Cursor requires a network roundtrip each time it fetches a record, thus consume network resources.
While data processing, it issues locks on part of the table, or on the whole table.

9. What is Explicit cursors?

Explicit cursors: these cursors are not invoked implicitly. User needs to create, open, fetch or close the cursor.

10. What is Static Cursor?

Static Cursor: Stores a complete copy of the result set. Used mostly where scrolling is required. Static cursors don't support updates.