Cursors for T-SQL Beginners

Question

Cursors for T-SQL Beginners

Viewing 13 posts - 76 through 87 (of 87 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 1

David Kuhl (5/27/2011)
battelofhalfwits (1/1/2009)
Wow...
Two things
1) Never use Cursors! Looping through a temp table takes less overhead than a Cursor; especially when you are working on a 24/7/365 server where you really need to watch your resources.
I want to echo this. I have encountered situations where there was no way around doing row-by-row inserts (database conversion / merger where a stored proc needed to be run to generate values for each line) but when it was first written with cursors our conversion process was going to take about 3 days to run. After replacing the cursors with temp tables and while loops the process was cut down to a few hours. I've never seen a situation where I could not replace a cursor and benefit.
D.

If you had simply changed your cursors to forward only, read only, static cursors, you would have achieved the same result. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 2

Sorry... duplicate post removed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 3

pete.griffiths (5/27/2011)
For people not trained in using set based theory it is understandable to use a rbar approach because this is all that they know. But if you do know it then please use it!
Rant over 😉

Speaking of rants, I'd probably reword that to say something to the effect that if you don't know set-based theory, please stay away from my data and servers. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 4

I haven't read all posts but...

DML is not Data Modification Language. It means Data Manipulation Language and the SELECT statement is also part of it.

Anyway great article. 🙂

I've never seen someone using a cursor to concatenate strings before though.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Mad Myche SSCommitted Points: 1593 More actions · Answer 5

The SELECT statement may be part of DML, it all depends on the context and who makes the decision. A straight Read-Only SELECT does no manipulation so it would be outside of DML. It is when the SELECT is conjoined with INSERT, INTO or UPDATE that SELECT is considered to be in DML.

Director of Transmogrification Services

mtassin SSC-Insane Points: 23099 More actions · Answer 6

Mhlewis (5/27/2011)
We've had to use cursors in generating notification emails using sp_send_dbmail. We have a list of 5 account execs that get notified when their data is loaded and ready for them to review. I'd love a set based version of sp_send_dbmail.

This is a bit rough... without tables, I just wrote the basic gist here.

But, this will generate a huge dynamic sql statement that will send all the emails at once.

DECLARE @SQL nvarchar(max)

SELECT

@SQL = COALESCE(@SQL + N'; EXEC sp_send_dbmail @Recipients = ''' + @recipientlist + ''',@body = ''Email body'',@subject = ''Subject''',N'; EXEC sp_send_dbmail @Recipients = ''' + @recipientlist + ''',@body = ''Email body'',@subject = ''Subject''')

FROM

joblisttable

EXEC sp_executesql @SQL

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 7

Mad Myche (5/31/2011)
The SELECT statement may be part of DML, it all depends on the context and who makes the decision. A straight Read-Only SELECT does no manipulation so it would be outside of DML. It is when the SELECT is conjoined with INSERT, INTO or UPDATE that SELECT is considered to be in DML.

I was just pointing that DML isn't related to modification only.

The way I see it manipulation is not same as modification.

I've heard people believing SELECT isn't part of DML because of the "modification" word.

Selecting data is a form of manipulating data unless I'm mistaken on the concept.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

DCPeterson SSCoach Points: 15266 More actions · Answer 8

codebyo (5/31/2011)
Mad Myche (5/31/2011)
The SELECT statement may be part of DML, it all depends on the context and who makes the decision. A straight Read-Only SELECT does no manipulation so it would be outside of DML. It is when the SELECT is conjoined with INSERT, INTO or UPDATE that SELECT is considered to be in DML.
I was just pointing that DML isn't related to modification only.
The way I see it manipulation is not same as modification.
I've heard people believing SELECT isn't part of DML because of the "modification" word.
Selecting data is a form of manipulating data unless I'm mistaken on the concept.
Best regards,

You are correct. SELECT is part of DML and it even starts its own transaction (assuming implicit transactions) just like INSERT, DELETE, UPDATE... I think the confusion arises from the common misinterpretation of the "M" as modification instead of manipulation as you pointed out, it also might stem in part from the fact that SELECT statements don't fire DML triggers.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

j_maloney Right there with Babe Points: 731 More actions · Answer 9

Jeff Moden (1/1/2009)
gryphonsclaw (1/1/2009)
You don't have to use a cursor to concatenate a string.
DECLARE @myVar varchar(MAX)
SET @myVar = ''
SELECT @myVar = @myVar + mycolumn + ' '
FROM myTable
SELECT @myVar
The author also pointed that out in the article.

