How much is the overhead of a "go" cursor?

Create a new Table

Use [tempdb]

GO

SET ANSI_NULLS on

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo]. [Test] (

[ID] [int] IDENTITY (1, 1) not NULL,

[Value] [INT] Not NULL,

CONSTRAINT [pk_test] PRIMARY KEY CLUSTERED

(

[ID] ASC

) with (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = ON, Allow_page_lo CKS = ON) on [PRIMARY]

) on [PRIMARY]

GO

ALTER TABLE [dbo]. [Test] ADD CONSTRAINT [Df_test_value] DEFAULT ((0)) for [Value]

GO

Insert data:

Declare @i INT

SET @i = 2

While @i < 10000

BEGIN

INSERT into Test (value)

VALUES (@i)

SET @i = @i + 1

END

Sum it up in three ways,

First, cursors:

DECLARE @i INT

DECLARE @Value INT

SET @i = 0

SET @Value = 0

DECLARE C$testcursor CURSOR for

Select Value from Test

OPEN C$testcursor

Select GETDATE ()

FETCH NEXT from C$testcursor to @Value

While @ @FETCH_STATUS = 0

BEGIN

SET @i = @i + @Value

FETCH NEXT from C$testcursor to @Value

END

CLOSE C$testcursor

Deallocate c$testcursor

Select GETDATE ()

Performed three times:

1.   2011-03-24 22:39:32.780, 2011-03-24 22:39:33.217. 437

2.   2011-03-24 22:40:37.750, 2011-03-24 22:40:38.187. 437

3.   2011-03-24 22:40:54.263, 2011-03-24 22:40:54.717. 454

Second, while loop:

DECLARE @MAXID INT

SELECT @MAXID = MAX (ID) from Test

SELECT GETDATE ()

DECLARE @ID INT

DECLARE @Value INT

SET @ID = 1

SET @Value = 0

While @ID <= @MAXID

BEGIN

IF EXISTS (Select ID from test WHERE ID = @ID)

BEGIN

Select @Value = (@Value + Value) from test WHERE ID = @ID

END

SET @ID = @ID + 1

END

SELECT GETDATE ()

Performed three times:

1.   2011-03-24 23:12:01.717, 2011-03-24 23:12:02.043. 326

2.   2011-03-24 23:13:15.390, 2011-03-24 23:13:15.717. 327

3.   2011-03-24 23:13:45.560, 2011-03-24 23:13:45.890. 330

The third, aggregate function:

SELECT GETDATE ()

SELECT SUM (Value) from Test

SELECT GETDATE ()

1. 2011-03-24 23:05:10.170, 2011-03-24 23:05:10.170

2, 2011-03-24 23:06:19.840, 2011-03-24 23:06:19.840

3. 2011-03-24 23:06:31.623, 2011-03-24 23:06:31.623

Try not to use cursors as much as possible with connection or set operations (Joins/set operations), even while the while may be faster than cursors.

How much is the overhead of a "go" cursor?