SQL Server Cursors Interview Preparation Guide
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 youre interview-ready.18 MS SQL Server Cursors 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
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
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
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
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.
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.
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
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
5 :: What is Implicit cursors?
Implicit cursors: these cursors are invoked implicitly. User need not create, open, fetch or close the cursor.
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.
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.
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.
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.
10 :: What is Explicit cursors?
Explicit cursors: these cursors are not invoked implicitly. User needs to create, open, fetch or close the cursor.