Showing posts with label Cursors. Show all posts
Showing posts with label Cursors. Show all posts

GROC (Getting Rid Of Cursors) part four: the T-SQL Pivot operator

This is part four of the GROC-files. I guess it's time to discover some syntactical constructions that make the use of cursors (and also temporary tables) obsolete. One of these constructions was added in SQL server 2005: the PIVOT operator. This operator allows you to write crosstab or matrix queries in a more-or-less relational way.

Let's say we have a relatively normalized data model that has a DailySales table or a view like the following:

  SELECT * 
    FROM DailySales
ORDER BY ProductName, [DayofWeek]



In most cases our business analysts/managers/sales people would like this information to be presented in a less normalized structure, like this:



Each value in the original DayOfWeek column is presented as a column name in the new resultset. The content of that column is the SUM of OrderQuantity after a GROUP BY ProductName is applied. I can imagine that a large number of database developers would attack this problem by creating a table variable -or worse: a temporary table- and then run through the DailySales table with a cursor to do INSERTs and UPDATEs. Well, this huge overkill is not necessary: the PIVOT statement can take care of everything. Here's the T-SQL statement that generates the pivoted result:

SELECT ProductName, 
       Monday, 
       Tuesday, 
       Wednesday, 
       Thursday,
       Friday, 
       Saturday, 
       Sunday
  FROM DailySales
 PIVOT (
          SUM(OrderQuantity) 
          FOR [DayOfWeek] IN 
          (
             [Monday], [Tuesday], [Wednesday], [Thursday],[Friday], [Saturday], [Sunday]
          )
       ) As pvt

The only disadvantage is that the list of columns should be enumerated in your query, but with some dynamic SQL you can get around this.

By the way, there is not much magic involved under the hood. The Pivot statement is just syntactic sugar for CASE constructions. The following query uses more traditional T-SQL, but returns exactly the same result, with the same performance (in SQL Server 2008 even with the exact same query plan steps):

  SELECT ProductName,
         SUM(CASE WHEN [DayOfWeek] = 'Monday' THEN OrderQuantity END) AS Monday,
         SUM(CASE WHEN [DayOfWeek] = 'Tuesday' THEN OrderQuantity END) AS Tuesday,
         SUM(CASE WHEN [DayOfWeek] = 'Wednesday' THEN OrderQuantity END) AS Wednesday,
         SUM(CASE WHEN [DayOfWeek] = 'Thursday' THEN OrderQuantity END) AS Thursday,
         SUM(CASE WHEN [DayOfWeek] = 'Friday' THEN OrderQuantity END) AS Friday,
         SUM(CASE WHEN [DayOfWeek] = 'Saturday' THEN OrderQuantity END) AS Saturday,
         SUM(CASE WHEN [DayOfWeek] = 'Sunday' THEN OrderQuantity END) AS Sunday         
    FROM DailySales
GROUP BY ProductName

If you want to testdrive the queries, then here's the full script:

/*********************************************/
/*                                           */
/* SQL Server 2005/2008 Pivot Statement Demo */
/*                                           */
/*********************************************/
 
-- If you use the SQL2005 AdventureWorksDW 
-- then replace DimDate by DimTime         
--          and DateKey by TimeKey         
 
USE AdventureWorksDW2008
GO
 
/*********************************/
/* What we have in the database  */
/* A 3rd Normal Form(-ish) model */
/*********************************/
 
;WITH DailySales AS
(
    SELECT p.EnglishProductName AS ProductName, 
           SUM(f.OrderQuantity) AS OrderQuantity, 
           d.EnglishDayNameOfWeek AS [DayofWeek] 
      FROM FactInternetSales f 
INNER JOIN DimProduct p 
        ON f.ProductKey = f.ProductKey 
INNER JOIN DimDate d 
        ON f.OrderDateKey = d.DateKey 
  GROUP BY p.EnglishProductName, 
           d.EnglishDayNameOfWeek, 
           d.DayNumberOfWeek
)
  SELECT * 
    FROM DailySales
