Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


4 Ways to Increase Your Salary (Using UPDATE)


4 Ways to Increase Your Salary (Using UPDATE)

Author
Message
John Lee-439997
John Lee-439997
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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
djgubber
djgubber
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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."
nickblethyn
nickblethyn
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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 Sad
Robert Davis
Robert Davis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
jarred.nicholls
jarred.nicholls
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 14
Despite the misinformed on operator precedence, just multiply by 1.15! Keep things DRY and simple :-)
djgubber
djgubber
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45418 Visits: 39942
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
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
jarred.nicholls
jarred.nicholls
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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"
LoztInSpace
LoztInSpace
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 278
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search