Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

4 Ways to Increase Your Salary (Using UPDATE) Expand / Collapse
Author
Message
Posted Thursday, November 26, 2009 7:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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.

BEGIN

Declare @min int, @max int
Select @min = Min(ID),
@max=Max(ID) -- OR a user-defined max
from emp

WHILE @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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 12:24 PM
Points: 5, Visits: 227
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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:16 AM
Points: 1,618, Visits: 1,554
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 -> Subtraction

This 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 int

Set @Salary = 85199

Select @Salary*115/100, (@Salary*115)/100



----------- -----------
97978 97978

(1 row(s) affected)




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #825311
Posted Thursday, November 26, 2009 11:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 int

Set @Salary = 85199

Select @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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 35,770, Visits: 32,434
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #825358
Posted Thursday, November 26, 2009 2:58 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 951, Visits: 2,621
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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 15, 2014 5:11 PM
Points: 10, Visits: 187
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
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse