25 September 2014

SQL Server - Cursor

Cursor is a variable in SQL Server Database which is used for row-by row operations. The cursor is so named because it indicates the current position in the resultset.
  • Let us look this example.
Here I am just taking 2 columns  from a table and passing through the cursor and printing them.

  • CREATE PROCEDURE Usp_cursor_test
    AS
      BEGIN
          –Declaring the  variables needed for cursor to store data
          DECLARE @Name VARCHAR(50)
          DECLARE @EmptypeID INT
          –Declaring the Cursor cur_print For name and Emptypeid in the Employeedetails table 
          DECLARE cur_print CURSOR FOR
            SELECT name,
                   emptypeid
            FROM   employee.employeedetails
          –After declaring we have to open the cursor 
          OPEN cur_print
          –retreives the First row from cursor and storing it into the variables. 
          FETCH NEXT FROM cur_print INTO @Name, @EmptypeID
          – @@FETCH_STATUS returns the status of the last cursor FETCH statement issued against  
          – any cursor currently opened by the connection. 
          – @@FETCH_STATUS = 0 means The FETCH statement was successful. 
          – @FETCH_STATUS = -1 The FETCH statement failed or the row was beyond the result set. 
          – @@FETCH_STATUS = -2 The row fetched is missing. 
          WHILE @@FETCH_STATUS = 0
            BEGIN
                –Operations need to be done,Here just printing the variables 
                PRINT @Name
                PRINT @EmptypeID
                –retreives the NExt row from cursor and storing it into the variables. 
                FETCH NEXT FROM cur_print INTO @Name, @EmptypeID
            END
          –Closing the cursor 
          CLOSE cur_print
          – removes the cursor reference and relase cursor from memory 
          – very Important 
          DEALLOCATE cur_print
      END 
      
    Note: 
  • Once cursor is opened we have to close the cursor
  • After the usage cursor should be deallocated from the memory.
  • As a DBA , I will not recommend the usage of cursors in all scenarios because it affects performance, since for each result it will have a  network round trip which will cause a major performance issue in large data sets. You can make use of case statement instead of cursors for some scenarios.

Difference betweeb SQL Server 2005 and 2008


S. NoSQL Server 2005SQL Server 2008
1XML datatype is introduced.XML datatype is used.
2Can not encrypt the entire database.Can encrypt the entire database introduced in 2008.
3Datetime is used for both date and time.Date and time are seperately used for date and time
4No table datatype is included.Table datatype introduced.
5SSIS is started using.SSIS avails in this version.
6CMS is not available.
Central Management Server(CMS) is Introduced.
7PBM is not available
Policy based management(PBM) server is Introduced.