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.

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