Dear reader of this message!

I'm looking for info about what is new in cursor on MS Sql Server 2005
compared to MS Sql Server 2000

Maybe some of you have a good link about this topic?

Anyway, have a great day all of you :-)

Personally, I don't know of anything that has changed in DECLARE CURSOR or
how they are processed.

They sure seem to work the same as they did in 2000, and I haven't heard of
any additional features or options...

A



Show quoteHide quote

Jeff wrote:
Nothing has changed to my knowledge.  You should still avoid using
cursors if at all possible.

Exactly.  The one thing that *has* changed is that there are several new
features in SQL Server 2005 that can make it easier to avoid cursors.

Aaron Bertrand [SQL Server MVP] wrote:
Yep.  I suspect the OP was fishing for reasons why it's now OK to use
them...

Do you have a link to info about these new features in Sql Server 2005?

I want to learn more about how to avoid using a cursors in Sql Server 2005
....


Show quoteHide quote

Here's a starting point.
http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx

Read Itzik Ben-Gan's articles in SQL Server Magazine.
He'll show you thousands of ways to avoid cursors, with new SQL 2005
features and with older features.

Show quoteHide quote

I'm making a stored procedure which will run once every day as a schedule
task. This stored procedure will send out emails to the email addresses
mentioned in a table:
create table emails(
    id int not null identity (1,1)
    email nvarchar(100));

There can be many rows in the resultset, and I need a way to access every
row in the resultset for retrieving the email address and send the email....

So I thought about using a cursors which loops through the resultset. And
for every cursor's loop send email using sp_send_dbmail

I don't know how this can be done without using a cursor... but I'm open for
suggestions :-)

Jeff


Show quoteHide quote

Is the content the same for every address?  If so, is there any reason there
has to be n distinct e-mail messages?