ORDER BY ProductName, [DayofWeek]
 
/*********************************/
/* What our boss wants to see    */
/* A heavily denormalized report */
/*********************************/
 
;WITH DailySales AS
(
    SELECT p.EnglishProductName AS ProductName, 
           SUM(f.OrderQuantity) AS OrderQuantity, 
           d.EnglishDayNameOfWeek AS [DayofWeek] 
      FROM FactInternetSales f 
INNER JOIN DimProduct p 
        ON f.ProductKey = f.ProductKey 
INNER JOIN DimDate d 
        ON f.OrderDateKey = d.DateKey 
  GROUP BY p.EnglishProductName, 
           d.EnglishDayNameOfWeek, 
           d.DayNumberOfWeek
)
SELECT ProductName, 
       Monday, 
       Tuesday, 
       Wednesday, 
       Thursday,
       Friday, 
       Saturday, 
       Sunday
  FROM DailySales
 PIVOT (
          SUM(OrderQuantity) 
          FOR [DayOfWeek] IN 
          (
             [Monday], [Tuesday], [Wednesday], [Thursday],[Friday], [Saturday], [Sunday]
          )
       ) As pvt
 
/***************************************/       
/* What SQL Server does under the hood */
/* A Select Case T-SQL statement       */
/***************************************/
 
;WITH DailySales AS
(
    SELECT p.EnglishProductName AS ProductName, 
           SUM(f.OrderQuantity) AS OrderQuantity, 
           d.EnglishDayNameOfWeek AS [DayofWeek] 
      FROM FactInternetSales f 
INNER JOIN DimProduct p 
        ON f.ProductKey = f.ProductKey 
INNER JOIN DimDate d 
        ON f.OrderDateKey = d.DateKey 
  GROUP BY p.EnglishProductName, 
           d.EnglishDayNameOfWeek, 
           d.DayNumberOfWeek
)
  SELECT ProductName,
         SUM(CASE WHEN [DayOfWeek] = 'Monday' THEN OrderQuantity END) AS Monday,
         SUM(CASE WHEN [DayOfWeek] = 'Tuesday' THEN OrderQuantity END) AS Tuesday,
         SUM(CASE WHEN [DayOfWeek] = 'Wednesday' THEN OrderQuantity END) AS Wednesday,
         SUM(CASE WHEN [DayOfWeek] = 'Thursday' THEN OrderQuantity END) AS Thursday,
         SUM(CASE WHEN [DayOfWeek] = 'Friday' THEN OrderQuantity END) AS Friday,
         SUM(CASE WHEN [DayOfWeek] = 'Saturday' THEN OrderQuantity END) AS Saturday,
         SUM(CASE WHEN [DayOfWeek] = 'Sunday' THEN OrderQuantity END) AS Sunday         
    FROM DailySales
GROUP BY ProductName


In this article Devin Knight explains how to pivot the same result set, using SQL Server Integration Services.
Labels:

GROC (Getting Rid Of Cursors) part three: Cursor Details

This is the third part of the "GROC-files". I will show you how to enumerate the details about the cursors in the scope of a sql module. You can use this information to figure out if these cursors can or should be optimized.

If you already have a reference (e.g. via a variable or a parameter) to cursors in the current scope, then it suffices to call sp_describe_cursor, and probably sp_describe_cursor_columns and sp_describe_cursor_tables. If you don't have a reference (e.g. within a stored procedure that is called from the body of a cursor) then you can just call sp_cursor_list. Well ... 'just call' may be an understatement. All these system stored procedures use a cursor as OUTPUT variable, so there's some work involved. In a sense I find it cruel that in order to see details about cursors you are forced to create yet another cursor. But anyway, here's how you can find out if there are cursors hanging around in your current scope:

USE AdventureWorks
GO
 
/************************/
/* Using sp_cursor_list */
/************************/
 
-- More info about the returned columns:
-- http://msdn.microsoft.com/en-us/library/ms186256.aspx
 
