Tech Point Fundamentals

Friday, September 9, 2022

SQL Interview Questions and Answers - Part 16

SQL Interview Questions and Answers - Part 16

sql-interview-questions-answers

Are you preparing for the SQL Interview? If yes, then you are at the right place. This is the SQL Interview Questions and Answers article series. Here we will see the Top 100+ SQL Interview Questions with Answers. 

Please visit our YouTube Channel for Interviews and other videos by below link:




Please read the complete Design Pattern, C#, MVC, WebAPI, and .Net Framework Interview Questions and Answers article series here.




Introduction


This is the 16th part of the SQL Interview Questions and Answers article series. Each part contains eight to ten SQL Interview Questions with Answers. Please read all the SQL Interview Questions list here.

I will highly recommend to please read the previous parts over here before continuing the current part:





SQL Interview Questions and Answers - Part 16


Q141. What is the Cursor in SQL? 

A SQL cursor is an object that enables traversal over the rows of a result set. It allows you to process individual rows returned by a query. It is T-SQL logic, which allows us to loop through the related query result and enables us to take the actions sequentially on the row.

We know that SQL works based on a set or set of results or rows. The SELECT statement returns a set of rows which is called a result set.  But, sometimes, we may need to process a data set on a row-by-row basis. This is where cursors come into play. 



So cursors are primarily used where set-based operations are not applicable and you are required to access data and perform operations one row at a time rather than applying a single set-based operation to an entire object. A SQL cursor provides access to data one row at a time, thereby giving you direct row-by-row control over the result set.

The purpose of the cursor may be to update one row at a time or perform an administrative process such as SQL Server database backups in a sequential manner. SQL Server cursors are mainly used for Development, DBA, and ETL processes.

The Permissions of DECLARE CURSOR default to any user that has SELECT permissions on the views, tables, and columns used in the cursor.



Cursor Fundamental Points:

  1. Cursors use variables to store values returned in each part of the loop. Therefore, you’ll need to DECLARE all variables you’ll need.
  2. Cursor names can be referenced only by other Transact-SQL statements. They cannot be referenced by database API functions.
  3. The cursor rows cannot be fetched using the fetch functions or methods of the APIs; the rows can be fetched only by Transact-SQL FETCH statements.
  4. After a cursor has been declared, some system stored procedures can be used to determine the characteristics of the cursor:



sp_cursor_list: It returns a list of cursors currently visible on the connection and their attributes.
sp_describe_cursor: It describes the attributes of a cursor, such as whether it is a forward-only or scrolling cursor.

sp_describe_cursor_tables: It describes the base tables accessed by the cursor.
sp_describe_cursor_columns: It describes the attributes of the columns in the cursor result set.




Q142. What is the Cursor Life Cycle in SQL? 

Creating a SQL Server cursor is a consistent process. The process of using a SQL cursor can be generally described as follows:

1. Declare Cursor: 

First, declare the cursor with a specific name. The cursor name can be anything meaningful which is immediately followed by opening the cursor.

To declare a cursor, you specify its name after the DECLARE keyword with the CURSOR data type and provide a SELECT statement that defines the result set for the cursor.

DECLARE CURSOR accepts both a syntax based on the ISO standard and a syntax using a set of Transact-SQL extensions.

DECLARE CURSOR defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. 

DECLARE cursor_name CURSOR
FOR select_statement;



2. Open Cursor:

The OPEN Cursor statement populates the result set. It opens and populates the cursor by executing the SELECT statement:

OPEN cursor_name;

3. Fetch Rows:

The FETCH returns a row from the result set for processing. It is used to fetch a row from the cursor into one or more variables for data processing.

There should be an equal number of variables declared for the cursor, columns in the SELECT statement, and variables in the Fetch logic. However, it may depend based on the requirement.

FETCH NEXT FROM cursor INTO variable_list;

SQL Server provides the @@FETCHSTATUS function that returns the status of the last cursor FETCH statement executed against the cursor.

If @@FETCHSTATUS returns 0, meaning the FETCH statement was successful. You can use the WHILE statement to fetch all rows from the cursor.

WHILE @@FETCH_STATUS = 0  
BEGIN
FETCH NEXT FROM cursor_name;  
END;



4. Close Cursor:

Once all of the data has been processed, then you need to close the cursor. It releases the current data and associated locks but permits the cursor to be re-opened again if required. The CLOSE statement releases the current result set associated with the cursor.

CLOSE cursor_name;



5. Deallocate Cursor:

Finally at last you need to deallocate the cursor to release all of the internal resources the SQL Server is holding. The DEALLOCATE statement releases the resources used by the cursor and destroys the cursor.

