Published on

November 3, 2009

SQL Server Concepts: Updating Employee Salaries

Good news! Our company has decided to increase employee salaries by 15%. As a database developer, it’s my responsibility to apply this increment to the employee table in our SQL Server database.

Let’s start by looking at the structure of our employee table. For the sake of simplicity, I have taken only three columns: id, name, and salary. Here’s the code to create the table:

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

Now, let’s populate the table with some sample data:

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)

To update the salaries, we can use a simple SQL statement:

UPDATE emp SET salary = (salary * 115)/100

This statement will update the salary field of all employees by increasing it by 15%. It’s a straightforward and efficient way to implement the change.

However, there may be situations where we can’t directly apply the UPDATE statement. In such cases, we need to access the rows of the table in a different manner. Let’s explore a couple of methods:

Method 1: Using a Cursor

The first method is to use a cursor. A cursor is a built-in functionality in SQL Server that allows us to operate on data row by row. Here’s 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

In this example, we declare a cursor named “myCursor” and fetch all the rows from the emp table into it. We then use a WHILE loop to iterate through each row and perform the UPDATE operation using the @id variable, which holds the employee id. Finally, we close and deallocate the cursor to release the memory.

Method 2: Using a Temporary Table and the TOP Keyword

The second method involves using a temporary table and the TOP keyword to access all the rows. Here’s an example:

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

In this example, we create a temporary table named “#temp” and populate it with all the data from the emp table. We then use a WHILE loop to iterate through each row, updating the salary in the emp table and deleting the corresponding row from the temporary table. This method can be effective, but excessive use of DELETE and subqueries may degrade performance.

Method 3: Using a Temporary Table with an IDENTITY Column

The third method also involves using a temporary table and a WHILE loop, but with a slight difference. In this case, the temporary table has an IDENTITY column that allows us to easily identify and update each row. Here’s an example:

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

In this example, we create a temporary table named “#temp” with an IDENTITY column. We insert the data from the emp table into the temporary table and then use a WHILE loop to iterate through each row, updating the salary in the emp table based on the IDENTITY column. This method can provide better performance compared to the cursor or the “temporary table with TOP” approach.

When it comes to performance, using direct SQL statements is always the best choice. Direct SQL is a set-based approach that manipulates data as a chunk, minimizing both the read and duration values. Cursors, while they may be necessary in certain situations, are generally not recommended due to their impact on performance. The use of a temporary table with an IDENTITY column can provide better performance compared to the other methods.

Now that you know these methods, you can confidently calculate and update the increased salaries for your employees. Enjoy the benefits of the salary increment and smile, knowing that the recession is over!

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.