-- Declare and open a keyset-driven cursor.
DECLARE c1 CURSOR GLOBAL DYNAMIC
FOR
  SELECT LastName
    FROM Person.Contact
   WHERE LastName LIKE 'S%'
ORDER BY LastName
 
OPEN c1
 
-- Declare a cursor variable to hold the cursor output variable
-- from sp_cursor_list.
DECLARE @Report CURSOR
 
-- Execute sp_cursor_list into the cursor variable.
EXEC master.dbo.sp_cursor_list 
  @cursor_return = @Report OUTPUT,
  @cursor_scope = 3 -- Local and Global
 
-- Fetch all the rows from the sp_cursor_list output cursor.
FETCH NEXT from @Report
 
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   FETCH NEXT from @Report
END
 
-- Close and deallocate the cursor from sp_cursor_list.
CLOSE @Report
DEALLOCATE @Report
GO
 
-- Close and deallocate the original cursor.
CLOSE c1
DEALLOCATE c1
GO

The result of the call to sp_cursor_list is not very intuitive:


I can easily imagine that you would prefer an output like this:


You can achieve this by writing a wrapper procedure -or table valued function- around the system stored procedure. Here's an example:

/**********************************/
/*                                */
/* User-friendly Cursor List Demo */
/*                                */
/**********************************/ 
 
USE AdventureWorks
GO
 
/***************/
/* Preparation */
/***************/ 
 
CREATE SCHEMA DockOfTheBay
GO
 
/*******************************************/
/* User-friendly version of sp_cursor_list */
/*******************************************/
 
CREATE PROC DockOfTheBay.usp_cursor_list
AS
 
SET NOCOUNT ON 
 
-- Build a table variable to hold the results from sp_cursor_list
DECLARE @Cursors TABLE
(  
   reference_name char(30), 
   cursor_name char(30), 
   cursor_scope tinyint,
   [status] int,  
   model tinyint, 
   concurrency tinyint, 
   scrollable tinyint, 
   open_status tinyint, 
   cursor_rows decimal(10,0), 
   fetch_status smallint,
   column_count smallint, 
   row_count decimal(10,0),    
   last_operation tinyint, 
   cursor_handle  int
)
 
-- Declare a cursor variable to hold the cursor output variable
-- from sp_cursor_list.
DECLARE @Report CURSOR
 
-- Declare variables to hold the fetched values from @Report
DECLARE
   @reference_name char(30), 
   @cursor_name char(30),
   @cursor_scope tinyint,
   @status int,  
   @model tinyint, 
   @concurrency tinyint, 
   @scrollable tinyint, 
   @open_status tinyint, 
   @cursor_rows decimal(10,0), 
   @fetch_status smallint,
   @column_count smallint, 
   @row_count decimal(10,0),    
   @last_operation tinyint, 
   @cursor_handle  int
 
-- Execute sp_cursor_list into the cursor variable.
EXEC master.dbo.sp_cursor_list 
     @cursor_return = @Report OUTPUT,
     @cursor_scope = 3
 
-- Fetch the first row from the sp_cursor_list output cursor.
FETCH NEXT FROM @Report 
INTO
   @reference_name, 
   @cursor_name,
   @cursor_scope,
   @status,  
   @model, 
   @concurrency , 
   @scrollable , 
   @open_status, 
   @cursor_rows, 
   @fetch_status,
   @column_count, 
   @row_count,    
   @last_operation, 
   @cursor_handle
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
   -- Insert the values into the temp table
   INSERT INTO @Cursors
   VALUES ( 
           @reference_name, 
           @cursor_name,
           @cursor_scope,
           @status,  
           @model, 
           @concurrency , 
           @scrollable , 
           @open_status, 
           @cursor_rows, 
           @fetch_status,
           @column_count, 
           @row_count,    
           @last_operation, 
           @cursor_handle
          )
 
   -- Fetch subsequent rows from @Report
   FETCH NEXT FROM @Report
   INTO
      @reference_name, 
      @cursor_name,
      @cursor_scope,
      @status,  
      @model, 
      @concurrency , 
      @scrollable , 
      @open_status, 
      @cursor_rows, 
      @fetch_status,
      @column_count, 
      @row_count,    
      @last_operation, 
      @cursor_handle