DEALLOCATE cursor_name;

Example: The below cursor will loop through each row and print the record one by one:



-- CURSOR Variable Declarations
DECLARE @EmpID INT;
DECLARE @EmpName VARCHAR(50);
DECLARE @RoleName VARCHAR(50);
DECLARE @Salary INT;

-- CURSOR Declaration
DECLARE cursorEmployee CURSOR LOCAL FORWARD_ONLY 
FOR
    SELECT ID, Name, RoleName, Salary
    FROM   Employee
    ORDER BY ID;

-- Opening CURSOR 
OPEN cursorEmployee

-- Fetching First Row From CURSOR 
FETCH NEXT FROM cursorEmployee INTO @EmpID, @EmpName, @RoleName, @Salary
 
-- Looping through Each Row of the ResultSet
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT 'ID : ' + CONVERT(NVARCHAR(50),@EmpID) + ' | Name : ' + @EmpName + ' | RoleName : ' + @RoleName + ' Salary : ' + CONVERT(NVARCHAR(MAX),@Salary)

FETCH NEXT FROM cursorEmployee INTO @EmpID, @EmpName, @RoleName, @Salary

END

-- Closing the CURSOR 
CLOSE cursorEmployee

-- Deallocating the CURSOR 
DEALLOCATE cursorEmployee
GO



You can see the complete cursor query and output here:

sql-cursor




Q143. What are the different types of Cursors in SQL? 

SQL supports mainly four cursors:

  1. Forward-Only Cursor (Firehouse Cursor)
  2. Static Cursor (Insensitive or Snapshot Cursor)
  3. Dynamic Cursor
  4. Keyset Cursor 

Apart from this, you can say there are two more types of cursor i.e Local Cursor and Global Cursor. But they are limited to the scope of the cursor only.



Local Cursor: 

The LOCAL keyword Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. After the batch finishes executing, the cursor is automatically deallocated. 
 
The cursor name is only valid within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, trigger, or stored procedure OUTPUT parameter. 

An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates. 

The local cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates unless the cursor was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.



Global Cursor:

The GLOBAL keyword is used to make the cursor global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is only implicitly deallocated at disconnect.

If neither GLOBAL nor LOCAL is specified, the default is controlled by the setting of the default to the local cursor database option.

cursor-syntax




Q144. What is the Static Cursor or InSensitive Cursor in SQL? 

The static cursor always displays the result set as it was when the cursor was first opened and makes a temporary copy of the data to be used by the cursor. 

The STATIC keyword makes a temporary copy of the data used by the cursor in tempdb in a temporary table. All requests to the cursor are answered from this temporary table in tempdb

Therefore inserts, updates, and deletes made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not detect changes made to the membership, order, or values of the result set after the cursor is opened.  So, no updates, deletes, or inserts made after the cursor was open will be visible in the cursor result set unless we close and reopen the cursor.



Since the result set of a static cursor is stored in a worktable in tempdb, the size of the rows in the result set cannot exceed the maximum row size for a SQL Server table.

A STATIC cursor is always read-only and is also referred to as a snapshot cursor because it only works with the data from the time it was opened, meaning that it won’t display any changes made in the database on the set of data used by the cursor. Transact-SQL uses the term insensitive for static cursors. Some database APIs identify them as snapshot cursors.

However, if the STATIC keyword is missing, the cursor is dynamic and uses a dynamic plan if available. But there are cases when a dynamic plan is worse than a static one.






Q145. What is the Dynamic Cursor in SQL? 

Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. 

A dynamic cursor defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor and fetch a new record, regardless of whether the changes occur from inside the cursor or by other users outside the cursor. 



Therefore all  UPDATE, INSERT and DELETE statements made by all users are visible through the cursor. Updates are visible immediately if they are made through the cursor using either an API function such as SQLSetPos or the Transact-SQL WHERE CURRENT OF clause.

However, Updates made outside the cursor are not visible until they are committed unless the cursor transaction isolation level is set to read uncommitted. For more information on isolation levels.
 
Limitation of Dynamic Cursor:

  1. Dynamic cursor plans never use spatial indexes.
  2. The ABSOLUTE fetch option is not supported with dynamic cursors. 



Q146. What is the Forward Only Cursor or Firehouse Cursor in SQL? What is the difference between Forward-Only and Fast Forward Cursor?

A Forward-Only Cursor is a cursor that can only move forward and be scrolled from the first to the last row. A forward-only cursor is specified as FORWARD_ONLY and READ_ONLY and does not support scrolling. 

