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

  • 5340
  • 0
  • SQL
  • 2010-01-20

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

今天在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)。

 

fn_Split

使用方式:

SELECT * FROM fn_Split('foo,bar,widget', ',')

參考資料:

Temp Tables vs Table Variables

Table Variables In T-SQL

 

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活

請大家繼續支持 ^_^