END
 
-- Close and deallocate the cursor from sp_cursor_list.
CLOSE @Report
DEALLOCATE @Report
 
-- Return the information from the table variable, translated into readable descriptions
IF (SELECT count(*) FROM @Cursors) > 0
  SELECT 
   [Cursor Reference] = reference_name, 
   [Declared Name] = cursor_name, 
   [Scope] = CASE cursor_scope
     WHEN 1 THEN 'local'
     ELSE 'global'
   END,
   [Status] = CASE [status]
     WHEN 1  THEN 'open'
     WHEN 0  THEN 'no rows'
     WHEN -1 THEN 'closed'
     WHEN -2 THEN 'no reference'
     WHEN -3 THEN 'does not exist'
   END,
   [Model] =  CASE model
     WHEN 1 THEN 'static'
     WHEN 2 THEN 'keyset'
     WHEN 3 THEN 'dynamic'
     WHEN 4 THEN 'fast forward'
   END,
   [Locking] = CASE concurrency 
     WHEN 1 THEN 'read only'
     WHEN 2 THEN 'scroll locks'
     WHEN 3 THEN 'optimistic'
   END,
   [Scrolling] = CASE scrollable 
     WHEN 0 THEN 'forward only'
     WHEN 1 THEN 'scrollable'
   END,
   [Open] = CASE open_status 
     WHEN 0 THEN 'no'
     WHEN 1 THEN 'yes'
   END,
   [Qualifying Rows] =  cursor_rows,
   [Fetch Status] = CASE fetch_status 
     WHEN 0  THEN 'successful'
     WHEN -1 THEN 'out of bounds'
     WHEN -2 THEN 'missing row'
     WHEN -9 THEN 'no fetch done'
   END,
   [Columns] = column_count, 
   [Rows for last operation] = row_count,  
   [Last operation] = CASE last_operation 
     WHEN 0 THEN 'none'
     WHEN 1 THEN 'OPEN'
     WHEN 2 THEN 'FETCH'
     WHEN 3 THEN 'INSERT'
     WHEN 4 THEN 'UPDATE'
     WHEN 5 THEN 'DELETE'
     WHEN 6 THEN 'CLOSE'
     WHEN 7 THEN 'DEALLOCATE'
   END,
   [Handle] = cursor_handle
  FROM @Cursors
GO
 
/*********/
/* Usage */
/*********/
 
-- Declare and open a keyset-driven cursor.
DECLARE c1 CURSOR DYNAMIC GLOBAL
FOR
  SELECT LastName
    FROM Person.Contact
   WHERE LastName LIKE 'S%'
ORDER BY LastName
 
OPEN c1
 
-- Call the user-friendly version.
exec DockOfTheBay.usp_cursor_list
 
-- Close and deallocate the original cursor.
CLOSE c1
DEALLOCATE c1
GO
 
/***********/
/* Cleanup */
/***********/
 
DROP PROC DockOfTheBay.usp_cursor_list
DROP SCHEMA DockOfTheBay
GO
Labels:

GROC (Getting Rid Of Cursors) part two: Cursor Resources

This is the second part of the "GROC-files". I will focus on the resource consumption of cursors, especially tempdb space and locks. The variable that holds the cursor, and the FETCH INTO-variables also take some space, but I'm going to ignore that here. The following T-SQL script contains switches for most of the relevant factors:
  • cursor type,
  • cursor locking behavior,
  • locking hints in the query,
  • transaction isolation level, and
  • cursor close on commit setting.
If you run the script, it will return a resultset containing the resource consumption per step, like this:



