Row limits

Question

Row limits

Viewing 8 posts - 16 through 22 (of 22 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

stephen.long.1 (10/3/2014)
Steve, thanks for the easy question to end the week. I prefer TOP for my queries because its effects don't persist, but several of my peers use SET ROWCOUNT.
Jeff, your comment made me laugh :laugh: (thanks), and I agree with you, but I don't think a pork chop dinner should be considered punishment. 🙂

It is when the "customer" is tied to the chair, the pork chops are frozen, and the method of delivery is a 3 banded "Wrist Rocket" slingshot fired at point blank range. 😛

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Steve Jones - SSC Editor (10/3/2014)
Jeff Moden (10/3/2014)
Heh... the correct answer isn't actually listed. Answer "D" should be "During the mandatory peer review, you get a trip to the woodshed for a porkchop dinner for writing such junk code". 😀
LOL, perhaps, but imagine
create proc spReallyBusyProc
@someparam int
as
begin
set no count on
set rowcount @someparam
-- do 40 things, because that's how we write procs
select top (@y_less_than_@someparam)
somecol
from sometable
-- do 10 more things
end
It seems like it might be tricky, but it makes sense.
Set rowcount 4 - we return 4 rows
select top 8 - we return 8 rows from the query, but only 4 get displayed because of the above.

Heh... I forgot to say that it's a great question but just had to add the "D" option (stands for [font="Arial Black"]D[/font]epression left in the forehead by high velocity pork). 😛

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Nice trivia question, thanks Steve.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

Hugo Kornelis (10/3/2014)
You do not want to know how many seasoned professionals did unspeakable things to their data due to setting SET ROWCOUNT and then forgetting to reset it.

I can only imagine this being useful while testing scripts and you're too lazy to put TOP everywhere 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

October 6, 2014 at 10:20 am

#1750344

Thanks for the question.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 6

akljfhnlaflkj

SSC Guru

Points: 76202

October 6, 2014 at 10:20 am

#1750345

I've never had a need to use this by the way.

Hany Helmy SSChampion Points: 13921 More actions · Answer 7

Ed Wagner (10/3/2014)
I learned something today, so thank you.

Yes, Thank u Steve.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 8

Ed Wagner (10/3/2014)
SqlMel (10/3/2014)
But I must say that I do not use this feature in my coding.
I haven't either. I'd use TOP myself, but this is another nice tool to have in the toolbox if you ever need it.

Same here 🙂

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant