軟件公司的開發(fā)人員討論數(shù)據(jù)庫(kù)設(shè)計(jì)調(diào)優(yōu)的時(shí)候又討論到了表變量和臨時(shí)表的問(wèn)題，覺(jué)得這個(gè)問(wèn)題確實(shí)是一個(gè)爭(zhēng)議比較大的問(wèn)題。
其實(shí)從上次發(fā)表了表變量和臨時(shí)表的一個(gè)帖子http://database.ctocio.com.cn/tips/442/8206442.shtml以來(lái)，也有些人留言，也有些人發(fā)過(guò)郵件討論這個(gè)問(wèn)題。其實(shí)表變量和臨時(shí)表的區(qū)別雖然有一些，但是兩者最根本的區(qū)別還是在于
對(duì)存儲(chǔ)的需求：表變量和臨時(shí)表都消耗Tempdb中的存儲(chǔ)空間，但是進(jìn)行數(shù)據(jù)更新的時(shí)候，表變量不會(huì)寫日志，而臨時(shí)表則會(huì)寫日志。(這一點(diǎn)是經(jīng)過(guò)腳本測(cè)試的，表變量并不像我們想象的那樣，只寫在內(nèi)存而不出現(xiàn)在Tempdb中。)
對(duì)優(yōu)化的支持：表變量不支持索引和統(tǒng)計(jì)數(shù)據(jù)，臨時(shí)表則可以支持索引和統(tǒng)計(jì)數(shù)據(jù)。
通常需要表變量或者臨時(shí)表的情況都是一些需要支持臨時(shí)計(jì)算結(jié)果集的地方，那么就有一些常見的情況了：
如果臨時(shí)結(jié)果集僅僅需要往里面寫數(shù)據(jù)，比如通過(guò)一個(gè)循環(huán)多次查找相關(guān)數(shù)據(jù)并合成一個(gè)臨時(shí)結(jié)果集，那么就可以使用表變量。(結(jié)果有人提到了返回結(jié)果集的時(shí)候需要有排序，但是表變量不支持索引阿。其實(shí)這個(gè)不要緊，因?yàn)楸碜兞侩m然不支持索引，但是表變量支持主鍵阿，所以可以利用主鍵來(lái)替代索引。)
如果臨時(shí)結(jié)果集不太多需要更改，而是更多地充當(dāng)一個(gè)臨時(shí)的關(guān)聯(lián)數(shù)據(jù)集去參加各種數(shù)據(jù)集的連接(JOIN)，那么索引和統(tǒng)計(jì)數(shù)據(jù)可能會(huì)更加適合一些(當(dāng)然這個(gè)臨時(shí)結(jié)果集要足夠大，這樣索引和統(tǒng)計(jì)數(shù)據(jù)帶來(lái)的代價(jià)才可以被彌補(bǔ)掉)。
由于表變量不支持統(tǒng)計(jì)數(shù)據(jù)，因此在一個(gè)存儲(chǔ)過(guò)程中使用表變量可以減少由于數(shù)據(jù)變化而導(dǎo)致的重新編譯問(wèn)題。
當(dāng)然，除了索引和統(tǒng)計(jì)數(shù)據(jù)這個(gè)明顯的限制外，表變量同時(shí)也不支持并行執(zhí)行計(jì)劃，因此對(duì)于大型的臨時(shí)結(jié)果集，表變量也不是一個(gè)好的選擇。
前面一個(gè)關(guān)于表變量和臨時(shí)表的貼子，有一位robi_xu的朋友提到的問(wèn)題也確實(shí)是在選擇表變量和臨時(shí)表時(shí)候的一些問(wèn)題。
對(duì)于函數(shù)中不能支持臨時(shí)表是由于函數(shù)不能對(duì)函數(shù)作用域外部的資源狀態(tài)造成永久性的更改，在SQL Server中也稱為副作用(side effect)。不過(guò)如果在函數(shù)中使用大型的臨時(shí)結(jié)果集是不推薦的，因?yàn)槿绻麑⑦@樣的函數(shù)放置到一個(gè)查詢中會(huì)造成很明顯的性能問(wèn)題，因此這種情況一般都采用存儲(chǔ)過(guò)程之類的批處理腳本。
對(duì)于動(dòng)態(tài)腳本不支持表變量的原因是因?yàn)榇鎯?chǔ)過(guò)程不接受表類型的參數(shù)。不過(guò)如果表變量的聲明和賦值都在sp_executesql的參數(shù)中的話，sp_executesql就可以執(zhí)行了，因?yàn)檫@個(gè)時(shí)候表變量就存在sp_executesql的stmt參數(shù)里面，不需要傳入，例如下面的代碼：(當(dāng)然這樣的實(shí)用性也就沒(méi)有多少了)
DECLARE @m nvarchar(max)
SET @m = N’DECLARE @t TABLE (ID int);INSERT INTO @t VALUES (1);SELECT * FROM @t T’
EXEC sp_executesql @m
其實(shí)從上次發(fā)表了表變量和臨時(shí)表的一個(gè)帖子http://database.ctocio.com.cn/tips/442/8206442.shtml以來(lái)，也有些人留言，也有些人發(fā)過(guò)郵件討論這個(gè)問(wèn)題。其實(shí)表變量和臨時(shí)表的區(qū)別雖然有一些，但是兩者最根本的區(qū)別還是在于
對(duì)存儲(chǔ)的需求：表變量和臨時(shí)表都消耗Tempdb中的存儲(chǔ)空間，但是進(jìn)行數(shù)據(jù)更新的時(shí)候，表變量不會(huì)寫日志，而臨時(shí)表則會(huì)寫日志。(這一點(diǎn)是經(jīng)過(guò)腳本測(cè)試的，表變量并不像我們想象的那樣，只寫在內(nèi)存而不出現(xiàn)在Tempdb中。)
對(duì)優(yōu)化的支持：表變量不支持索引和統(tǒng)計(jì)數(shù)據(jù)，臨時(shí)表則可以支持索引和統(tǒng)計(jì)數(shù)據(jù)。
通常需要表變量或者臨時(shí)表的情況都是一些需要支持臨時(shí)計(jì)算結(jié)果集的地方，那么就有一些常見的情況了：
如果臨時(shí)結(jié)果集僅僅需要往里面寫數(shù)據(jù)，比如通過(guò)一個(gè)循環(huán)多次查找相關(guān)數(shù)據(jù)并合成一個(gè)臨時(shí)結(jié)果集，那么就可以使用表變量。(結(jié)果有人提到了返回結(jié)果集的時(shí)候需要有排序，但是表變量不支持索引阿。其實(shí)這個(gè)不要緊，因?yàn)楸碜兞侩m然不支持索引，但是表變量支持主鍵阿，所以可以利用主鍵來(lái)替代索引。)
如果臨時(shí)結(jié)果集不太多需要更改，而是更多地充當(dāng)一個(gè)臨時(shí)的關(guān)聯(lián)數(shù)據(jù)集去參加各種數(shù)據(jù)集的連接(JOIN)，那么索引和統(tǒng)計(jì)數(shù)據(jù)可能會(huì)更加適合一些(當(dāng)然這個(gè)臨時(shí)結(jié)果集要足夠大，這樣索引和統(tǒng)計(jì)數(shù)據(jù)帶來(lái)的代價(jià)才可以被彌補(bǔ)掉)。
由于表變量不支持統(tǒng)計(jì)數(shù)據(jù)，因此在一個(gè)存儲(chǔ)過(guò)程中使用表變量可以減少由于數(shù)據(jù)變化而導(dǎo)致的重新編譯問(wèn)題。
當(dāng)然，除了索引和統(tǒng)計(jì)數(shù)據(jù)這個(gè)明顯的限制外，表變量同時(shí)也不支持并行執(zhí)行計(jì)劃，因此對(duì)于大型的臨時(shí)結(jié)果集，表變量也不是一個(gè)好的選擇。
前面一個(gè)關(guān)于表變量和臨時(shí)表的貼子，有一位robi_xu的朋友提到的問(wèn)題也確實(shí)是在選擇表變量和臨時(shí)表時(shí)候的一些問(wèn)題。
對(duì)于函數(shù)中不能支持臨時(shí)表是由于函數(shù)不能對(duì)函數(shù)作用域外部的資源狀態(tài)造成永久性的更改，在SQL Server中也稱為副作用(side effect)。不過(guò)如果在函數(shù)中使用大型的臨時(shí)結(jié)果集是不推薦的，因?yàn)槿绻麑⑦@樣的函數(shù)放置到一個(gè)查詢中會(huì)造成很明顯的性能問(wèn)題，因此這種情況一般都采用存儲(chǔ)過(guò)程之類的批處理腳本。
對(duì)于動(dòng)態(tài)腳本不支持表變量的原因是因?yàn)榇鎯?chǔ)過(guò)程不接受表類型的參數(shù)。不過(guò)如果表變量的聲明和賦值都在sp_executesql的參數(shù)中的話，sp_executesql就可以執(zhí)行了，因?yàn)檫@個(gè)時(shí)候表變量就存在sp_executesql的stmt參數(shù)里面，不需要傳入，例如下面的代碼：(當(dāng)然這樣的實(shí)用性也就沒(méi)有多少了)
DECLARE @m nvarchar(max)
SET @m = N’DECLARE @t TABLE (ID int);INSERT INTO @t VALUES (1);SELECT * FROM @t T’
EXEC sp_executesql @m