Here are a couple of observations from several runs with different settings:
  • Static and keyset cursors can consume a considerable amount of tempdb memory.
  • Dynamic cursors don't use tempdb.
  • In higher transaction isolation levels, keyset cursors can take a lot of locks, and for a long period.
  • In higher transaction isolation levels, static cursors can take a lot of locks, and for a long period. [They copy everything to the tempdb and lock it in the user db !].
  • FOR UPDATE apparently has no impact on resources.
  • Cursor owned locks only appear with the SCROLL_LOCKS setting. All other locks belong to the transaction.
  • If the connection option to keep cursors open on a commit is set on, all locks will be kept across a commit or rollback operation.
  • A WITH (NOLOCK) hint avoids transaction as well as cursor owned locks. [So it actually overrides the SCROLL_LOCKS setting, which might no be what you wanted !]
Anyway, some of these observations confirm (again) that cursors should be avoided in most scenarios: they use more resources than necessary and are not easily controllable.

Some final advice:
  • Select as few as possible rows and columns in the cursor resultset (this advice actually applies to all possible resultsets in batches/transactions),
  • whenever possible, prefer dynamic cursors over static ones, and
  • explicitly set CURSOR_CLOSE_ON_COMMIT to ON in your session.
Here's the full test script:
 
/*************************/
/*                       */
/* Cursor Resources Demo */
/*                       */
/*************************/
 
USE AdventureWorks
GO
 
/***************/
/* Preparation */
/***************/
 
CREATE SCHEMA DockOfTheBay
GO
 
CREATE FUNCTION DockOfTheBay.ufn_TempDb_Internal_Objects
(
    @Session_id AS int
)
RETURNS int
AS
BEGIN
    --
    -- Returns the number of KBytes allocated to tempdb internal objects for a session
    --    
    DECLARE @Result int
 
    -- rem: Caching and Deferred Drop operations may influence counters
    SELECT @Result = SUM((internal_objects_alloc_page_count - internal_objects_dealloc_page_count)) * 8
      FROM  tempdb.sys.dm_db_task_space_usage 
     WHERE  session_id = @Session_id
       AND database_id = db_id('tempdb')
 
    RETURN @Result
END
GO
 
CREATE FUNCTION DockOfTheBay.ufn_Locks
(
    @Session_id AS int
)
RETURNS NVARCHAR(200)
AS
BEGIN
    --
    -- Returns the number of locks per type and owner for a session, as a comma-separated list
    --
    DECLARE @Result NVARCHAR(200)    
 
    ;WITH Locks AS
    (
        SELECT resource_type AS [Type]
              ,CONVERT(NVARCHAR(03), COUNT(*)) AS [Number]
              ,request_owner_type AS [Owner]
          FROM sys.dm_tran_locks
         WHERE request_session_id = @Session_id
           AND resource_type IN ('ROW', 'PAGE', 'RID', 'KEY')
         GROUP BY resource_type, request_owner_type
    )
    SELECT @Result = COALESCE(@Result + ', ', '') + [Owner] + ' ' + [Type] + ' lock(s): ' + [Number]
    FROM Locks
 
    RETURN @Result
 
END
GO
 
/*************/
/* Execution */
/*************/
 
/* Switch option ON and OFF: */
SET CURSOR_CLOSE_ON_COMMIT /* ON */ OFF
 
/* Uncomment ONE of the following options at a time: */
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
BEGIN TRANSACTION 
 
-- Table Variable to hold the measurement results
DECLARE @Measurements TABLE 
(
    [Step] NVARCHAR(50)
   ,[TempDb KBytes] INT
   ,[Locks] NVARCHAR(200)
)
 
INSERT @Measurements
SELECT 'Transaction started' 
      ,DockOfTheBay.ufn_TempDb_Internal_Objects(@@spid) 
      ,DockOfTheBay.ufn_Locks(@@spid) 
 
-- Dummy variable for the FETCH operation
DECLARE @temp SQL_VARIANT
 