These are also called Firehose Cursors and support only fetching the rows serially from the start to the end of the cursor. So the FETCH NEXT is the only supported fetch option in this cursor. However, the rows are not retrieved from the database until they are fetched. 



All insert, update and delete statements made by the current user that affect rows in the result set are visible as the rows are fetched. Because the cursor cannot be scrolled backward, however, changes made to rows in the database after the row was fetched are not visible through the cursor. 

Forward-only cursors are dynamic by default, meaning that all changes are detected as the current row is processed. This provides faster cursor opening and enables the result set to display updates made to the underlying tables.

While forward-only cursors do not support backward scrolling, applications can return to the beginning of the result set by closing and reopening the cursor.



Although the database API cursor models consider a forward-only cursor to be a distinct separate type of cursor, SQL Server does not. SQL Server considers both forward-only and scroll as options that can be applied to static, keyset-driven, and dynamic cursors.

Transact-SQL cursors support forward-only static, keyset-driven, and dynamic cursors. The database API cursor models assume that static, keyset-driven, and dynamic cursors are always scrollable. When a database API cursor attribute or property is set to forward-only, SQL Server implements this as a forward-only dynamic cursor.

If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a dynamic cursor. 

When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL. 

FAST_FORWARD is equivalent to the READ_ONLY and FORWARD_ONLY cursor but has the ability to choose the better plan from either a static or a dynamic one.



Q147. What is the KeySet Cursor in SQL? 

The KEYSET option Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identifies the rows is built into a table in tempdb known as the keyset. 

The keyset is the set of key values from all the rows returned by the query statement. With keyset-driven cursors, a key is built and saved for each row in the cursor and stored either on the client workstation or on the server. The keyset for a keyset-driven cursor is built-in tempdb when the cursor is opened.

So Keyset-driven cursors are controlled by a set of unique identifiers (keys) known as the keyset. The keys are built from a set of columns that uniquely identify the rows in the result set. If the query references at least one table without a unique index, the keyset cursor is converted to a static cursor.

This cursor provides functionality between a static and a dynamic cursor in its ability to detect changes. Like a static cursor, it does not always detect changes to the membership and order of the result set. Like a dynamic cursor, it does detect changes to the values of rows in the result set. 



When you access each row, the stored key is used to fetch the current data values from the data source. In a keyset-driven cursor, the result set membership is frozen when the keyset is fully populated. Thereafter, additions or updates that affect membership are not a part of the result set until it is reopened.

Changes to data values made either by the keyset owner or other processes are visible as the user scrolls through the result set:

1. If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2 because the deleted row appears as a gap in the result set. The key for the row exists in the keyset, but the row no longer exists in the result set.

2. Updates of key values from outside the cursor resemble a delete of the old row followed by an insert of the new row. The row with the new values is not visible and attempts to fetch the row with the old values return an @@FETCH_STATUS of -2. The new values are visible if the update is done through the cursor by specifying the WHERE CURRENT OF clause.

3. Inserts made outside the cursor (by other processes) are visible only if the cursor is closed and reopened. Inserts made from inside the cursor are visible at the end of the result set.



Q148. What are the advantages and applications of Cursors in SQL? 

Advantages of Cursor:

Using Cursor we can perform row-by-row processing so we can perform row-wise validation or operations on each row.

Cursors can provide the first few rows before the whole result set is assembled. Without using cursors, the entire result set must be delivered before any rows are displayed by the application. So by using a cursor, better response time is achieved. 

Cursors are updatable. So by using the UPDATE option in the cursor creation statement, you can update the columns within the cursor.



You can move both forward and backward direction in a cursor. By using the SCROLL option in the DECLARE CURSOR statement you can navigate across the cursor records in both directions with the fetch options FIRST, LAST, PRIOR, NEXT, RELATIVE and ABSOLUTE.

Cursors can be passed to stored procedures. If you use the GLOBAL option to create a cursor, it can be used in any stored procedure or batch executed in the same connection. This allows you to use cursors on nested stored procedures.

The cursor allows READ ONLY behavior. It Prevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.



Application or Use of Cursor:

  1. A cursor allows us to iterate over data one row at a time.
  2. The cursor allows completing a process in a sequential manner such as SQL Server database backups
  3. A cursor can be used for updating data across numerous tables for a specific account
  4. Cursors can be passed to stored procedures directly.



Q149. What are the disadvantages or limitations of Cursors in SQL? 

The biggest disadvantage of cursors is that they are very slow when compared to SQL statements, and therefore you should avoid using them because they will sooner or later lead to performance issues. Usually, cursors have less performance than an equivalent loop using a WHILE loop or CTE.

