April 30, 2012 at 5:59 am
dwain.c (4/30/2012)
Writing articles ain't that difficult. Look at me. Just over 400 points and I have one published and another accepted/awaiting publication.The trick is to find something that you've never seen anyone do before. And enjoy solving it. If it's actually useful, there's a good chance Steve will accept it.
If it isn't accepted, just blog on it.
I think Jeff even read my article. 🙂
Actually, the only things I've ever written about are things that have already been written about many times. I just explain things a bit differently than a lot of folks do.
--Jeff Moden
April 30, 2012 at 6:04 am
Jeff Moden (4/30/2012)
dwain.c (4/30/2012)
Writing articles ain't that difficult. Look at me. Just over 400 points and I have one published and another accepted/awaiting publication.The trick is to find something that you've never seen anyone do before. And enjoy solving it. If it's actually useful, there's a good chance Steve will accept it.
If it isn't accepted, just blog on it.
I think Jeff even read my article. 🙂
Actually, the only things I've ever written about are things that have already been written about many times. I just explain things a bit differently than a lot of folks do.
Is that like deja vu all over again? 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 30, 2012 at 6:10 am
Maybe but I do know that when I come to a fork in the road, I take it. 😉
--Jeff Moden
May 4, 2012 at 4:43 am
Cadavre (4/25/2012)
Method 2:
SET NOCOUNT ON;--DDL
CREATE TABLE Ex (NAME VARCHAR(30));
--Sample Data
INSERT INTO Ex
SELECT 'Jack'
UNION ALL SELECT 'Vinu'
UNION ALL SELECT 'Jim'
UNION ALL SELECT 'Stan'
UNION ALL SELECT 'Ash';
--No Cursors
SELECT
MAX(CASE WHEN rn = 1 THEN NAME ELSE NULL END),
MAX(CASE WHEN rn = 2 THEN NAME ELSE NULL END),
MAX(CASE WHEN rn = 3 THEN NAME ELSE NULL END),
MAX(CASE WHEN rn = 4 THEN NAME ELSE NULL END),
MAX(CASE WHEN rn = 5 THEN NAME ELSE NULL END)
FROM (SELECT NAME, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
FROM Ex) a;
Results in: -
---- ---- ---- ---- ----
Jack Vinu Jim Stan Ash
Thanks Cadavre,
I really liked this one. Can this be customized a little more?
Like, if we have the following requirements:
1. We don't know how many rows there are in the table :
In that case we would have to use a loop. right??....Like get the count of rows in the table and use that count in the While loop...right??....Is there a way of avoiding the loop here??....
2. We want the results to be stored in one temporary variable rather than four of them.
I tried customizing your code a little and came up with the following:
Declare @temp varchar(10)
Declare @cnt int
Declare @curs int = 1
Select @cnt = Count(*) From Ex
While(@curs <= @cnt)
Begin
Select @temp = MAX(Case When rn = @curs then Name Else '' End)
FROM (SELECT NAME, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
FROM Ex) a
Print @temp
Set @curs = @curs + 1
End
I got the second requirement right I guess.....but the for the first requirement...I could not avoid the loop....So can your code be customized to include the above mentioned requirements??
May 4, 2012 at 5:59 am
vinu512 (5/4/2012)
Thanks Cadavre,I really liked this one. Can this be customized a little more?
Like, if we have the following requirements:
1. We don't know how many rows there are in the table :
In that case we would have to use a loop. right??....Like get the count of rows in the table and use that count in the While loop...right??....Is there a way of avoiding the loop here??....
NO!! No loops 😛
DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = COALESCE(@SQL,'') + ',' +CHAR(13)+CHAR(10)+
'MAX(CASE WHEN rn = '+ CAST(ROW_NUMBER() OVER(ORDER BY NAME) AS NVARCHAR(6)) + ' THEN NAME ELSE NULL END)'
FROM Ex;
SET @SQL = 'SELECT ' + STUFF(@SQL,1,3,'') + ' FROM (SELECT NAME, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn' + CHAR(13) + CHAR(10) +
'FROM Ex) a;';
EXECUTE sp_executesql @SQL;
vinu512 (5/4/2012)
2. We want the results to be stored in one temporary variable rather than four of them.
You're not thinking SET-based, you're thinking procedurally. SQL is designed for set operations, if you're using it for procedural code then you're probably doing something wrong.
May 4, 2012 at 6:28 am
You're not thinking SET-based, you're thinking procedurally. SQL is designed for set operations, if you're using it for procedural code then you're probably doing something wrong.
I agree with you Cadavre. But my requirement here is such that I am not able to think set based. Following is the requirement:
I have to get the "Names" one by one from the table, store it in one temporary variable ie: "@temp" and then pass this variable to another procedure which will do some other stuff with the "Name".
Your code was very simple and was the closest to what I had in mind. But if we could just customize it to:
Select one name at a time and store it in one temporary variable(this has to be done using something like a Pseudo Loop because the names have to be stored one by one in the same variable).
Can your code be customized to do this without loop/Cursor/Temp Table?
Thanks For All the help.
May 4, 2012 at 6:49 am
I give up...I've banged my head on this for really long....I have come to the conclusion that a loop cannot be avoided in this scenario.
However un-SetBased( 😛 ) or Procedural the requirement this may be, since the results have to be got into the same variable again and again then some kind of loop has to exist.
Is that right?
May 4, 2012 at 7:03 am
vinu512 (5/4/2012)
I agree with you Cadavre. But my requirement here is such that I am not able to think set based. Following is the requirement:
I have to get the "Names" one by one from the table, store it in one temporary variable ie: "@temp" and then pass this variable to another procedure which will do some other stuff with the "Name".
If this is the case, then you also need to look at the stored procedure you are passing the name to one by one. It looks like this procedure may also be a candidate for rework to allow it to be used in a set-base manner.
Perhaps, instead of a single value, it is passed a set of values that could be from 1 to however many need to be processed when invoked.
May 4, 2012 at 7:04 am
vinu512 (5/4/2012)
I give up...I've banged my head on this for really long....I have come to the conclusion that a loop cannot be avoided in this scenario.However un-SetBased( 😛 ) or Procedural the requirement this may be, since the results have to be got into the same variable again and again then some kind of loop has to exist.
Is that right?
Still no. Your limitations are only in what you can design.
DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = COALESCE(@SQL,'') +CHAR(13)+CHAR(10)+
'EXECUTE ' + QUOTENAME('yourSprocName') + ' ' + CHAR(39) + NAME + CHAR(39) + ';'
FROM Ex;
SET @SQL = STUFF(@SQL,1,2,'');
EXECUTE sp_executesql @SQL;
Produces: -
EXECUTE [yourSprocName] 'Jack';
EXECUTE [yourSprocName] 'Vinu';
EXECUTE [yourSprocName] 'Jim';
EXECUTE [yourSprocName] 'Stan';
EXECUTE [yourSprocName] 'Ash';
So I'd execute the sproc for every row in the table.
This is not necessarily better than a loop, it depends on a lot of factors. Chances are that if you're having to execute a sproc per row, then your design is incorrect.
May 25, 2012 at 6:19 pm
vinu512 (5/4/2012)
You're not thinking SET-based, you're thinking procedurally. SQL is designed for set operations, if you're using it for procedural code then you're probably doing something wrong.
I agree with you Cadavre. But my requirement here is such that I am not able to think set based. Following is the requirement:
I have to get the "Names" one by one from the table, store it in one temporary variable ie: "@temp" and then pass this variable to another procedure which will do some other stuff with the "Name".
Your code was very simple and was the closest to what I had in mind. But if we could just customize it to:
Select one name at a time and store it in one temporary variable(this has to be done using something like a Pseudo Loop because the names have to be stored one by one in the same variable).
Can your code be customized to do this without loop/Cursor/Temp Table?
Thanks For All the help.
Sorry for the late reply but, unless it's for something like sending an email using sp_send_dbmail where the stored procedure cannot be changed, I usually try to convince people to rework the stored procedure so that IT is setbased instead of RBAR.
--Jeff Moden
May 26, 2012 at 1:36 pm
vinu512 (5/4/2012)
I give up...I've banged my head on this for really long....I have come to the conclusion that a loop cannot be avoided in this scenario.However un-SetBased( 😛 ) or Procedural the requirement this may be, since the results have to be got into the same variable again and again then some kind of loop has to exist.
Is that right?
No, this is absolutely not true. Here (EXECute SQL Commands over entire sets of parameters![/url]) is an article that presents a tool and example of a procedure that does exactly that and much more (in fact it can automate your process for you), without a single loop anywhere.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2012 at 10:34 pm
RBarryYoung (5/26/2012)
vinu512 (5/4/2012)
I give up...I've banged my head on this for really long....I have come to the conclusion that a loop cannot be avoided in this scenario.However un-SetBased( 😛 ) or Procedural the requirement this may be, since the results have to be got into the same variable again and again then some kind of loop has to exist.
Is that right?
No, this is absolutely not true. Here (EXECute SQL Commands over entire sets of parameters![/url]) is an article that presents a tool and example of a procedure that does exactly that and much more (in fact it can automate your process for you), without a single loop anywhere.
Thanks for the link Mr. Young. It seems like a very good option. Is it better than Cursors/Loops performance-wise as well??....I'll check it too with a couple of my procedures and get back to you.
May 27, 2012 at 10:49 pm
vinu512 (5/27/2012)
RBarryYoung (5/26/2012)
vinu512 (5/4/2012)
I give up...I've banged my head on this for really long....I have come to the conclusion that a loop cannot be avoided in this scenario.However un-SetBased( 😛 ) or Procedural the requirement this may be, since the results have to be got into the same variable again and again then some kind of loop has to exist.
Is that right?
No, this is absolutely not true. Here (EXECute SQL Commands over entire sets of parameters![/url]) is an article that presents a tool and example of a procedure that does exactly that and much more (in fact it can automate your process for you), without a single loop anywhere.
Thanks for the link Mr. Young. It seems like a very good option. Is it better than Cursors/Loops performance-wise as well??....I'll check it too with a couple of my procedures and get back to you.
Generally I find that it is, however, I am sure that someone could easily come up with cases where a customized Cursor routine could beat this very general procedure. If you do find such a case, let me know and I'll see what I can do to make it faster for that specific case.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2012 at 11:11 pm
I tried executing the following example from your link:
CREATE TABLE #temp (DB sysname, [Schema] sysname, Routine sysname);
INSERT INTO #temp
EXECUTE OVER_SET '
SELECT ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME
FROM [{db}].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION like "%cursor%" ',
@from = 'sys.sysdatabases WHERE dbid > 4',
@subs1 = '{db}=name',
@quote = '"'
;
SELECT * from #temp;
It gave me the following Error:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'OVER_SET'.
I am using SQL Server 2008. Does it work in SQL Server 2008??
May 28, 2012 at 5:02 am
"OVER_SET" is the name of the stored procedure that I wrote and that the article discusses. You first have to copy the code from the bottom of the article into your database and then execute it. This will make the OVER_SET procedure so that you can then use it as you are trying to do above.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 46 through 59 (of 59 total)
You must be logged in to reply to this topic. Login to reply