-- Declare and open a keyset-driven cursor.
DECLARE c1 CURSOR 
    /* Please uncomment ONE of the following options at a time: */
    -- DYNAMIC 
    DYNAMIC SCROLL_LOCKS 
    -- KEYSET
    -- STATIC  
FOR
    SELECT *
      FROM Production.ProductModel 
      /* Uncomment to see the impact: */
      -- WITH (NOLOCK)
     WHERE ProductModelID > 1 
     /* Uncomment to see the impact: */
     -- FOR UPDATE
 
OPEN c1
 
INSERT @Measurements
SELECT 'After Open' As [Step]
      ,DockOfTheBay.ufn_TempDb_Internal_Objects(@@spid) AS [TempDb KBytes]
      ,DockOfTheBay.ufn_Locks(@@spid) AS [Locks]
 
FETCH NEXT FROM c1 INTO @temp, @temp, @temp, @temp, @temp, @temp
 
INSERT @Measurements
SELECT 'After Fetch' As [Step]
      ,DockOfTheBay.ufn_TempDb_Internal_Objects(@@spid) AS [TempDb KBytes]
      ,DockOfTheBay.ufn_Locks(@@spid) AS [Locks]
 
COMMIT TRANSACTION
 
INSERT @Measurements
SELECT 'After Commit' As [Step]
      ,DockOfTheBay.ufn_TempDb_Internal_Objects(@@spid) AS [TempDb KBytes]
      ,DockOfTheBay.ufn_Locks(@@spid) AS [Locks]
 
-- Close and deallocate the cursor.
CLOSE c1
DEALLOCATE c1
 
INSERT @Measurements
SELECT 'After Deallocate' As [Step]
      ,DockOfTheBay.ufn_TempDb_Internal_Objects(@@spid) AS [TempDb KBytes]
      ,DockOfTheBay.ufn_Locks(@@spid) AS [Locks]
 
-- Display results
SELECT * FROM @Measurements
 
GO
/***********/
/* Cleanup */
/***********/
 
DROP FUNCTION DockOfTheBay.ufn_TempDb_Internal_Objects
DROP FUNCTION DockOfTheBay.ufn_Locks
DROP SCHEMA DockOfTheBay
GO
Labels:

GROC (Getting Rid Of Cursors) part one: Cursor Lifecycle

This is the first of the GROC-files: a list of articles about TSQL server-side cursors and how to get rid of them.

In the late '80s cursors were added to the ANSI SQL standard, basically to allow the Cobol/PL1/IMSDB developers of that time (like me) to program against a relational database using the same constructs as in their structured programs and hierarchical databases: DO WHILE and FOR NEXT loops. So in a sense you can consider them as a backward compatibility feature: they are there for your development comfort, not for performance reasons. Indeed in most -but not all!- cases cursor based logic will be outperformed by well written Relational Algebra.

In SQL Server cursors are implemented as a native data type, so they can be referred to by variables of this type, or returned as an output parameter of a stored procedure.

In this article I'll focus on the life cycle of cursors. Cursors live (and hence consume resources) from the moment that they are declared, until they are deallocated - explicitly by you, or automatically by SQL Server. To find out whether or not there are cursors hanging around, you can monitor SQL Server's Performance Counters from the category SQLServer:Cursor Manager. You can access these counters from TSQL:

/***************************************/
/* Cursor-related Performance Counters */
/***************************************/
 
SELECT DISTINCT * 
  FROM sys.dm_os_performance_counters
 WHERE [object_name] LIKE 'SQLServer:Cursor%'
GO


I created a small function on top of some the performance counters, and called it a couple of times during the lifecycle of a pretty trivial cursor. Run the script, modify it (see included comments) and run it again. The script proves that cursors can remain alive (and consume resources) a lot longer than you might expect, because:
  • a global cursor survives batches, and
  • a cursor is not really deallocated if there are variables referencing it.

So here is some advice to keep the lifetime of a cursor as short as possible:
  • Open any cursor as late as possible,
  • close and deallocate any cursor as soon as possible,
  • explicitly use LOCAL for a locally scoped cursor - otherwise you depend on the CURSOR_DEFAULT database setting, and
  • be careful when referring to cursors via variables.


