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!