What are set based operations in MS SQL ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

What are set based operations in MS SQL ?

Author

Topic

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-11 : 05:22:08

What are set based operations in MS SQL ?

And are there any downsides ?

_____________________

Yes O !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-11 : 05:59:27

insert,delete,update etc can all be done set based if that what you're asking. didnt understand what you mean by downsides? can you elaborate?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-11 : 06:11:46

Set based is the normal kind of operatioon SQL Server should do and work with.

E 12°55'05.63"
N 56°04'39.26"

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-11 : 06:40:02

I was using a cursor to execute bulk operations in batches in a table. But was adviced to run a set based approach.

And I dont understand that. So thats why i asked, what are set based approach ?

_____________________

Yes O !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-11 : 06:56:46

You can set batch size for both BCP and BULK INSERT.
Why do you use a CURSOR?

E 12°55'05.63"
N 56°04'39.26"

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-11 : 07:03:12

No its not for bulk insert. Its a billing system, that needs to process millions of records one by one, by calling a function, and then log the results

_____________________

Yes O !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-11 : 07:37:25

A function can not alter data in outer tables.
The function can return a resultset or a scalar value.

If the function returns a resultset, use CROSS APPLY to incorporate the result in your original query.
If the function returns a scalar value, it will act as a normal column.

Please post the code you use today so that we will have some better understanding.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-11 : 07:38:25

You can think SET based vs CURSOR based as thinking in columns (SET based) and thinking in rows (CURSOR based).

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-11 : 07:46:18

And if you mean inserting into a second table as logging, you can use the new OUTPUT operator to BOTH insert into original target table AND insert into log table, simultaneously.

E 12°55'05.63"
N 56°04'39.26"

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-11 : 08:08:54

declare @batch varchar(max), @results varchar(max), @counter int, @total int

set @total = select max(number) from gateway where username = @user

set @counter = 0

while @counter =< @total
begin

set @batch = -- selects the top 1,000, next 2,000 etc up to 100,000 or millions or records

set @results = (select [dbo].[function_to_process_execution](@batch))
set @counter = @counter + 1
end

process a batch as the above

_____________________

Yes O !

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-11 : 08:56:19

Just to answer your topic question:

when a developer begins to use t-sql a typical mistake is to tackle every problem with what they are used to: a procedural approach. So for instance, when they have a result set that includes an ID and a value and they want to update a table for those IDs with that value they will often "loop" through their result set and for each id/value pair apply the update statement:
update my table set value = @myValue where id = @myID. (one update statement per result set row - very slow)

a "set based" approach is much more efficient:
update mt set mt.value = rs.value
from myTable mt
inner join resultset rs on rs.ID = mt.ID

So you will often hear that advice: "dont use cursors, use a "set based" approach"

That is obviously a simple example. Your situation sounds much more complex as your cursor iterations each deal with a "batch" of data to be processed. Your processing may utilized a set based approach for each batch - we can't tell.

Be One with the Optimizer
TG

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-11 : 09:06:44

Ok quite interesting.

My code simple, processes a billing system and inserts the results in a table. This values are identified by a batch number.

How do i read more on set based approaches ?

_____________________

Yes O !

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-11 : 09:15:07

>>How do i read more on set based approaches ?
The vast majority of t-sql reference material details set-based approaches. One good place is from Books Online:

"Accessing and Changing Database Data"
http://msdn.microsoft.com/en-us/library/ms181080(SQL.90).aspx

work through all the examples provided in the subtopics using the sql server supplied database AdventureWorks.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources