Lesson 44. Cursors

Cursors are the devil and they should only be used in one scenario which I talk about in the lesson titled The Only Bloody Good Reason To Use Cursors (AKA Moving Large Amounts Of Data Between Tables).

But I do talk about them, so I am going to show you how to create one here.

Cursors are a really inefficient way to loop, so they should only be used in those cases where your process is so inefficient that a cursor is the only thing less efficient than your process. A good example of this is loading large amounts of data between tables.

When you have this scenario, you can split the load into batches that can be committed to the server in smaller amounts. This takes the load off several elements of SQL Server internal mechanisms, most notably the log. Every SQL Server operation is logged so, in the case of a rollback, things can be returned to the state they were in prior to your operation. When you commit smaller batches of data, the log does not get filled up so fast.

When you create a cursor, you need to pick something to partition your dataset by. I usually use dates. Just the year will do, but in the case of extra-large amounts of data, I may have to partition by month as well. However, you have to be careful with this because partitioning by year AND month may actually make the load last longer than just using the year.

Examples

Generic Cursor Example

The below example of creating a cursor is generic. We will do a specific example later.

In [ ]:

USE demo

DECLARE @Year INT
DECLARE @Month INT

DECLARE BatchingCursor CURSOR FOR
SELECT DISTINCT YEAR([SomeDateField]),MONTH([SomeDateField])
FROM [Sometable];


OPEN BatchingCursor;
FETCH NEXT FROM BatchingCursor INTO @Year, @Month;
WHILE @@FETCH_STATUS = 0
BEGIN

BEGIN TRANSACTION
--All logic goes in here
--Any select statements from [Sometable] need to be suffixed with:
--WHERE Year([SomeDateField])=@Year AND Month([SomeDateField])=@Month   
COMMIT TRANSACTION

FETCH NEXT FROM BatchingCursor INTO @Year, @Month;
END;
CLOSE BatchingCursor;
DEALLOCATE BatchingCursor;
GO