Avoiding Cursors with Superclass-Subclass Tables
Ever since I read Itzik Ben-Gan's article in SQL Server Magazine about changing cursor-based logic to set-based logic, I've avidly sought to avoiding using cursors in my SQL procedures. One of the biggest problems that I've faced on this front is the issue of inserting records into a superclass-subclass table structure. Here's an example of the table structure that I'm talking about:
1 // Give every employee a bonus!
2 DECLARE @curEmployees CURSOR
3
4 SET @curEmployees = CURSOR
5 FOR
6 SELECT EmployeeID, BonusAmount
7 FROM tblEmployee
8
9 DECLARE @employeeID int
10 DECLARE @bonusAmount money
11
12 OPEN @curEmployees
13
14 FETCH NEXT FROM @curEmployees INTO @employeeID, @bonusAmount
15
16 WHILE @@FETCH_STATUS = 0
17 BEGIN
18 DECLARE @transactionID int
19
20 INSERT INTO
21 tblTransaction (TransactionClass, [Date], Amount)
22 VALUES
23 ('PR', '11/21/2005', @bonusAmount)
24
25 SET @transactionID = @@IDENTITY
26
27 INSERT INTO
28 tblPayrollTransaction (TransactionID, TransactionClass, EmployeeID)
29 VALUES
30 (@transactionID, 'PR', @employeeID)
31
32 FETCH NEXT FROM @curEmployees INTO @employeeID, @bonusAmount
33 END
34
35 CLOSE @curEmployees
36
After writing blocks of code like this about twenty times, I finally thought up a solution that avoids cursors. To be honest, I haven't done exhaustive performance testing on the solution or anything, but I did notice performance improvements with my new method. Check it out:
// Give every employee a bonus more quickly!
DECLARE @tblEmployee TABLE
(
EmployeeID int PRIMARY KEY,
BonusAmount money,
RowNum int IDENTITY(0, -1)
)
INSERT INTO
@tblEmployee (EmployeeID, BonusAmount)
SELECT
EmployeeID, BonusAmount
FROM
tblEmployee
INSERT INTO
tblTransaction (TransactionClass, [Date], Amount)
SELECT
'PR', '11/21/2005', BonusAmount
FROM
@tblEmployee
ORDER BY
RowNum
INSERT INTO
tblPayrollTransaction (TransactionID, TransactionClass, EmployeeID)
SELECT
@@IDENTITY + RowNum, 'PR', EmployeeID
FROM
@tblEmployee
ORDER BY
RowNum
The critical elements of the new code are as follows:
- The table variable must contain the most granular instance of an item to be inserted. In this example, each employee has a single transaction, so employees are stored in the table variable.
- The table variable must have an identity column that starts at zero and counts backwards. (The assumption here is the TransactionID on tblTransaction is an IDENTITY(1,1) column.)
- Both insert statements must have identical ORDER BY clauses that include the RowNum column.
If all of these assumptions hold true, then the two insert statements should "line up" and cause each superclass record to be properly joined to the corresponding subclass record.