Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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 emp
SET 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..

BEGIN
DECLARE @id INT
DECLARE myCursor CURSOR
FOR
SELECT id FROM emp
OPEN myCursor
FETCH NEXT FROM myCursor
INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE emp
SET salary = (salary * 115)/100
WHERE emp.id = @id
FETCH NEXT FROM myCursor INTO @id
END
CLOSE myCursor
DEALLOCATE myCursor
END

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.

BEGIN
SELECT id, name, salary INTO #temp FROM emp
DECLARE @i INT, @count INT
SET @i = 1
SELECT @count = COUNT(id) FROM #temp
WHILE (@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
END
END

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

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
SET @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.

 
  CPU READ WRITE DURATION
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: 7919 | Views in the last 30 days: 3
 
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

cursor

FORUM

update cursor

error in update cursor

FORUM

Cursors

How to Update using Cursors?

FORUM

Average Salary

Average Salary

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones