If not cursors then what

I've noted quite a bit of articles about not using cursors in SQL Server.
That's OK with me. What then do you use to iterate through large sets of
records? Could I have an example or two and maybe a link to a location that
give a bit more info.
Thank you
PeteYou don't iterate through sets of rows (except maybe in a client
application). 99.99% of the time this is not necessary and set-based SELECT
/ UPDATE / INSERT / DELETE statements can do the job much more efficiently.
Many examples of cursor code are written by programmers who aren't familiar
with SQL and are more comfortable with row-by-row sequential processing than
with set-based SELECT statements. Familiarise yourself with the SQL concepts
of joins, aggregates, subqueries, etc and you'll soon find solutions to your
requirements which avoid iterative processing altogether.
There are a few situations where a cursor might offer a more efficient
solution than a SELECT statement. But in general, if you think you need a
cursor, think again. If you're sure you need a cursor, post a description of
your requirements to this group and see if someone can suggest an
alternative.
--
David Portas
--
Please reply only to the newsgroup
--|||> What then do you use to iterate through large sets of
> records?
The problem is not finding a way to iterate. The goal is to find a way to
*not* have to iterate. If you can solve the problem using a set, instead of
one row at a time. There is no "code sample" out there that will show you
how to eliminate a cursor and use a set-based query instead; it is very
situation-dependent.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Set-Based' Could I see an example of that?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:sIedndpffMQj11ai4p2dnA@.giganews.com...
> You don't iterate through sets of rows (except maybe in a client
> application). 99.99% of the time this is not necessary and set-based
SELECT
> / UPDATE / INSERT / DELETE statements can do the job much more
efficiently.
> Many examples of cursor code are written by programmers who aren't
familiar
> with SQL and are more comfortable with row-by-row sequential processing
than
> with set-based SELECT statements. Familiarise yourself with the SQL
concepts
> of joins, aggregates, subqueries, etc and you'll soon find solutions to
your
> requirements which avoid iterative processing altogether.
> There are a few situations where a cursor might offer a more efficient
> solution than a SELECT statement. But in general, if you think you need a
> cursor, think again. If you're sure you need a cursor, post a description
of
> your requirements to this group and see if someone can suggest an
> alternative.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>|||> Set-Based' Could I see an example of that?
Show an example (DDL, sample data and desired results) of what you are doing
that "requires" a cursor. And hopefully someone will be able to show you
how to make it set-based instead.
See http://www.aspfaq.com/5006
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||This is a multi-part message in MIME format.
--=_NextPart_000_00DB_01C3B7ED.7FE0FFD0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
A set-based query is one that acts on all of the desired rows as a set. For
example, if you wanted a list of all customers who ordered ProductID = 7,
the following would give you that list:
select
c.*
from
Customers as c
where exists
(
select
*
from
Orders as o
join [Order Details] as od on od.OrderID = o.OrderID
where
od.ProductID = 7
and c.CustomerID = o.CustomerID
)
Here, you didn't set up a cursor on the Customers table and then picked
through the Orders and Order Details tables. You just ran the query in one
step.
HTH
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Peter Shankey" <shankeyp@.NOSPAM.comcast.net> wrote in message
news:h56dnbqeeY6o01aiRVn-sw@.comcast.com...
Set-Based' Could I see an example of that?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:sIedndpffMQj11ai4p2dnA@.giganews.com...
> You don't iterate through sets of rows (except maybe in a client
> application). 99.99% of the time this is not necessary and set-based
SELECT
> / UPDATE / INSERT / DELETE statements can do the job much more
efficiently.
> Many examples of cursor code are written by programmers who aren't
familiar
> with SQL and are more comfortable with row-by-row sequential processing
than
> with set-based SELECT statements. Familiarise yourself with the SQL
concepts
> of joins, aggregates, subqueries, etc and you'll soon find solutions to
your
> requirements which avoid iterative processing altogether.
> There are a few situations where a cursor might offer a more efficient
> solution than a SELECT statement. But in general, if you think you need a
> cursor, think again. If you're sure you need a cursor, post a description
of
> your requirements to this group and see if someone can suggest an
> alternative.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>
--=_NextPart_000_00DB_01C3B7ED.7FE0FFD0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

