# 4 Ways to Increase Your Salary (Using UPDATE)

By Arup Chakraborty,

Hi, a good news. Instead of being in a recession, our company has decided to increase the employee salaries by 15 %. That's amazing, in this market, to get 15%!!!!!. As a database developer, I need to apply this incremental for the employee table. In my company, we have the traditional emp table, from which for the shake of simplicity, I have taken only 3 columns. The code for the structure of the table is like this:

```CREATE TABLE emp(id INT IDENTITY(1,1), name VARCHAR(32), salary float)
```

Our company is very small, hence I have populated the data of all five employees below.

```INSERT INTO emp VALUES ('Tom', 1000.00)INSERT INTO emp VALUES ('Dick', 2000.00)INSERT INTO emp VALUES ('Harry', 3000.00)INSERT INTO emp VALUES ('Mery', 4000.00)INSERT INTO emp VALUES ('Zen', 5000.00)
```

Now its the time to update the salaries. All of us know that SQL is based on set theory, hence we can directly apply the following SQL statement.

```UPDATE empSET salary = (salary * 115)/100
```

NOTE: Don't use salary * 115 / 100. This violates the operator precedence rule and gives the wrong result. Please refer to the following link for further information: http://msdn.microsoft.com/en-us/library/ms190276.aspx

This updates the salary fields of all employees by 15%. This is the most efficient and easy way to implement the change. That's why SQL is called "SO EASY" :)

But imagine some situation, where we can not directly apply the UPDATE statement. Maybe we need to proceed row by row for this particular situation and apply the changes. Maybe to update each row we need to call a stored procedure. Obviously that situation is not so easy, but I will explain different methods to access the rows of a table through this simple example.

The first and foremost method is using a cursor. The cursor functionality is built into SQL Server. It reserves some space in database memory, puts all the data there and then operates on that data row by row. Here is an example..

```BEGINDECLARE @id INTDECLARE myCursor CURSORFORSELECT id FROM empOPEN myCursor    FETCH NEXT FROM myCursor    INTO @idWHILE @@FETCH_STATUS = 0    BEGIN      UPDATE emp      SET salary = (salary * 115)/100      WHERE emp.id = @id       FETCH NEXT FROM myCursor INTO @id    ENDCLOSE myCursorDEALLOCATE myCursorEND
```

Here I have reserved space in memory with the name myCursor and also put all data in the cursor through the SELECT statement. Then I have opened the cursor, fetched all the rows one by one with the help of a WHILE loop and performed the UPDATE operation using the @id variable, which holds the employee id's. At the last, I have closed and deallocated the cursor to release the memory (optional, but a good practice).

The next method is using a temporary table and the TOP keyword to access all rows.

```BEGINSELECT id, name, salary INTO #temp FROM empDECLARE @i INT, @count INTSET @i = 1SELECT @count = COUNT(id) FROM #tempWHILE (@i <= @count)  BEGIN    UPDATE emp    SET salary = (salary * 115)/100    WHERE emp.id = (SELECT TOP 1 id from #temp)    DELETE #temp     WHERE #temp.id = (SELECT TOP 1 id from #temp)    SET @i = @i + 1  ENDEND
```

Here, all data of emp table have been put in the temporary table (#temp). Then I have accessed the first row using TOP keyword. After applying the UPDATE on the emp table, I have deleted the first row. So next time the second row would come as the top row. I have taken help of a WHILE loop to iterate in such a fashon.

This method is pretty good, but the only thing is the excessive use of DELETE may degrade the performance (I hope all tables may not have only 5 rows.). Also, use of subqueries may degrade performance.

Here is the last method. Here also I have used a temporary table and a WHILE loop. The difference is here the temp table has an IDENTITY column. I have mapped each and every row with this identity column (id) and the looping variable (@i). Once they have been mapped, I can easily identify each and every row and do the UPDATE operation on it. (We need to ensure that both identity column and the looping variable should increament in the same manner like 1 and 1, or 2 and 2 or so on.)

```BEGINCREATE TABLE #temp(id INT IDENTITY(1,1), name VARCHAR(32), salary float)INSERT INTO #tempSELECT name, salary FROM emp DECLARE @i INTSET @i = 1 WHILE (@i <= (SELECT COUNT(id) FROM #temp))  BEGIN    UPDATE emp    SET salary = (salary * 115)/100    WHERE emp.id = @i    SET @i = @i + 1  END END
```

Now we come to the point of performance. Here I have depicted the SQL Server profiler data in tabular format.

Direct SQL 15 5 0 15
Cursor 15 93 2 35
Temp table with TOP 0 236 0 61
Temp table with IDENTITY column 0 142 0 18

(To know how to build this table for performance testing, please go through my previous article:
http://www.sqlservercentral.com/articles/Stored+Procedures/64523/ )

So, we can conclude that using direct SQL can always give the best performance. This is because it's a set based approach and the data is manipulated not individually but as a chunk. Direct SQL minimizes both the READ and DURATION values. The cursor, from the view of performance, I think is NOT a good choice, though it gives better performance than the temp table with TOP method. I do agree and prescribe the use of a temp table with an IDENTITY column for any non set based approach. For real time also, it gives better performance than the cursor or the "temp table with TOP" approach.

Whats more! Now you know the methods to calculate the increased salary. So don't wait. Just get the increased salary and smile. After all, the recession is OVER :D

Total article views: 7937 | Views in the last 30 days: 1

Related Articles
FORUM

### Update salary details without cursors

Hi i want to write the while loop without cursors and update salary of emp if salary is less than 50...

FORUM

cursor

FORUM

### update cursor

error in update cursor

FORUM

### Cursors

How to Update using Cursors?

FORUM

### Updating Values Without Cursor

Updating Values Without Cursor in a table

Tags
 alternative to cursors cursors while loop