473,883 Members | 1,787 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

new to cursors

Can anyone point me to a good resource for learning cursors in MSSQL?

Thanks

Dave
Jul 20 '05 #1
19 5772
Dave,
Try www.TechnicalVideos.net. The videos on triggers and Stored
Procedures will help you a lot. There is also a video specifically dealing
with cursors.

Hope this helps,
Chuck Conover
www.TechnicalVideos.net
"Dave Anderson" <an******@cvn.n et> wrote in message
news:EL******** ******@newsread 1.news.pas.eart hlink.net...
Can anyone point me to a good resource for learning cursors in MSSQL?

Thanks

Dave

Jul 20 '05 #2
Remember that you should generally try to avoid using cursors at all. They
are not usually a good solution to a problem in SQL because of their
performance and resource constraints compared to the set-based alternatives.

My view is that the majority of cursors fall into one of three categories:
Procedural administrative tasks (a reasonable use of a cursor); Written by
procedural programmers who don't know SQL; Used to work around a poor data
design (e.g. lack of keys in tables). There are other cases but they are few
and far between in my experience.

--
David Portas
SQL Server MVP
--
Jul 20 '05 #3
David,
I'm not disagreeing exactly, but I would be interested in your opinion.
Typically I use cursors to load data from an outside source. Since you
can't control what that outside data looks like, I will do the following:

- load data into a temp table
- cursor thru the temp table, verifying data types and generally massaging
the data, if needed
- insert into our production table(s) if the data passes examination (done
in the cursor)

I'm not certain if you'd consider this an administrative task. Some of the
data checking we do would be hard or impossible to do using a set-based
structure, I think.

Appreciate your views.
Best regards,
Chuck

"David Portas" <RE************ *************** *@acm.org> wrote in message
news:Jb******** ************@gi ganews.com...
Remember that you should generally try to avoid using cursors at all. They
are not usually a good solution to a problem in SQL because of their
performance and resource constraints compared to the set-based alternatives.
My view is that the majority of cursors fall into one of three categories:
Procedural administrative tasks (a reasonable use of a cursor); Written by
procedural programmers who don't know SQL; Used to work around a poor data
design (e.g. lack of keys in tables). There are other cases but they are few and far between in my experience.

--
David Portas
SQL Server MVP
--

Jul 20 '05 #4
"Chuck Conover" <cc******@comms peed.net> wrote in
news:10******** *******@news.co mmspeed.net:
David,
I'm not disagreeing exactly, but I would be interested in your
opinion. Typically I use cursors to load data from an outside
source. Since you can't control what that outside data looks like,
I will do the following:

- load data into a temp table> - cursor thru the temp table, verifying data types and generally massaging the data, if needed
- insert into our production table(s) if the data passes
examination (done in the cursor)


Here's what I do:

1) bcp the data to load into a staging table
2) using set-based logic to validate against master tables putting the
'clean' results into a 'good' table and 'bad' data into another
(for later reporting)
3) once all the validation is done, a single insert from the 'good'
table is used to slam all the data into the target table.
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com
Jul 20 '05 #5
As Pablo says, many data cleansing and transformation operations can be
performed using set-based statements, even if you don't have keys in your
source data. For the rest I would use a dedicated ETL tool. DTS is the
component that ships with SQLServer but there are also other packages
specifically designed to automate the type of data prep you are describing
and then load the cleansed data into your database. This approach has lots
of advantages over hand-coded conversions in an RDBMS that isn't really
designed and optimised for the job.

http://pervasive.datajunction.com/djcosmos/
http://www.embarcadero.com/products/dtstudio/index.html
http://www.informatica.com

--
David Portas
SQL Server MVP
--
Jul 20 '05 #6
David Portas (RE************ *************** *@acm.org) writes:
My view is that the majority of cursors fall into one of three
categories: Procedural administrative tasks (a reasonable use of a
cursor); Written by procedural programmers who don't know SQL; Used to
work around a poor data design (e.g. lack of keys in tables). There are
other cases but they are few and far between in my experience.


I'll add one more case: you already have a stored procedures that
performs some complex logic, and this procedure accepts its input
in scalar parameters, and you want to apply that logic to entire
set of data.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #7
Erland Sommarskog <so****@algonet .se> wrote in
news:Xn******** *************@1 27.0.0.1:
David Portas (RE************ *************** *@acm.org) writes:

I'll add one more case: you already have a stored procedures that
performs some complex logic, and this procedure accepts its input
in scalar parameters, and you want to apply that logic to entire
set of data.


I'll counter the above with the fact that you're using the wrong
tool for the job. The SP was written to handle single row inserts
therefore if you plan on doing batch inserts, then the SQL should be
written and tuned accordingly. Unless you wish this side of sucky
performance. :)
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com
Jul 20 '05 #8
>> My view is that the majority of cursors fall into one of three
categories:
Procedural administrative tasks (a reasonable use of a cursor);
Written by procedural programmers who don't know SQL; Used to work
around a poor data design (e.g. lack of keys in tables). There are
other cases but they are few and far between in my experience. <<

