SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Arup Chakraborty / 4 Ways to Increase Your Salary (Using UPDATE) / Latest Posts

RE: 4 Ways to Increase Your Salary (Using UPDATE)

Colin Heming — Tue, 01 Dec 2009 18:48:34 GMT

Operator precedence isn't just a programming or SQL server concept - it's a basic rule of mathematics. And no, multiplication doesn't occur before division or vice versa. The left-most operation is evaluated first. This is even clearly conveyed in the linked microsoft article.The reason you may get differences where division is used is not due to operator precedence - it's due to SQL server interpreting the numerator and denominator as integers and therefore defining the result to be an integer, truncating the fractional portion.The simpliest way to avoid this is to explicitly make either the numerator or denominator a decimal term, ie use / 100.0 instead of just / 100 or explicitly cast either as a decimal type. The result will then also be a decimal type. (Casting the result after the calculation has no effect.)However, in this particular case, the numerator would already be a decimal type by the time the division occurs - salary is a float so salary * 115 produces a decimal type. So this whole concept doesn't even apply to this example. Furthermore, the reason for not simply using salary * 1.15 eludes me.It may seem silly to harp on about a minor technicality, but getting such a basic concept wrong invalidates the credibility of the rest of the article. I'd advise a bit more fact checking and testing for next time.

RE: 4 Ways to Increase Your Salary (Using UPDATE)

David Walker-278941 — Mon, 30 Nov 2009 10:00:46 GMT

"Don't use salary * 115 / 100. This violates the operator precedence rule and gives the wrong result."That statement is absolutely wrong. The Microsoft page that the article links to says "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."Since, as has been mentioned in the comments, multiply and divide have the same precedence, the expressionsalary * 115 / 100is exactly and precisely the same as (salary * 115)/ 100Yes, dividing 115 / 100 returns 1, but that doesn't happen here if parentheses are not used. Salary is multiplied by 115 before the result of that is divided by 100. Left to right. (I prefer Salary * 1.15, which someone mentioned above.)It would be nice to see the original article get corrected by the author!David Walker

RE: 4 Ways to Increase Your Salary (Using UPDATE)

dalcock — Fri, 27 Nov 2009 08:45:33 GMT

I'm sure I will receive much abuse for this post, but I'm going to share my thoughts anyway because I am a big fan of SQLServer Central and I care about the quality, accuracy and relevancy of the content on this site.That being said, I found this article extremely disappointing on many levels. First off, as has been mentioned already, the operator precedence applied, as well as the corresponding result, are identical for the expressions (X * Y) / Z and X*Y/Z. Second, as the author mentions, SQL is built upon set theory and the performance of set based operations dramaticaly outperforms sequential operations, which is why I can't understand why the artical is demonstrating alternatives to set-based solutions when a real-world premise for doing so hasn't even been provided. Last, there are several set-based operations that can accomplish sophisticated updates (like using ROWCOUNT) that numerous prior articles on this very site have identified. So, as I said, I am very disappointed with this article and fail to see how it is relevant (or helpful) at all to SQL programmers new and old.Am I missing something here?

RE: 4 Ways to Increase Your Salary (Using UPDATE)

jarred.nicholls — Thu, 26 Nov 2009 18:04:46 GMT

[quote][b]fzaynoun (11/26/2009)[/b][hr]This article will improve your skills by reading it, then going into the discussion section and learning how the solution provided is wrong, why not use it, and what to use instead.[/quote]rofl

RE: 4 Ways to Increase Your Salary (Using UPDATE)

fzaynoun — Thu, 26 Nov 2009 17:34:15 GMT

This article will improve your skills by reading it, then going into the discussion section and learning how the solution provided is wrong, why not use it, and what to use instead.

RE: 4 Ways to Increase Your Salary (Using UPDATE)

azaroth_lettar — Thu, 26 Nov 2009 16:21:42 GMT

Please note that id as identity, so using [code="sql"]count (*)[/code] do not deliver the last record but much better to use [code="sql"]max (id)[/code].Besides the set should be:[code="sql"]set salary = salary * 1.15[/code]dividing machines are bad.

RE: 4 Ways to Increase Your Salary (Using UPDATE)

LoztInSpace — Thu, 26 Nov 2009 16:03:08 GMT

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.

RE: 4 Ways to Increase Your Salary (Using UPDATE)

jarred.nicholls — Thu, 26 Nov 2009 15:27:52 GMT

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"

RE: 4 Ways to Increase Your Salary (Using UPDATE)

Jason-299789 — Thu, 26 Nov 2009 14:58:54 GMT

[quote][b]jarred.nicholls (11/26/2009)[/b][hr][quote][b]Jason Lees-299789 (11/26/2009)[/b][hr]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.[/quote]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.[/quote]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.

RE: 4 Ways to Increase Your Salary (Using UPDATE)

Jeff Moden — Thu, 26 Nov 2009 13:57:18 GMT

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.

RE: 4 Ways to Increase Your Salary (Using UPDATE)

djgubber — Thu, 26 Nov 2009 13:35:00 GMT

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.

RE: 4 Ways to Increase Your Salary (Using UPDATE)

jarred.nicholls — Thu, 26 Nov 2009 11:18:55 GMT

Despite the misinformed on operator precedence, just multiply by 1.15! Keep things DRY and simple :-)

RE: 4 Ways to Increase Your Salary (Using UPDATE)

Robert Davis — Thu, 26 Nov 2009 10:59:45 GMT

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)

RE: 4 Ways to Increase Your Salary (Using UPDATE)

