閱讀Using SQL Server Table Variables to Eliminate the Need for Cursors感想

2010-01-18

5672
0
SQL
2010-01-20

今天在CodeProject上看到了Using SQL Server Table Variables to Eliminate the Need for Cursors，他是說將資料整理好後放到Table Variables裡去，然後再去Update資料。除非必要，否則不要使用Cursor。

不過，感覺直接透過SubQuery來Update就可以了說!

建立測試資料如下，


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

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

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

他Update的Code如下，



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)

使用SubQuery的Code如下，


SET MB_COUNT = (SELECT COUNT(*) FROM MEMBERS2 B (NOLOCK) 
WHERE B.RECORD_TYPE = 'I'  AND B.COMPANY_ID = A.MB_ID )
FROM MEMBERS2 A
WHERE A.RECORD_TYPE = 'C'

另外，要注意的是，使用Table變數和使用Temporary Table效果是相同的，但是Table變數與Temp Table是不同的哦!

Table變數可以用在Store Procedures, User-Defined functions! 雖然它是變數，但是它無法當作是SP的參數傳遞，所以當您需要某個在SP間Share資料時，就需要使用temporary Table來達成。雖然它的內容是Table，但是在不用時，您不需將它Drop掉。也可以建立PK及一些欄位的限制。但是您無法針對它去建立non-clustered index(所以如果您的資料量很大，需要建立index去增加效能的話，那最好改用temporary table)，或是之後去alter它。另外，值得注意的是，Table變數是不會參與Transaction的哦! 所以如果您在下insert前先建立Transaction之後再去Rollback的話，資料還是會在哦!

DECLARE @MyTable TABLE
(
  ProductID int UNIQUE,
  Price money CHECK(Price < 10.0)
)
DECLARE @MyTable TABLE
(
  ProductID int IDENTITY(1,1) PRIMARY KEY,
  Name varchar(10) NOT NULL DEFAULT('Unknown')
)

如果資料量是少的，就很適合用Table變數哦! 像常常在畫面上會有checkboxlist，裡面的值是複選的，到DB時，可以將它將該值Split成Table再去Join。如下(from Table Variables In T-SQL)。