Apologies for the newbishness, but this bit of SQL kinda blows my mind because I've been a developer for years, but had never come across code like this. How does this work? Seems like there would have to be an internal cursor at work here. Can someone please point out a link explaining how this style block of code works? It would be greatly appreciated.

I've started reading Itzik Ben Gan's T-SQL Querying book (currently on Ch 4) and hope to learn new appraches and techniques. Ch 1 was indeed eye-opening on the order in which clauses are processed. Sad to say that the db's I'm currently working with at my new job are rife with cursors and udfs. You couldn't possibly design less performant dbs if you tried. Oh, and not one single comment ... anywhere. What did I get myself into?

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 10

DCPeterson (5/31/2011)
You are correct. SELECT is part of DML and it even starts its own transaction (assuming implicit transactions) just like INSERT, DELETE, UPDATE... I think the confusion arises from the common misinterpretation of the "M" as modification instead of manipulation as you pointed out, it also might stem in part from the fact that SELECT statements don't fire DML triggers.

Thank you for verifying the statement. I became unsure for an instant.

I have so much to learn and so little (precious) time. :w00t:

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 11

jmaloney-1117255 (5/31/2011)
Jeff Moden (1/1/2009)
gryphonsclaw (1/1/2009)
You don't have to use a cursor to concatenate a string.
DECLARE @myVar varchar(MAX)
SET @myVar = ''
SELECT @myVar = @myVar + mycolumn + ' '
FROM myTable
SELECT @myVar
The author also pointed that out in the article.
Apologies for the newbishness, but this bit of SQL kinda blows my mind because I've been a developer for years, but had never come across code like this. How does this work? Seems like there would have to be an internal cursor at work here. Can someone please point out a link explaining how this style block of code works? It would be greatly appreciated.
I've started reading Itzik Ben Gan's T-SQL Querying book (currently on Ch 4) and hope to learn new appraches and techniques. Ch 1 was indeed eye-opening on the order in which clauses are processed. Sad to say that the db's I'm currently working with at my new job are rife with cursors and udfs. You couldn't possibly design less performant dbs if you tried. Oh, and not one single comment ... anywhere. What did I get myself into?

Apologies for the late response. :blush:

Think of it as a "set based loop"... something that some of us call a "Pseudo Cursor". For every row in the table, it will add "mycolumn" from the table. So, if the table had the first three letters of the English Alphabet in it, here's what would happen.

1. The DECLARE and SET create the variable and turns it from containing a NULL to having an empty string in it.

2. The SELECT reads the "first" row from the table and the content of row/column is added to the variable along with a "space" delimiter (the delimiter isn't important here) and reassigned to the variable kind of like say @counter = @counter + 1.

The variable now contains "A ".

3. The SELECT then reads the "second" row from the table and the same thing happens as in #2 above. The content of the second row is added to the variable and the result is stored back in the variable.

The variable now contatins "A B ".

4. The SELECT then reads the "third" row from the table and the same thing happens as in #2 and #3 above. The content of the second row is added to the variable and the result is stored back in the variable.

The variable now contatins "A B C ".

Unfortunately, the technique is a less-than-optimal method and can get quite slow as the variable gets bigger for reasons similar to why the old fashioned "bubble-sort" get's slower... It has to handle more and more data for each row added in. There's a trick with XML Path that you can do in a setbased fashion to concatenate related row information. Please see the following articles for more information...

http://www.sqlservercentral.com/articles/Test+Data/61572/

http://www.sqlservercentral.com/articles/comma+separated+list/71700/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

j_maloney Right there with Babe Points: 731 More actions · Answer 12

Jeff, thank you for taking the time to answer. 🙂

I suspected that there would likely be some overhead involved. And thank you for the links, I'll check them out straight away. I also read your Tally Table article - fantastic stuff. I think I found my new religion as an anti-RBAR convert. This forum is outstanding!

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 13

jmaloney-1117255 (6/7/2011)
Jeff, thank you for taking the time to answer. 🙂
I suspected that there would likely be some overhead involved. And thank you for the links, I'll check them out straight away. I also read your Tally Table article - fantastic stuff. I think I found my new religion as an anti-RBAR convert. This forum is outstanding!

Apologies for the late response, again! :blush: I managed to burn the candle at both ends and at the middle. 😛

Thank you very much for the kind words about the Tally Table article. I'm very happy to add you to the "Anti-RBAR Movement". 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)