A set-based query is one that acts on =all of the desired rows as a set. For example, if you wanted a list of all =customers who ordered ProductID =3D 7, the following would give you that =list:
select
=c.*
from
Customers as =c
where exists
(
=select
= *
=from
= Orders as =o
join [Order =Details] as od on od.OrderID =3D o.OrderID
=where
= od.ProductID =3D 7
and c.CustomerID =3D o.CustomerID
)
Here, you didn't set up a cursor on =the Customers table and then picked through the Orders and Order Details tables. =You just ran the query in one step.
HTH
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Peter Shankey" wrote in message news:h56dnbqeeY6o01aiRVn=-sw@.comcast.com...Set-Based' Could I see an example of that?"David Portas" wrote in messagenews:sIedndpffMQj11ai4p=2dnA@.giganews.com...> You don't iterate through sets of rows (except maybe in a client> =application). 99.99% of the time this is not necessary and set-basedSELECT> / UPDATE / INSERT / DELETE statements can do =the job much moreefficiently.>> Many examples of cursor code =are written by programmers who aren'tfamiliar> with SQL and are =more comfortable with row-by-row sequential processingthan> with =set-based SELECT statements. Familiarise yourself with the SQLconcepts> =of joins, aggregates, subqueries, etc and you'll soon find solutions toyour> requirements which avoid iterative processing altogether.>> There are a few situations where a cursor =might offer a more efficient> solution than a SELECT statement. But in =general, if you think you need a> cursor, think again. If you're sure you =need a cursor, post a descriptionof> your requirements to this group =and see if someone can suggest an> alternative.>> =--> David Portas> --> Please reply only to the =newsgroup> -->>

---

