Using SQL Server Table Variables to Eliminate the Need for Cursors

Today, I encountered a tricky problem about performance.
There is one table which is used to store both kinds of members including the Company Member(with RECORD_TYPE 'C') and the Individual Member(with RECORD_TYPE 'I').
When it comes to an individual one, it has a specific field 'COMPANY_ID', which stores the MB_ID as its company ID when it belongs to a company. The field can also be NULL. By comparison, a company one has a specific field 'MB_COUNT', which stores the total count of individual members whose COMPANY_ID equals the company member's MB_ID.
Now, we need to write a procedure to calculate the MB_COUNT of each Company Records. It will show how many individuals the company has.
The below pic show the original data with 'MB_COUNT' NULL:
The below picture shows the result data with 'MB_COUNT' calculated:

Preparation

You can create your own testing database, then run the SQL-Scripts stored in the ZIP file (or run the below 1-5 scripts manually) to create the testing table and dynamic testing data.

1. Create your testing DB:
   
   

   IF EXISTS(SELECT 1 FROM MASTER..SYSDATABASES WHERE NAME='TEST_DB1')
    DROP DATABASE TEST_DB1
   GO
   CREATE DATABASE TEST_DB1
   GO
   USE TEST_DB1
   GO

2. Drop Table MEMBERS if it exists:
   
   

   IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = _
    object_id(N'MEMBERS') and OBJECTPROPERTY(id, N'IsUserTable') = 1)  
    DROP TABLE MEMBERS

3. Create Table MEMBERS:
   
   

   CREATE TABLE MEMBERS(
    MB_ID INT PRIMARY KEY,
    MEMBER_NAME NVARCHAR(100),
    RECORD_TYPE CHAR(1),
    COMPANY_ID INT,
    MB_COUNT INT
   )

4. Insert Company Data with MB_ID from 1001 to 6000:
   
   

   DECLARE @num INT
   SET @num = 1
   
   WHILE(@num <= 5000)
   BEGIN
    INSERT INTO MEMBERS(MB_ID, MEMBER_NAME, RECORD_TYPE, _
     COMPANY_ID, MB_COUNT)VALUES(1000+@num, 'Company' + _
     convert(varchar, @num), 'C', NULL, NULL);
    SET @num = @num + 1
   END

5. Insert Individual Data with MB_ID from 10001 to 40000, utilize RAND() method to create random COMPANYID from 1001 to 6000.:
   
   

   SET @num = 1
   
   WHILE(@num <= 30000)
   BEGIN
    INSERT INTO MEMBERS(MB_ID, MEMBER_NAME, RECORD_TYPE, _
     COMPANY_ID, MB_COUNT)VALUES(10000+@num, 'Individual' + _
     convert(varchar, @num), 'I', 1001 + FLOOR(RAND()*5000), NULL);
    SET @num = @num + 1
   END

Solution

Actually, I have two totally different ways to achieve the requirement. Use 'cursor' or 'table variable'.
SOLUTION ONE--Use 'cursor' to loop records the whole table when the record stands for company:

DECLARE @ID INT,
  @MemberCount INT

DECLARE CUR1 CURSOR FOR
 SELECT MB_ID FROM MEMBERS 
 WHERE RECORD_TYPE = 'C'

OPEN CUR1
     FETCH CUR1 INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
 SELECT @MemberCount = COUNT(1)FROM MEMBERS 
      WHERE MEMBERS.COMPANY_ID = @ID
 UPDATE MEMBERS
      SET MB_COUNT=@MemberCount
      WHERE MEMBERS.MB_ID = @ID
 
 FETCH NEXT FROM CUR1 INTO @ID
END
Close CUR1
DEALLOCATE CUR1

Solution One's Result:
Initialize the MB_COUNT, use it to initialize the MB_COUNT to NULL.

UPDATE MEMBERS SET MB_COUNT = NULL

SOLUTION TWO--Use 'table variable' to solve this issue:

DECLARE @TEMP_TABLE TABLE (COMPANY_MB_ID INT, MB_COUNT INT)

INSERT INTO 
 @TEMP_TABLE 
SELECT 
 COMPANY_ID, count(MB_ID) AS MB_COUNT FROM MEMBERS
WHERE 
 RECORD_TYPE = 'I' AND COMPANY_ID IS NOT NULL
GROUP BY COMPANY_ID
ORDER BY COMPANY_ID ASC

UPDATE 
 MEMBERS
SET 
 MB_COUNT=(SELECT T.MB_COUNT FROM @TEMP_TABLE T _
  WHERE MEMBERS.MB_ID = T.COMPANY_MB_ID)
WHERE 
 MEMBERS.MB_ID IN (SELECT T2.COMPANY_MB_ID FROM @TEMP_TABLE T2)

Solution Two's Result:

Conclusion

Now we come to a conclusion: Don't ever use cursors in your SQL statement unless you are DBAs. For cursors will lock tables and they would affect the performance of the whole system. Beginners may feel comfortable with using cursors without concerning its poor performance. Let me say it again: DON'T use cursors. Try to use Table Variables!
Remember: Almost everything that you may first envision as requiring cursors to achieve can actually be done using the new SQL Server TABLE type. Let’s discard cursors and meet the challenge!