SQL Server Cursor

Personal Interview - Answers to frequently asked questions

SQL server cursor - Interview questions 

What is cursor in SQL Server? Define the steps to use Transact-SQL Cursor. Explain the cursor types. Define the cursor lock types. Explain in brief the cursor optimization tips. Explain the disadvantages/limitation of the cursor. Define scrollable cursor. Temporary table VS Table variable: Cursor alternative What is cursor in 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. Define 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 Explain 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. Define 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. Explain in brief 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. 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. Define 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. Temporary table VS Table variable: 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. SQL Server Interview questions part 3 includes following questions with answers What are cursors in SQL Server? | What is a join and explain different types of joins. | What is an extended stored procedure in SQL Server? | What are triggers in SQL Server? | What is a self join in SQL Server? | What is the difference between UNION ALL Statement and UNION? | Write some disadvantage of Cursor? | What is Log Shipping in SQL Server? SQL Server Interview questions part 5 includes following questions with answers Define Local temporary table and global temporary table in SQL Server. | When do we use the UPDATE_STATISTICS command? | Difference between clustered and non-clustered index in SQL Server. Question: Define database objects. SQL Server database stores information in a two dimensional objects of rows and columns called table...... Question: What is index? Define its types. Index can be thought as index of the book that is used for fast retrieval of information. Index uses one or more column index keys and pointers to the record to locate record......... Question: Describe in brief authentication modes in SQL server. This is the default and recommended security mode. In this mode, access to SQL server is controlled by Windows NT..... Question: Define @@Error and raiseerror. It is system variable that returns error code of the SQL statement.....