--=_NextPart_000_00DB_01C3B7ED.7FE0FFD0--|||I usually describe this with a drop dead simple example:
Say you want to increase the price with 10% for each product that comes from Sweden. Say we have
1,000,000 rows in the table and 1,000 come from Sweden.
A procedural thinking would be to write a SELECT statement which returns all rows. Then for each
row, the client app check if the country is Sweden. If so, the client app executes an UPDATE
statement to increase the price for that product.
This result in reading 1,000,000 rows to the client and executing 1,000 UPDATE statements.
A set based thinking is to execute one UPDATE statement:
UPDATE products
SET price = price * 1.1
WHERE country = 'Sweden'
Here we read 0 rows and execute 1 UPDATE statement.
I know, this is an extremely simplified example, but it serves to explain the differences in
approaching the problem. David posted a good, slightly more complex, example.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Peter Shankey" <shankeyp@.NOSPAM.comcast.net> wrote in message
news:h56dnbqeeY6o01aiRVn-sw@.comcast.com...
> Set-Based' Could I see an example of that?
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:sIedndpffMQj11ai4p2dnA@.giganews.com...
> > You don't iterate through sets of rows (except maybe in a client
> > application). 99.99% of the time this is not necessary and set-based
> SELECT
> > / UPDATE / INSERT / DELETE statements can do the job much more
> efficiently.
> >
> > Many examples of cursor code are written by programmers who aren't
> familiar
> > with SQL and are more comfortable with row-by-row sequential processing
> than
> > with set-based SELECT statements. Familiarise yourself with the SQL
> concepts
> > of joins, aggregates, subqueries, etc and you'll soon find solutions to
> your
> > requirements which avoid iterative processing altogether.
> >
> > There are a few situations where a cursor might offer a more efficient
> > solution than a SELECT statement. But in general, if you think you need a
> > cursor, think again. If you're sure you need a cursor, post a description
> of
> > your requirements to this group and see if someone can suggest an
> > alternative.
> >
> > --
> > David Portas
> > --
> > Please reply only to the newsgroup
> > --
> >
> >
>|||Oh, that's not a big deal.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OJ72$yBuDHA.2712@.tk2msftngp13.phx.gbl...
> I usually describe this with a drop dead simple example:
> Say you want to increase the price with 10% for each product that comes
from Sweden. Say we have
> 1,000,000 rows in the table and 1,000 come from Sweden.
> A procedural thinking would be to write a SELECT statement which returns
all rows. Then for each
> row, the client app check if the country is Sweden. If so, the client app
executes an UPDATE
> statement to increase the price for that product.
> This result in reading 1,000,000 rows to the client and executing 1,000
UPDATE statements.
> A set based thinking is to execute one UPDATE statement:
> UPDATE products
> SET price = price * 1.1
> WHERE country = 'Sweden'
> Here we read 0 rows and execute 1 UPDATE statement.
> I know, this is an extremely simplified example, but it serves to explain
the differences in
> approaching the problem. David posted a good, slightly more complex,
example.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Peter Shankey" <shankeyp@.NOSPAM.comcast.net> wrote in message
> news:h56dnbqeeY6o01aiRVn-sw@.comcast.com...
> > Set-Based' Could I see an example of that?
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > news:sIedndpffMQj11ai4p2dnA@.giganews.com...
> > > You don't iterate through sets of rows (except maybe in a client
> > > application). 99.99% of the time this is not necessary and set-based
> > SELECT
> > > / UPDATE / INSERT / DELETE statements can do the job much more
> > efficiently.
> > >
> > > Many examples of cursor code are written by programmers who aren't
> > familiar
> > > with SQL and are more comfortable with row-by-row sequential
processing
> > than
> > > with set-based SELECT statements. Familiarise yourself with the SQL
> > concepts
> > > of joins, aggregates, subqueries, etc and you'll soon find solutions
to
> > your
> > > requirements which avoid iterative processing altogether.
> > >
> > > There are a few situations where a cursor might offer a more efficient
> > > solution than a SELECT statement. But in general, if you think you
need a
> > > cursor, think again. If you're sure you need a cursor, post a
description
> > of
> > > your requirements to this group and see if someone can suggest an
> > > alternative.
> > >
> > > --
> > > David Portas
> > > --
> > > Please reply only to the newsgroup
> > > --
> > >
> > >
> >
> >
>|||> Oh, that's not a big deal.
I know. But the purpose of the example is that each example is dependent on what you want to do. As
already mentioned, if you have a specific case, you can always post your details and see if anyone
can come up with a proposal for a set-based solution.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Peter Shankey" <shankeyp@.NOSPAM.comcast.net> wrote in message
news:utudnQyQnL9WxlaiRVn-tw@.comcast.com...
> Oh, that's not a big deal.
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OJ72$yBuDHA.2712@.tk2msftngp13.phx.gbl...
> > I usually describe this with a drop dead simple example:
> >
> > Say you want to increase the price with 10% for each product that comes
> from Sweden. Say we have
> > 1,000,000 rows in the table and 1,000 come from Sweden.
> >
> > A procedural thinking would be to write a SELECT statement which returns
> all rows. Then for each
> > row, the client app check if the country is Sweden. If so, the client app
> executes an UPDATE
> > statement to increase the price for that product.
> > This result in reading 1,000,000 rows to the client and executing 1,000
> UPDATE statements.
> >
> > A set based thinking is to execute one UPDATE statement:
> > UPDATE products
> > SET price = price * 1.1
> > WHERE country = 'Sweden'
> > Here we read 0 rows and execute 1 UPDATE statement.
> >
> > I know, this is an extremely simplified example, but it serves to explain
> the differences in
> > approaching the problem. David posted a good, slightly more complex,
> example.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Peter Shankey" <shankeyp@.NOSPAM.comcast.net> wrote in message
> > news:h56dnbqeeY6o01aiRVn-sw@.comcast.com...
> > > Set-Based' Could I see an example of that?
> > >
> > > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > > news:sIedndpffMQj11ai4p2dnA@.giganews.com...
> > > > You don't iterate through sets of rows (except maybe in a client
> > > > application). 99.99% of the time this is not necessary and set-based
> > > SELECT
> > > > / UPDATE / INSERT / DELETE statements can do the job much more
> > > efficiently.
> > > >
> > > > Many examples of cursor code are written by programmers who aren't
> > > familiar
> > > > with SQL and are more comfortable with row-by-row sequential
> processing
> > > than
> > > > with set-based SELECT statements. Familiarise yourself with the SQL
> > > concepts
> > > > of joins, aggregates, subqueries, etc and you'll soon find solutions
> to
> > > your
> > > > requirements which avoid iterative processing altogether.
> > > >
> > > > There are a few situations where a cursor might offer a more efficient
> > > > solution than a SELECT statement. But in general, if you think you
> need a
> > > > cursor, think again. If you're sure you need a cursor, post a
> description
> > > of
> > > > your requirements to this group and see if someone can suggest an
> > > > alternative.
> > > >
> > > > --
> > > > David Portas
> > > > --
> > > > Please reply only to the newsgroup
> > > > --
> > > >
> > > >
> > >
> > >
> >
> >
>