November 21, 2014 at 2:23 pm
g.britton (11/21/2014)
ZZartin (11/21/2014)
Sean Lange (11/21/2014)
ZZartin (11/21/2014)
Just out of curiousity but how do you use a cursor without using a while loop?Under the hood a cursor is a just a while loop on steroids that is true. This discussion has been about using an explicit while loop versus a cursor. 😉
I guess I was just kind of confused why someone would think that explicitly loading a temp table then looping through it with a bunch of individual selects would ever be either more efficient or easier to code than just using a cursor with FETCH NEXT calls.
It's not just about efficiency, though cursors are usually the least efficient way to solve a problem. It's also about the inherent DRY-violation. Since SQL lacks modern programming constructs, you have to issue the FETCH twice. Not too bad if there are only a few variables. Absolutely, completely terrible to read/debug/modify if you have 10, 20, 30 or more (yes, I've seen that many!).
Copying and pasting a fetch next statement is hard?
And that many variables is easier to manage in a select statement why?
November 21, 2014 at 2:31 pm
no one said cursors are bad and should not be used, i have done lots of loops with cursors
and i also wanted to know how this code can be changed or done alternative method that's all.
you guys jumped into conclusion and all this article proving about what is cursors and loops. please stop
i know all that points..
all i wanted is to know how in T-SQL it can be changed the way this code was written into a different way.
November 21, 2014 at 2:35 pm
ZZartin (11/21/2014)
g.britton (11/21/2014)
ZZartin (11/21/2014)
Sean Lange (11/21/2014)
ZZartin (11/21/2014)
Just out of curiousity but how do you use a cursor without using a while loop?Under the hood a cursor is a just a while loop on steroids that is true. This discussion has been about using an explicit while loop versus a cursor. 😉
I guess I was just kind of confused why someone would think that explicitly loading a temp table then looping through it with a bunch of individual selects would ever be either more efficient or easier to code than just using a cursor with FETCH NEXT calls.
It's not just about efficiency, though cursors are usually the least efficient way to solve a problem. It's also about the inherent DRY-violation. Since SQL lacks modern programming constructs, you have to issue the FETCH twice. Not too bad if there are only a few variables. Absolutely, completely terrible to read/debug/modify if you have 10, 20, 30 or more (yes, I've seen that many!).
Copying and pasting a fetch next statement is hard?
And that many variables is easier to manage in a select statement why?
Copy/Paste is just what I want to avoid...and not just me. I'll remember to copy/paste today and maybe tomorrow. The next unlucky person to work on it? Who knows? That's why I strive for DRY (and, it rhymes!)
That many variables are easier to manage in a select statement because you only need one select statement. DRY wins again!
Gerald Britton, Pluralsight courses
November 21, 2014 at 2:37 pm
Damian-167372 (11/21/2014)
no one said cursors are bad and should not be used, i have done lots of loops with cursorsand i also wanted to know how this code can be changed or done alternative method that's all.
you guys jumped into conclusion and all this article proving about what is cursors and loops. please stop
i know all that points..
all i wanted is to know how in T-SQL it can be changed the way this code was written into a different way.
Well, I gave you an example on how to do it without loops or cursors. Is the performance better? Maybe not that much, but as you said, is was meant only as an example on how to do things different.
November 21, 2014 at 2:38 pm
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.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 21, 2014 at 2:42 pm
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.
Gerald Britton, Pluralsight courses
November 21, 2014 at 2:44 pm
Damian-167372 (11/21/2014)
no one said cursors are bad and should not be used, i have done lots of loops with cursorsand i also wanted to know how this code can be changed or done alternative method that's all.
you guys jumped into conclusion and all this article proving about what is cursors and loops. please stop
i know all that points..
all i wanted is to know how in T-SQL it can be changed the way this code was written into a different way.
Well your thread got hijacked by a more in depth discussion that was sparked by your question.
As I said before, just changing a cursor to a while loop, or the other way around is an exercise in futility. Your process requires some form of RBAR and you have code that is already working. "If it ain't broke, don't fix it." 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 21, 2014 at 2:48 pm
g.britton (11/21/2014)
ZZartin (11/21/2014)
g.britton (11/21/2014)
ZZartin (11/21/2014)
Sean Lange (11/21/2014)
ZZartin (11/21/2014)
Just out of curiousity but how do you use a cursor without using a while loop?Under the hood a cursor is a just a while loop on steroids that is true. This discussion has been about using an explicit while loop versus a cursor. 😉
I guess I was just kind of confused why someone would think that explicitly loading a temp table then looping through it with a bunch of individual selects would ever be either more efficient or easier to code than just using a cursor with FETCH NEXT calls.
It's not just about efficiency, though cursors are usually the least efficient way to solve a problem. It's also about the inherent DRY-violation. Since SQL lacks modern programming constructs, you have to issue the FETCH twice. Not too bad if there are only a few variables. Absolutely, completely terrible to read/debug/modify if you have 10, 20, 30 or more (yes, I've seen that many!).
Copying and pasting a fetch next statement is hard?
And that many variables is easier to manage in a select statement why?
Copy/Paste is just what I want to avoid...and not just me. I'll remember to copy/paste today and maybe tomorrow. The next unlucky person to work on it? Who knows? That's why I strive for DRY (and, it rhymes!)
That many variables are easier to manage in a select statement because you only need one select statement. DRY wins again!
Yes, but instead of repeating one line of code, you'll be repeating the information to have the information for the loop. DRY-violation again. 😀
November 21, 2014 at 3:02 pm
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.
Sad, but true. They really shouldn't be considered "standard".
[Off-topic, but related: Likewise, so-called "standard" code showing clustered indexes on identity columns, which shouldn't be considered "standard" either, and which do far more damage than coding two FETCHes.]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 21, 2014 at 3:07 pm
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.
November 21, 2014 at 3:45 pm
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 don't find two different reads of the same data structure "intuitive", maybe that's just me.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 21, 2014 at 10:03 pm
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
November 21, 2014 at 10:36 pm
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.
Gerald Britton, Pluralsight courses
November 22, 2014 at 12:27 am
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).
November 22, 2014 at 9:11 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 31 through 45 (of 50 total)
You must be logged in to reply to this topic. Login to reply