The second major disadvantage of the cursor is that they are very costly because they require a lot of resources and temporary storage. A cursor in SQL is a temporary work area created in the system memory, thus it occupies memory from your system that may be available for other processes. So occupies more resources and temporary storage. 

Since cursors are pointers that occupy your system memory, that’s why traversing a large result set by using cursors is usually not the best idea. If the entire result set must be transferred to the client for processing and display, for large results, holding the entire result set on the client can lead to demanding memory requirements on the client-side system.



It uses more resources because each time you fetch a row from the cursor, it results in a network roundtrip. This uses much more network bandwidth than the execution of a single SQL statement like SELECT or DELETE etc that makes only one round trip. Repeated network round trips can degrade the speed of the operation using the cursor.

The cursor imposes locks on a portion of the table or the entire table when processing data.

You cannot use cursors or triggers on a table with a clustered columnstore index. However, you can use cursors and triggers on a table with a nonclustered columnstore index.

If you use global cursors in your code, you are taking the risk of facing errors due to a cursor being closed by some stored procedure nested in your code.






Q150. What is the difference between Loop and Cursor?

Advantages of using a WHILE loop compared to a cursor.

  1. While loops are faster than cursors.
  2. While loops use fewer locks than cursors.
  3. While loop does not use Tempdb like a cursor. 

Disadvantages of using a WHILE loop compared to a cursor.

  1. Moving backward and forward is complex in the loop while the cursor supports both at the same time.
  2. Compared to a cursor, you don’t have a fixed set of data to loop, instead when using a WHILE loop you have to define a boundary with an expression that is evaluated to true or false.
  3. A cursor provides a lot of features which is not available in the loop.



Q151. How you can avoid the cursor in SQL? What are the different alternatives of Cursor in SQL?

  1. Use a WHILE Loop to iterate over records in a sequential manner. Using a while loop we can insert the result set into the temporary table.
  2. Use user-defined functions to calculate the resultant row set. Cursors are sometimes used to perform some calculations on the resultant row set. This can also be achieved by creating a user-defined function to suit the needs 
  3. Use CASE expression which can include some branching logic to process data with a SELECT statement.
  4. Use Table Variable, Temp Table, Derived Tables, CTE, and Correlated Sub-queries for intermediate result processing. Use Set-based logic like INSERT or SELECT INTO or INSERT... SELECT to add records to a table as a single transaction.
  5. Use the GO command to repeat a batch. 
  6. Using sp_MSforeachdb SQL Server system stored procedure to loop over each database on an instance.
  7. Using sp_MSforeachtable SQL Server system stored procedure to loop over each table in a database






Alternatives of Cursor:

1. Use SQL Server Integration Services (SSIS) to loop through data primarily for data extraction, transformation, and loading processes between databases.

2. Use SQL Server CROSS APPLY and OUTER APPLY. Microsoft SQL Server 2005 introduced the APPLY operator, which is like a join clause and it allows joining between two table expressions 



The difference between the join and APPLY operator becomes evident when you have a table-valued expression on the right side and you want this table-valued expression to be evaluated for each row from the left table expression.

The APPLY operator allows you to join two table expressions; the right table expression is processed every time for each row from the left table expression. 

So the left table expression is evaluated first and then the right table expression is evaluated against each row of the left table expression for the final result set. 

The final result set contains all the selected columns from the left table expression followed by all the columns of the right table expression.






The CROSS APPLY operator returns only those rows from the left table expression (in its final output) if it matches with the right table expression. In other words, the right table expression returns rows for the left table expression match only.

The OUTER APPLY operator returns all the rows from the left table expression irrespective of its match with the right table expression. For those rows for which there are no corresponding matches in the right table expression, it contains NULL values in columns of the right table expression.

So you might conclude, the CROSS APPLY is equivalent to an INNER JOIN (or to be more precise it's like a CROSS JOIN with a correlated sub-query) with an implicit join condition of 1=1 whereas the OUTER APPLY is equivalent to a LEFT OUTER JOIN.

3. Use JOIN: You can use different joins like INNER JOIN, SELF JOIN, OUTER JOIN, or CROSS JOIN to get the required result instead of the Cursor.









To Be Continued Part-17...

Recommended Articles






Thanks for visiting this page. Please follow and join us on LinkedInFacebookTelegramQuoraYouTubeTwitterPinterestTumbler, and VK for regular updates.

    

No comments:

Post a Comment

Please do not enter any HTML. JavaScript or spam link in the comment box.