Here we go with the full test script:
/*************************/
/*                       */
/* Cursor Lifecycle Demo */
/*                       */
/*************************/
 
/***************/
/* Preparation */
/***************/
 
ALTER DATABASE AdventureWorks SET CURSOR_DEFAULT LOCAL -- or GLOBAL
GO
 
USE AdventureWorks
GO
CREATE SCHEMA DockOfTheBay
GO
CREATE FUNCTION DockOfTheBay.ufn_CursorsInScope
(    
    @Step AS NVARCHAR(25)
)
RETURNS TABLE 
AS
RETURN 
(
SELECT @Step AS [Step], instance_name AS [Cursor Type], cntr_value AS [Number]
  FROM sys.dm_os_performance_counters
 WHERE counter_name = 'Active cursors'
   AND instance_name LIKE 'TSQL%Cursor%'
)
GO
 
/*************/
/* Execution */
/*************/
 
-- Declare variables for each field
DECLARE @Name nvarchar(50)
 
-- Show that cursor is not yet there
SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Start')
 
-- Declare the cursor: 
--   name, scrolling behavior, scope, and 
--   the query that produces the resultset    
DECLARE c1 CURSOR READ_ONLY LOCAL /* change to 'GLOBAL', or remove, and compare results */
FOR
SELECT [Name]
  FROM Production.Product
 
-- Show that cursor is already there
SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Declared')
 
-- Open cursor: 
--    populates the resultset
OPEN c1
 
-- Show that cursor is already there
SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Opened')
 
-- Fetch Cursor:
--    reads the next row of the resultset into variables
FETCH NEXT FROM c1 -- Some types also allow 'FIRST', 'LAST' and/or 'PREVIOUS'
INTO @Name
 
-- Loop through resultset
WHILE @@FETCH_STATUS = 0
BEGIN
 
    -- Body of the cursor
    PRINT @Name -- Away to the Messages Pane
 
    -- Fetch Cursor:
    --    reads the next row of the resultset into variables
    FETCH NEXT FROM c1
    INTO @Name
 
END
 
-- Show that cursor is still there
SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Fetched')
-- GO 
-- Uncomment the 'GO' statement on the previous line, and compare results:
--    at the end of a batch a local cursor is cleaned up,
--    but a global cursor continues to live.
 
-- Close cursor: 
--    releases the result set and the locks
CLOSE c1
 
BEGIN TRY
    OPEN c1 -- Only successful if still in scope: local and in the batch
    SELECT 'LOOK: ' AS [ ], 'The cursor was reopened' As [Message]
    CLOSE c1
END TRY
BEGIN CATCH
    SELECT 'OOPS: ' AS [ ], ERROR_MESSAGE() As [Message]
END CATCH
 
-- Show cursor is still there
SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Closed')
 
-- Create a reference in a variable
DECLARE @c2 CURSOR
SET @c2 = c1
 
-- Deallocate cursor: 
--    removes the reference and -if it was the last one- all remaining resources
DEALLOCATE c1
 
-- Show cursor is still there, even after deallocation
SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Deallocated')
 
-- Try to reopen
BEGIN TRY
    OPEN @c2 
    SELECT 'LOOK: ' AS [ ], 'I reopened a deallocated cursor' As [Message]
    CLOSE @c2
END TRY
BEGIN CATCH
    SELECT 'OOPS: ' AS [ ], ERROR_MESSAGE() As [Message]
END CATCH
 
-- Deallocate cursor: 
--    removes the reference and -if it was the last one- all remaining resources
DEALLOCATE @c2
 
-- Show cursor is really gone now
SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Deallocated - last ref')
GO
 
/***********/
/* Cleanup */
/***********/
 
DROP FUNCTION DockOfTheBay.ufn_CursorsInScope
DROP SCHEMA DockOfTheBay
GO
Labels:
Subscribe to: Posts (Atom)