This post has already been read 89 times!

In the following example we will looking for the LOCAL FAST_FORWARD parameter in to the declaration of cursor code.

LOCAL
Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is only valid within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a 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 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.

FAST_FORWARD
Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.

-- T-SQL CURSOR WITH LOCAL AND FAST_FORWARD options parameters
-- LOCAL FAST_FORWARD

USE AdventureWorks2016

DECLARE @i INT, @d DATETIME2(7) = SYSUTCDATETIME();

DECLARE c CURSOR
FOR
	SELECT TOP(100000) [BusinessEntityID] FROM 
	[AdventureWorks2016].[Person].[Person] ORDER BY [BusinessEntityID] DESC

OPEN c; FETCH c INTO @i;

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @i += @i;
	FETCH c INTO @i;
END

CLOSE c; 
DEALLOCATE c;

PRINT 'TIME : '+RTRIM(DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME())) + ' milliseconds'

-- T-SQL CURSOR WITH LOCAL AND FAST_FORWARD options parameters
-- LOCAL FAST_FORWARD

USE AdventureWorks2016

DECLARE @i INT, @d DATETIME2(7) = SYSUTCDATETIME();

DECLARE c CURSOR LOCAL FAST_FORWARD
FOR
	SELECT TOP(100000) [BusinessEntityID] FROM 
	[AdventureWorks2016].[Person].[Person] ORDER BY [BusinessEntityID] DESC

OPEN c; FETCH c INTO @i;

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @i += @i;
	FETCH c INTO @i;
END

CLOSE c; 
DEALLOCATE c;

PRINT 'TIME : '+RTRIM(DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME())) + ' milliseconds'

Leave a Reply

Post Navigation