Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 4 Ways to Increase Your Salary (Using UPDATE) Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, November 26, 2009 7:38 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, April 22, 2014 12:20 PM Points: 1, Visits: 19
 I've been using Martin's solution for years, however, slightly modified. BEGINDeclare @min int, @max intSelect @min = Min(ID), @max=Max(ID) -- OR a user-defined max from empWHILE @min <= @max BEGIN UPDATE emp SET salary = (salary * 115) /100 WHERE emp.id = @min Select @min = Min(ID) from emp where ID >@min END
Post #825228
 Posted Thursday, November 26, 2009 8:30 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, June 1, 2010 10:44 AM Points: 2, Visits: 14
 Since \ and * share the same precidence, does that not mean that if we have salary*115/100 that the salary*115 part will happen first? Or is there no guarentee of the ordering?Edit:The presidence article that was linked to says this "When two operators in an expression have the same operator precedence level, they are evaluated left to right based on their position in the expression."
Post #825245
 Posted Thursday, November 26, 2009 9:20 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, February 26, 2016 4:07 AM Points: 5, Visits: 235
 I found a very efficient way to DECREASE my salary.I stayed in a government job for longer than I should have, and inflation did the rest
Post #825276
 Posted Thursday, November 26, 2009 10:59 AM
 SSCommitted Group: General Forum Members Last Login: 2 days ago @ 6:49 AM Points: 1,634, Visits: 1,621
 Actually, the author and the first two replyers are wrong about the order of precedence. If you fully understand tha order of precedence, multiplication is evaluated before division. The order is as follows:Paranthesis -> Exponents -> Multiplication -> Division -> Addition -> SubtractionThis can be remembered using the following nemonic: Please excuse my dear aunt Sally. That's how I learned it in school.But don't believe me. Try it out:Declare @Salary intSet @Salary = 85199Select @Salary*115/100, (@Salary*115)/100 ----------- -----------97978 97978(1 row(s) affected) My blog: SQL SoldierTwitter: @SQLSoldierMy book: Pro SQL Server 2008 MirroringMicrosoft Certified Master, SQL Server MVPDatabase Engineer at BlueMountain Capital Management
Post #825311
 Posted Thursday, November 26, 2009 11:18 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, December 1, 2009 7:03 PM Points: 4, Visits: 14
 Despite the misinformed on operator precedence, just multiply by 1.15! Keep things DRY and simple
Post #825314
 Posted Thursday, November 26, 2009 1:35 PM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, June 1, 2010 10:44 AM Points: 2, Visits: 14
 If multiplication happened before division than these would return the same answer, but they dont.Declare @Salary intSet @Salary = 85199Select @Salary/100*115, @Salary/(100*115)Edit: please note this is not a solution to the problem, the both have wrong answers for the original problem.
Post #825348
 Posted Thursday, November 26, 2009 1:57 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 3:23 PM Points: 42,036, Visits: 39,415
 Agreed. Items like multiplication and division have the same precedence and, provided nothing is include to interfer with the order of precedence (such as parenthesis), are solved left to right as they appear. --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." Helpful Links:How to post code problemsHow to post performance problems
Post #825358
 Posted Thursday, November 26, 2009 2:58 PM
 Ten Centuries Group: General Forum Members Last Login: Monday, October 31, 2016 11:11 AM Points: 1,076, Visits: 3,229
 jarred.nicholls (11/26/2009)Jason Lees-299789 (11/26/2009)As an aside, one of my colleagues ran some internal tests on a somewhat larger data set (1000 rows, same number of columns) and found that the Cursor ran significantly faster than the last option.Cursors and temp tables are very similar, since a cursor creates a temp table in tempdb (spooling all data to disk before being read back)...just like a temp table does. A cursor has an upfront hit when allocating itself, but that overhead doesn't occur with each row fetch and each row fetch is quite speedy.The CPU cycles is much greater on each row's operation in the identity temp table method (it is doing a COUNT(*) on the temp table each iteration). With a large data set, a cursor will (always) startup slower but finish faster and with less CPU. On a small data set, a cursor will still startup slower but finish slower as well.Bottom line is, the last method makes certain assumptions about the identity column that can't always be made and thus isn't a universal solution. And of course, 99% of the time there is a set-based solution that will run circles around any procedural solution. I once tuned a stored procedure that took over 2 hours to complete by replacing a cursor with a set-based solution and brought the execution time down to just around 15 seconds. If you are forced into a procedural situation, first post your situation onto a forum and let someone find a set-based solution to the problem (there's a great chance there is one), and if none is found, use a method that suits the size of the data set. Martin's solution is the appropriate one here, no matter the size of the data set - it doesn't use a temp table.No Arguments there, in fact i pretty much said that martins was the best non-set based solution, and im not saying that a cursor should be considered and that set based operations are not the best way.To be honest there was only one situation ive come accross that needed while loop, and never one that needed a cursor in stead of Set based theories.The issue i had with the article was that the sample data set is miniscule, so it doesnt proove that cursors are worse than loops in real world situations. _________________________________________________________________________SSC Guide to Posting and Best Practices
Post #825376
 Posted Thursday, November 26, 2009 3:27 PM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, December 1, 2009 7:03 PM Points: 4, Visits: 14
 Yeah I was just adding on to your statement...I knew you were aware of that, but was just stating to others (in many words) "what your colleague experienced makes sense and the size of the data set can make a difference"
Post #825379
 Posted Thursday, November 26, 2009 4:03 PM
 Grasshopper Group: General Forum Members Last Login: Thursday, November 17, 2016 10:51 PM Points: 10, Visits: 277
 What a useless article. It's full of misinformation and bad practice (SELECT TOP 1 id from #temp) won't even work reliably. There is no reason on earth to use any of the methods other than set or cursor, even if they were reliable, which they are not.Anyone seeking to advance their skills by using anything in this article should be very wary.
Post #825381

 Permissions