tsql with out using cursors

Question

tsql with out using cursors

Viewing 6 posts - 46 through 50 (of 50 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Koen Verbeeck (11/22/2014)
g.britton (11/21/2014)
It's not just about efficiency, though cursors are usually the least efficient way to solve a problem.
True, but not always true 😀
Before windowing functions came through in SQL Server 2012, some problems could only be done using two ways:
1. A lot of joining
2. Using a cursor
Number 1 is set-based, but scales quadratically in some cases. In other words, great for smaller sets, not so great for large sets.
Number 2 is, well, a cursor, but it scales linearly. In other words, after a while it becomes better than the set-based approach. Until windowing functions showed up of course 😀
There are some great examples in Itzik Ben-gans book about windowing functions.

Before the windowing functions, you could also do divide and conquer to improve the performance of routines where multiple joins or cursors may not be the best option. As Jeff as said many times, set based doesn't mean in one statement.

g.britton SSChampion Points: 13863 More actions · Answer 2

Lynn Pettis (11/22/2014)
g.britton (11/21/2014)
Lynn Pettis (11/21/2014)
Here is an alternative that does not use a cursor or while loop. You will have to test it yourself to be sure it does what you expect.
create table dbo.Tbl_ServerList(
server_name sysname
);
insert into dbo.Tbl_ServerList
values ('MyServer1'),('MyServer2');
go
declare @SQLCmd nvarchar(max);
select @SQLCmd = stuff((select N'union all select Loginame,HostName,DbName,cmd from openrowset(''sqlncli'',''server=''' + server_name + ';Trusted_Connection=yes;'',''exec sp_who'')' + nchar(13) + nchar(10)
from dbo.Tbl_ServerList
order by server_name
for xml path(''),TYPE).value('.','nvarchar(max)'),1,10,'');
print @SQLCmd;
INSERT INTO Capture_Logins
exec (@SQLCmd);
go
drop table dbo.Tbl_ServerList;
go
Ah yes! The third way. Not often spoken of but viable if not as scalable. For smallish cardinalities, though, a good alternative.
Not sure what you mean by "not as scalable." I use this quite often in place of cursors (which I have also written for some tasks).

I mean, should the string to be passed to sp_executesql need to exceed the NVARCHAR(max) size. I know, not too likely when dealing with typical admin tasks. Conceivable though with large databases. You could solve that problem by doing it in segments, of course, but that adds complexity that could make maintenance less pleasant.

BTW have you done any benchmarks with this approach vs cursors? If so, any general conclusions? I wonder too about error handling. Do you take special steps to ensure you can pin errors down to a particular statement in the batch?

Gerald Britton, Pluralsight courses

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

g.britton (11/22/2014)
Lynn Pettis (11/22/2014)
g.britton (11/21/2014)
Lynn Pettis (11/21/2014)
Here is an alternative that does not use a cursor or while loop. You will have to test it yourself to be sure it does what you expect.
create table dbo.Tbl_ServerList(
server_name sysname
);
insert into dbo.Tbl_ServerList
values ('MyServer1'),('MyServer2');
go
declare @SQLCmd nvarchar(max);
select @SQLCmd = stuff((select N'union all select Loginame,HostName,DbName,cmd from openrowset(''sqlncli'',''server=''' + server_name + ';Trusted_Connection=yes;'',''exec sp_who'')' + nchar(13) + nchar(10)
from dbo.Tbl_ServerList
order by server_name
for xml path(''),TYPE).value('.','nvarchar(max)'),1,10,'');
print @SQLCmd;
INSERT INTO Capture_Logins
exec (@SQLCmd);
go
drop table dbo.Tbl_ServerList;
go
Ah yes! The third way. Not often spoken of but viable if not as scalable. For smallish cardinalities, though, a good alternative.
Not sure what you mean by "not as scalable." I use this quite often in place of cursors (which I have also written for some tasks).
I mean, should the string to be passed to sp_executesql need to exceed the NVARCHAR(max) size. I know, not too likely when dealing with typical admin tasks. Conceivable though with large databases. You could solve that problem by doing it in segments, of course, but that adds complexity that could make maintenance less pleasant.
BTW have you done any benchmarks with this approach vs cursors? If so, any general conclusions? I wonder too about error handling. Do you take special steps to ensure you can pin errors down to a particular statement in the batch?

No benchmarks, and yes I have actually embedded code inside of TRY/CATCH blocks to trap and deal with errors when they are needed. Takes time and effort to build when needed, but usually worth the effort.

It also depends on what you are trying to accomplish if you go that route. As I have said, I have also written cursor routines when appropriate. You have to pick the right tool for the job at hand.

x SSC-Insane Points: 23660 More actions · Answer 4

ZZartin (11/21/2014)
g.britton (11/21/2014)
ScottPletcher (11/21/2014)
You don't "have" to do the FETCH twice. In fact, I never do that, because it's too error prone when the code is changed.
good point, though the standard examples tend to have two FETCH statements.
/shrug it's a very intuitive way to code it, and the risk that someone might forget to update one of the fetch statements should be pretty obvious pretty fast when(hopefully) the code is tested.

I wonder if maybe the "two fetch" statements protect against problems with a cursor definition that returns an empty set (no rows)? If you use the two fetch statements, does the immediate "while" test after the first "fetch next" catch a cursor returning no rows? If so then I'm guessing with "one fetch", you'll then need two tests against @@fetch_status if you really want to catch an empty row situation.

stook SSC Enthusiast Points: 126 More actions · Answer 5

if the cursor is not working then try the insensitive cursor. Without cursor - you mean something like this?

declare @dbname nvarchar(255)='', @found bit=1

while @found=1 begin

set @found=0

select top 1 @dbname=name,@found=1 from master..sysdatabases where name>@dbname and name like '%tst%' order by name

if @found=1 begin

print @dbname

end

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

stook (11/28/2014)
if the cursor is not working then try the insensitive cursor. Without cursor - you mean something like this?
declare @dbname nvarchar(255)='', @found bit=1
while @found=1 begin
set @found=0
select top 1 @dbname=name,@found=1 from master..sysdatabases where name>@dbname and name like '%tst%' order by name
if @found=1 begin
print @dbname
end
end

To be sure, that's just another form of a "cursor".

--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)