SQL Server Cursors Interview Questions And Answers

Download MS SQL Server Cursors Interview Questions and Answers PDF

Optimize your MS SQL Server Cursors interview preparation with our curated set of 18 questions. These questions will test your expertise and readiness for any MS SQL Server Cursors interview scenario. Ideal for candidates of all levels, this collection is a must-have for your study plan. Download the free PDF to have all 18 questions at your fingertips. This resource is designed to boost your confidence and ensure you're interview-ready.

18 MS SQL Server Cursors Questions and Answers:

MS SQL Server Cursors Job Interview Questions Table of Contents:

MS SQL Server Cursors Job Interview Questions and Answers
MS SQL Server Cursors Job Interview Questions and Answers

1 :: Explain the steps to use Transact-SQL Cursor?

Declare the cursor,
Open the cursor,
Fetch record row by row,
Close cursor,
Deallocate cursor.

Example of a cursor

Declare @EmpId int
Declare curEmp CURSOR READ_ONLY FOR SELECT EmpId FROM Employee
Open curEmp
Fetch next from curEmp into @EmpId
While @@FETCH_STATUS = 0
Begin
Print @EmpId
Fetch next from curEmp into @EmpId
End
Close curEmp
Deallocate curEmp
Read More

2 :: What is cursor in MS SQL Server?

A Cursor is a database object that represents a result set and is used to manipulate data row by row.
When a cursor is opened, it is positioned on a row and that row is available for processing.
SQL Server supports three types of cursor namely Transact-SQL server cursor, API server cursor, and client cursor.
Transact-SQL Server cursors use Transact-SQL statements and are declared using DECLARE CURSOR statement.
Transact-SQL Server cursors can be used in Transact-SQL scripts, stored procedures, and triggers.
Transact-SQL cursors are implemented on the server.
You can fetch only one row at a time in Transact-SQL Server cursors.
You can use FETCH statements with Transact-SQL cursors to retrieve rows from a cursor’s result set.
API server cursors support the API cursor functions.
API server cursors are implemented on the server.
API server cursors support fetching blocks of rows with each fetch.
A cursor fetches multiple rows at a time is called a block cursor
Read More

3 :: Do you know the cursor optimization tips?

Close cursor when it is not required.
You shouldn’t forget to deallocate cursor after closing it.
You should fetch least number of records.
You should use FORWARD ONLY option when there is no need to update rows.
Read More

4 :: Do you know the cursor types?

DYNAMIC: It reflects changes happened on the table while scrolling through the row.
STATIC: It works on snapshot of record set and disconnects from the server. This kind doesn’t reflects changes happened on the table while scrolling through the row.
KEYSET: In this kind, new record is not reflected, but data modification can be seen
Read More

5 :: What is Implicit cursors?

Implicit cursors: these cursors are invoked implicitly. User need not create, open, fetch or close the cursor.
Read More

6 :: What is Key set driven?

Key set driven : It is a scrollable cursor that cannot be updated. These cursors are controlled by a set of physical identifiers called as key set. The keyset is built in a temporary table when the cursor is opened.
Read More

7 :: What is Forward – only cursors / Read only cursor?

Forward – only cursors / Read only cursor: These are the fastest of the cursors and cannot be updated. They cannot be created on query that returns only read only columns.
Read More

8 :: Explain Forward – only cursors?

Forward – only cursors: This cursor supports updates but not scrolling. It supports only fetching serially. Rows are not retrieved from the database until they are fetched.
Read More

9 :: 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.
Read More

10 :: What is Explicit cursors?

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

11 :: 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.
Read More

12 :: 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
Read More

13 :: 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
Read More

14 :: 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
Read More

15 :: 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.
Read More

16 :: 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.
Read More

17 :: 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.
Read More

18 :: 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.
Read More