The only other one that comes to mind is an NP complete problem
(traveling salesman, etc.) where you can use the first near-optimal
answer. Being a set-oriented language, SQL tends to find the **entire
set** of solutions and that can take a **lot** of time. Thank god you
don't run intot hem very often.
Jul 20 '05 #9
Pablo Sanchez (ho******@blueo akdb.com) writes:
Erland Sommarskog <so****@algonet .se> wrote in
news:Xn******** *************@1 27.0.0.1:
I'll add one more case: you already have a stored procedures that
performs some complex logic, and this procedure accepts its input
in scalar parameters, and you want to apply that logic to entire
set of data.


I'll counter the above with the fact that you're using the wrong
tool for the job. The SP was written to handle single row inserts
therefore if you plan on doing batch inserts, then the SQL should be
written and tuned accordingly. Unless you wish this side of sucky
performance. :)


I'm not talking of simple SPs that inserts a single row into a table.
I'm talking about stored procedures with more than 1500 lines of code,
and which calls plenty of over procedures, activating another 1500 lines
of code. Those lines of code include important business rules, that you
don't want to have duplicated in a scalar version of the procedure and
a table-oriented one. And when many of the calls to the procedure for
busieness reasons are in fact one off, there may be a performance penalty
of the procedure is rewritten to be table-oriented.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
9063
by: Alban Hertroys | last post by:
Oh no! It's me and transactions again :) I'm not really sure whether this is a limitation of psycopg or postgresql. When I use multiple cursors in a transaction, the records inserted at the start of the transaction aren't visible to those later on in that transaction (using a different cursor). Attached is a simplified example (the except's are a bit blunt, I know) of what I'm trying to do. In reality, the different cursors are...
22
10681
by: T.S.Negi | last post by:
Hi All, I want to avoid using cursors and loops in stored procedures. Please suggest alternate solutions with example (if possible). Any suggestion in these regards will be appreciated. Thanks in advance, T.S.Negi
5
6197
by: Todd Huish | last post by:
I have noticed something disturbing when retrieving datasets over a relatively slow line (multiple T1). I am looking at about 25 seconds to retrieve 500 rows via a php-odbc link. This same select from the cli is for all intents practicaly instantaneous. After much research I discovered that PHP by default uses a dynamic cursor type which can be quite a bit slower than a forward only cursor. BTW I have been searching forward only/read...
6
2515
by: a | last post by:
Hello, I am doing some multithreading in an MDI app, and I can't seem to get the cursor to stay as an Hourglass. I call: Cursor.Current = cursors.wait at the beginning of my routing, and set it back to cursors.default when the thread ends (using a callback)
10
17395
by: Just Me | last post by:
Does Me.Cursor.Current=Cursors.WaitCursor set the current property of Me.Cursor to Cursors.WaitCursor And Me.Cursor.Current=Cursors.Default set the Me.Current property to something (default) stored in Me.Cursor. Or is Cursors.Default some process wide cursor shape? What is a correct statement?
5
1397
by: Boni | last post by:
Dear all, 1.Is there a standard set of cursors in windows which can be used? If yes, where. I need a "hand"- cursor for drag-drop operation. 2. What is a best practice to place cursors? In app directory or in resource file? Thanks, Boni
7
3558
by: H. Williams | last post by:
I know the .Net Cursor class doesn't work with color cursors. So I'm currently using the LoadCursorFromFile API with reflection to set color cursors: here is my code: public static extern IntPtr LoadCursorFromFile( string fileName ); IntPtr hwdCursor= LoadCursorFromFile( "color.cur" ); myCursor.GetType().InvokeMember("handle",BindingFlags.Public |
17
6828
by: vishal | last post by:
I am new to sql and require some help on cursors? what are they and how and why are they used for??? it will be kind enough if anyone helps me in this regards.. regards vishal jain.
3
2306
by: schwartzenberg | last post by:
Dear friends, I have just run into a strange DB2 problem. Something i'd some of you would answer, if only shortly. My basic question is: How do i ensure 'insensitive' (ie static) cursors that are only forward readable (in DB2 for mainframe)?? It seems that the cursors i'm working on suddenly have become (after
1
6702
by: Dima Kuchin | last post by:
Hello, I was trying to find the information about when and where should I use cursors in DB2, no luck. Maybe you can point me to some article that describes just that (or tell me which page is it in DB2 SQL reference book, if it's there)? Or maybe you can post a short answer here, if there's no dedicated article? Another question would be about scrollable cursors (just found out about their existence) - what performance gains will I...
0
9796
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10757
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10860
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10420
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9583
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7134
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5804
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6002
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3239
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.