nickblethyn — Thu, 26 Nov 2009 09:20:27 GMT

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 :(

RE: 4 Ways to Increase Your Salary (Using UPDATE)

djgubber — Thu, 26 Nov 2009 08:30:35 GMT

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."

RE: 4 Ways to Increase Your Salary (Using UPDATE)

John Lee-439997 — Thu, 26 Nov 2009 07:38:27 GMT

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

RE: 4 Ways to Increase Your Salary (Using UPDATE)

juancvaz — Thu, 26 Nov 2009 06:37:28 GMT

Hi All,I´m test for 24200 rows, the cursor done in 35 seconds and the tabla with identity in 84 seconds.But when i modify the WHILE control for an constant the final time is 35 seconds, equal to cursor.BEGIN CREATE TABLE #temp(id INT IDENTITY(1,1), name VARCHAR(32), salary float) INSERT INTO #temp SELECT name, salary FROM #emp DECLARE @i INT, [color="#ff0000"]@last INT[/color] [color="#ff0000"]SELECT @last = COUNT(id) FROM #temp [/color] SET @i = 1 WHILE (@i <= [color="#ff0000"]@last[/color] ) BEGIN UPDATE #emp SET salary = (salary * 115)/100 WHERE #emp.id = @i SET @i = @i + 1 END END

RE: 4 Ways to Increase Your Salary (Using UPDATE)

jarred.nicholls — Thu, 26 Nov 2009 06:22:29 GMT

[quote][b]Jason Lees-299789 (11/26/2009)[/b][hr]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.[/quote]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.

RE: 4 Ways to Increase Your Salary (Using UPDATE)

honza.mf — Thu, 26 Nov 2009 06:12:56 GMT

The last example uses assumption on the identity column values in the main table.You can just add these values to the temp table.But Martin's solution is better

RE: 4 Ways to Increase Your Salary (Using UPDATE)

Jochen Vleming — Thu, 26 Nov 2009 05:14:35 GMT

nice one Martin,That's a nice and easy way to do the updates. ...And independent of identity values in the table.

RE: 4 Ways to Increase Your Salary (Using UPDATE)

Jason-299789 — Thu, 26 Nov 2009 05:14:06 GMT

Martins answer is a lot sweeter and more robust if you need to loop through a data set, as there is no guarantee that the numbers are consecutive eg record number 4 is deleted for some reason (yes it can happen), so the last record will never get updated, assuming a 1% row deletion count, then in an organisation with 1000 members, 10 wont get pay rises. As always the advice is use set based queries rather than Loops and Cursors, though in some cases you have to revert to them they should be rare.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.

RE: 4 Ways to Increase Your Salary (Using UPDATE)

Martin Hill-258755 — Thu, 26 Nov 2009 04:50:12 GMT

There is actually another way to archive a row by row processing. I am using it in different scenarios since quite a few years and it turned out to be very efficient.[code="sql"]BEGINDeclare @ID intSelect @ID = Min(ID) from empWHILE @ID Is Not Null BEGIN UPDATE emp SET salary = (salary * 115) /100 WHERE emp.id = @ID Select @ID = Min(ID) from emp where ID > @ID END END[/code]In relation to the actual performance of this method I listed the times/cost on my server below. Please note that I also included the cost calculated by the execution plan:[code="other"] READ ESTIMATED EXECUTION COST Direct SQL 5 0.0132935 100%Cursor 69 0.1462060 1099%Temp table with TOP 254 0.2531007 1904%Temp table with IDENTITY column 121 0.0994881 748%Min Loop 39 0.0861955 648%[/code]:-)

RE: 4 Ways to Increase Your Salary (Using UPDATE)

stuart.jeffery — Thu, 26 Nov 2009 03:45:33 GMT

I have to agree, there is no real difference with the calculations except in the interpretation by the system. Int divide by Int = Int which causes incorrect values. Simpler would be to change one of the Int's or even remove one altogether.What is wrong with using Salary * 1.15? After all Salary is a float.Remember the key to performance is simplicity. Every level of complexity will exponentially affect your query performance.

RE: 4 Ways to Increase Your Salary (Using UPDATE)

Kevin Gill — Thu, 26 Nov 2009 02:51:45 GMT

You say that salary * 115 / 100 and (salary * 115) / 100 return different results due to operator precedence - I can't see how operator precedence makes any difference in this scenario. Could you elaborate or provide an example?Basically :salary x (115/100) = (salary x 115)/100 so operator precedence should make no difference...Actually - thinking about it, is it the integer division that's the issue? What I say above is true in a pure mathematical sense, but if you divide 115/100 as integers in SQLServer, you get 1. If however you divide 115 as a decimal /100, then you get 1.150000, and the calculation will work in any layout. So technically it's the operator precedence causing the integer divide to happen first which is the issue I suppose... though it's the integer divide in its own right that means there is any confusion to be had in the first place.declare @percentage decimal(3,0)set @percentage = 115 -- Even works with an 'integer' decimal.select 3000 * @percentage / 100select (3000 * @percentage) / 100select 3000 * (@percentage / 100.0)Cheers-- Kev

RE: 4 Ways to Increase Your Salary (Using UPDATE)

Jochen Vleming — Thu, 26 Nov 2009 02:39:54 GMT

Hi Arup,I'm fairly new to SQL so maybe it's a stupid question, but why do you use a temp-table in the last example? I think there's no use for that since both the emp-table and the temp-table share the same identity. The way the query is built I think it wouldn't even work if the id's in the emp-table would be different (for example 1,2,5,6,7 (after delete)).Jochen

4 Ways to Increase Your Salary (Using UPDATE)

arup chakraborty — Wed, 25 Nov 2009 23:43:57 GMT

Comments posted to this topic are about the item [B]4 Ways to Increase Your Salary (Using UPDATE)[/B]