You could easily concatenate all the addresses and put them in the BCC
column (provided they don't exceed the capacity of the header) and send one
single e-mail.





Show quoteHide quote

Hi
you can find out differences from this link
http://blogs.msdn.com/sqlprogrammability/archive/2006/06/29/651634.aspx



*** Sent via Developersdex http://www.developersdex.com ***

Jeff wrote:
Show quoteHide quotehttp://www.rac4sql.net/xp_execresultset.asp

Jeff wrote:
Show quoteHide quoteYou can do that from a SSIS package:
http://www.microsoft.com/sql/technologies/integration/default.mspx

Also take a look at:
http://www.microsoft.com/sql/technologies/notification/default.mspx

Certainly you could do the same thing using T-SQL but then you would
also have to create your own logging and error handling, features that
are built in to SSIS.

<DIV>&quot;Jeff&quot; &lt;it_consulta***@hotmail.com.NOSPAM&gt; wrote in
stored procedure which will run once every day as a schedule
Show quoteHide quoteI wouldn't bother looking for a set-based solution, this is an excellent use
for cursors.

Performance isn't an issue and are you really going to do better than:

declare c cursor local fast_forward for
select email from emails

declare @email nvarchar(100)

open c
fetch next from c into @email

while @@fetch_status = 0
begin
  exec msdb.dbo.sp_send_dbmail @recipients=@email,
    @subject = 'Hello',
    @body = 'Hello';
fetch next from c into @email
end

close c
deallocate c

?
David

Sure, if you have 100 such emails, I think that 100 individual calls to
sp_send_dbmail is going to be more expensive than one call with a large BCC
list.  Or at least you're pushing the expense away from the database server
(the SMTP pipeline will be stuck with distributing the message to all of its
recipients).

IMHO.

It may well be more expensive, but would the difference be enough to matter?
The call to sp_send_dbmail is cheap and a background process actually sends
the mail.  And the BCC method is substantially less flexible.  You can't
customize the emails, and the recipient doesn't appear on the TO line.

David

Jeff wrote:
Show quoteHide quoteSimple example - a loop without using a CURSOR.  Still, consider Aaron's
suggestion of concatenating addresses.

DECLARE @EmailAddress VARCHAR(255)

SELECT @EmailAddress = NULL

SELECT TOP 1 @EmailAddress = EmailAddress FROM table

WHILE @EmailAddress IS NOT NULL
    BEGIN

    -- Do stuff here

    DELETE FROM table WHERE EmailAddress = @EmailAddress

    SELECT @EmailAddress = NULL
    SELECT TOP 1 @EmailAddress = EmailAddress FROM table

    END

<DIV>&quot;Tracy McKibben&quot; &lt;tr***@realsqlguy.com&gt; wrote in
Show quoteHide quoteOf course this is both more complicated and more expensive than using a
cursor.

david

David Browne wrote:
Show quoteHide quoteMore "complex", yes, but not complicated.  I've also not seen how it's
more expensive than a cursor, and SQL-Server-Performance.com seems to
agree: http://www.sql-server-performance.com/dp_no_cursors.asp

<DIV>&quot;Tracy McKibben&quot; &lt;tr***@realsqlguy.com&gt; wrote in
Show quoteHide quoteIf you are going to increase complexity you must demonstrate a compelling
improvement in performance.  Replacing each cursor FETCH with a SELECT is
not going to improve performance.

The problem with cursors is that they perform poorly and procedural code is
difficult to maintain.  Replacing them with slower procedural code that's
even harder to maintain is just crazy.

David

David Browne wrote:
Cursors also tend to hold locks longer than necessary and the general
consensus is to avoid them if possible.  The guy asked how this could be
done without a cursor, I gave him a way to do it without a cursor.

<DIV>&quot;Tracy McKibben&quot; &lt;tr***@realsqlguy.com&gt; wrote in
Show quoteHide quoteLocking is up to you.  A STATIC cursor takes a snapshot of the data and
works against that.  And you overstate the "general consensus".  The general
consensus is to avoid cursors if you can replace them with set-based SQL, as
set-based SQL usually performs better and is easier to maintain.

Only after you advised him that "You should still avoid using cursors if at
all possible."

David

But what you provided him was, essentially, a cursor, in different clothing.

I'm not certain there is a way to do what he wants without a cursor,
depending on the desired effect.  As David suggests, the general consensus
is that cursors should be replaced by set-based code where possible, and
when it will have a positive impact on the query (it doesn't always).
Replacing a cursor with a different kind of loop will typically not improve
things, especially if you have transactions/DML inside each iteration.  And
while I'm not an expert, I don't know of any general consensus that
contradicts that.

Unlike Celko, I have written more than 3 cursors in my entire life, and will
continue to do so if the situation warrants it.  Sometimes a cursor is the
best answer, and there is no validity to the blanket statement "a loop is
better than a cursor."  IMHO.

Just because you led him to believe he didn't want to kill himself using a
gun, does not mean he would rather die by blowtorch.  :-)

A

Hi Tracy,

Have you actually observed this approach to more efficient than a
cursor? Can you post a repro script?

Alexander Kuznetsov wrote:
I've used this to replace many cursors that I've come across, and have
seen no negative performance impact.  Here is a good article comparing
this approach to a cursor:
http://www.sql-server-performance.com/dp_no_cursors.asp

Hi Tracy,

I tried to do this in a test case, and it seemed that the cursor was faster
when (as in this case) I was deleting each row as I processed it.  And the
cursor and loop were equally fast when processing each row, but not deleting
theem.  I'm running XP, SQL Server 2000 SP3 (8.00.760).  For example, the
following code showed 1936 ms for the cursor version, but 5360 ms for the
loop version.

Set NoCount ON
use pubs
go
Create Table FooTable (FooID int Identity Primary Key,
  FooData char(100) Null)
-- Load FooTable with 4096 rows
Insert FooTable (FooData) Values (Null) -- 1 row
Insert FooTable (FooData) Select FooData From FooTable -- 2 rows
Insert FooTable (FooData) Select FooData From FooTable -- 4 rows
Insert FooTable (FooData) Select FooData From FooTable -- 8 rows
Insert FooTable (FooData) Select FooData From FooTable -- 16 rows
Insert FooTable (FooData) Select FooData From FooTable -- 32 rows
Insert FooTable (FooData) Select FooData From FooTable -- 64 rows
Insert FooTable (FooData) Select FooData From FooTable -- 128 rows
Insert FooTable (FooData) Select FooData From FooTable -- 256 rows
Insert FooTable (FooData) Select FooData From FooTable -- 512 rows
Insert FooTable (FooData) Select FooData From FooTable -- 1024 rows
Insert FooTable (FooData) Select FooData From FooTable -- 2048 rows
Insert FooTable (FooData) Select FooData From FooTable -- 4096 rows
-- Put values in FooData
Update FooTable Set FooData = Cast(FooID As char(100))
-- Declare variables
Declare @Time1 datetime,
        @Time2 datetime,
        @FooID int,
        @FooData char(100),
        @Counter int
Set @Counter = 0
-- Do Cursor
Set @Time1 = GetDate()
Declare curFooTable Cursor For
  Select @FooID, @FooData
  From FooTable
Open curFooTable
Fetch Next From curFooTable Into @FooID, @FooData
While @@Fetch_Status = 0
Begin
  /* real processing would go here
  in this case, just count
  number of times thru the loop */
  Set @Counter = @Counter + 1
  Delete FooTable Where Current Of curFooTable
  Fetch Next From curFooTable Into @FooID, @FooData
End
Close curFooTable
Deallocate curFooTable
Set @Time2 = GetDate()
Select DateDiff(ms, @Time1, @Time2) As 'Cursor ms',
       @Counter As 'Rows Processed'
go
Drop Table FooTable
go

Set NoCount ON
use pubs
go
Create Table FooTable (FooID int Identity Primary Key,
  FooData char(100) Null)
-- Load FooTable with 8196 rows
Insert FooTable (FooData) Values (Null) -- 1 row
Insert FooTable (FooData) Select FooData From FooTable -- 2 rows
Insert FooTable (FooData) Select FooData From FooTable -- 4 rows
Insert FooTable (FooData) Select FooData From FooTable -- 8 rows
Insert FooTable (FooData) Select FooData From FooTable -- 16 rows
Insert FooTable (FooData) Select FooData From FooTable -- 32 rows
Insert FooTable (FooData) Select FooData From FooTable -- 64 rows
Insert FooTable (FooData) Select FooData From FooTable -- 128 rows
Insert FooTable (FooData) Select FooData From FooTable -- 256 rows
Insert FooTable (FooData) Select FooData From FooTable -- 512 rows
Insert FooTable (FooData) Select FooData From FooTable -- 1024 rows
Insert FooTable (FooData) Select FooData From FooTable -- 2048 rows
Insert FooTable (FooData) Select FooData From FooTable -- 4096 rows
--Insert FooTable (FooData) Select FooData From FooTable -- 8192 rows
--Insert FooTable (FooData) Select FooData From FooTable -- 16384 rows
--Insert FooTable (FooData) Select FooData From FooTable -- 32768 rows
-- Put values in FooData
Update FooTable Set FooData = Cast(FooID As char(100))
-- Declare variables
Declare @Time1 datetime,
        @Time2 datetime,
        @FooID int,
        @FooData char(100),
        @Counter int
Set @Counter = 0
-- Do Loop
Set @Time1 = GetDate()
Set @FooID = Null
Select Top 1 @FooID = FooID, @FooData = FooData
From FooTable
While @FooID Is Not Null
Begin
  /* real processing would go here
  in this case, just count
  number of times thru the loop */
  Set @Counter = @Counter + 1
  Delete From FooTable Where FooID = @FooID
  Set @FooID = Null
  Select Top 1 @FooID = FooID, @FooData = FooData
  From FooTable
End
Set @Time2 = GetDate()
Select DateDiff(ms, @Time1, @Time2) As 'Loop ms',
       @Counter As 'Rows Processed'
go
Drop Table FooTable
go

Tom
Show quoteHide quote

Tom Cooper wrote:
I wasn't advocating the use of one method over another.  The general
rule of thumb is to avoid cursors if possible, and you asked how this
could be done without a cursor.  This while loop method is that way.
You decide which way works best for you, and run with it...

Tracy,

I would say there are at least two different situations

1. You open a cursor and use it to modify the underlying table.
2. You open a cursor, do something, but you do not touch the underlying
at all.

I think that we need to distinguish between these two cases. I think
the popular rule of thumb that "cursors should be avoided whatever it
takes" does not apply in case 2.
Have a look at this simple script (Table Numbers stored numbers from 1
to 10000):

DROP PROCEDURE CursorTest
go
CREATE PROCEDURE CursorTest
AS
DECLARE @KeepLooping INT, @n INT, @d1 DATETIME, @d2 DATETIME
DECLARE Test1 CURSOR LOCAL FORWARD_ONLY
  FOR SELECT Number
      FROM dbo.Numbers
      ORDER BY Number

SET @KeepLooping = 1

SET @d1 = GETDATE()

OPEN Test1

WHILE @KeepLooping = 1 BEGIN
  FETCH Test1 INTO @n
  IF @@FETCH_STATUS <> 0 BEGIN
    SET @KeepLooping = 0
  END
END

SET @d2 = GETDATE()
SELECT 'CursorTest' testname, DATEDIFF(ms, @d1, @d2) duration

CLOSE Test1
DEALLOCATE Test1
go
DROP PROCEDURE LoopTest
go
CREATE PROCEDURE LoopTest
AS
DECLARE @n INT, @d1 DATETIME, @d2 DATETIME

SET @n = -1
SET @d1 = GETDATE()

WHILE @n IS NOT NULL BEGIN
  SELECT @n = MIN(Number)
      FROM dbo.Numbers WHERE Number > @n
END
SET @d2 = GETDATE()
SELECT 'LoopTest' testname, DATEDIFF(ms, @d1, @d2) duration
go
EXEC LoopTest
EXEC CursorTest
EXEC LoopTest
EXEC CursorTest
EXEC LoopTest
EXEC CursorTest

testname duration
-------- -----------
LoopTest 30000

(1 row(s) affected)

testname   duration
---------- -----------
CursorTest 106

(1 row(s) affected)

testname duration
-------- -----------
LoopTest 32190

(1 row(s) affected)

testname   duration
---------- -----------
CursorTest 106

(1 row(s) affected)

testname duration
-------- -----------
LoopTest 29736

(1 row(s) affected)

testname   duration
---------- -----------
CursorTest 106

(1 row(s) affected)

What do you think?

Jeff wrote:
Do we care? What new feature were you expecting.

error converting datatype varchar to numeric
working with gaps in date series
How to get table data ?
updating data from different table
problems sending email from a trigger