SQL Prompt是一款實(shí)用的SQL語(yǔ)法提示工具。SQL Prompt根據(jù)數(shù)據(jù)庫(kù)的對(duì)象名稱、語(yǔ)法和代碼片段自動(dòng)進(jìn)行檢索，為用戶提供合適的代碼選擇。自動(dòng)腳本設(shè)置使代碼簡(jiǎn)單易讀--當(dāng)開(kāi)發(fā)者不大熟悉腳本時(shí)尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶還可根據(jù)需要進(jìn)行自定義，使之以預(yù)想的方式工作。

點(diǎn)擊下載SQL Prompt正式版

人們可以并且確實(shí)對(duì)表變量和臨時(shí)表的相對(duì)優(yōu)點(diǎn)爭(zhēng)論不休。有時(shí)，就像編寫(xiě)函數(shù)時(shí)一樣，您別無(wú)選擇。但是當(dāng)您這樣做時(shí)，您會(huì)發(fā)現(xiàn)兩者都有其用途，并且很容易找到其中一個(gè)速度更快的示例。在本文中，我將解釋選擇一個(gè)或另一個(gè)涉及的主要因素，并演示一些簡(jiǎn)單的“規(guī)則”以獲得最佳性能。

假設(shè)您遵循基本的接觸規(guī)則，那么在使用相對(duì)較小的數(shù)據(jù)集時(shí)，應(yīng)將表變量視為首選。與使用臨時(shí)表相比，它們更易于使用，并且在使用它們的例程中觸發(fā)更少的重新編譯。表變量還需要較少的鎖定資源，因?yàn)樗鼈兪莿?chuàng)建它們的過(guò)程和批處理的“專用”資源。SQL Prompt將此建議作為代碼分析規(guī)則ST011實(shí)施-考慮使用表變量而不是臨時(shí)表。

如果您要對(duì)臨時(shí)數(shù)據(jù)進(jìn)行更復(fù)雜的處理，或者需要使用其中的少量數(shù)據(jù)，那么本地臨時(shí)表可能是一個(gè)更好的選擇。根據(jù)他的建議，SQL Code Guard包含一個(gè)代碼分析規(guī)則ST012 –考慮使用臨時(shí)表代替表變量，但是SQL Prompt當(dāng)前未實(shí)現(xiàn)。

表變量和臨時(shí)表的優(yōu)缺點(diǎn)

表變量趨向于“糟糕”，因?yàn)槭褂盟鼈兊牟樵兣紶枙?huì)導(dǎo)致執(zhí)行計(jì)劃效率低下。但是，如果遵循一些簡(jiǎn)單的規(guī)則，它們對(duì)于中間“工作”表以及在例程之間傳遞結(jié)果是個(gè)不錯(cuò)的選擇，在常規(guī)例程中數(shù)據(jù)集很小，所需的處理相對(duì)簡(jiǎn)單。

表變量的使用非常簡(jiǎn)單，主要是因?yàn)樗鼈兪恰傲憔S護(hù)”。它們的作用域僅限于創(chuàng)建它們的批處理或例程，一旦完成執(zhí)行便會(huì)自動(dòng)刪除，因此在長(zhǎng)期存在的連接中使用它們不會(huì)冒著tempdb中“資源占用”問(wèn)題的風(fēng)險(xiǎn)。如果在存儲(chǔ)過(guò)程中聲明了表變量，則該表變量是該存儲(chǔ)過(guò)程的本地變量，并且不能在嵌套過(guò)程中引用。表變量也沒(méi)有基于統(tǒng)計(jì)信息的重新編譯，因此您不能ALTER一種，因此使用它們的例程比使用臨時(shí)表的例程傾向于更少的重新編譯。它們也沒(méi)有完全記錄，因此創(chuàng)建和填充它們的速度更快，并且在事務(wù)日志中需要的空間更少。在存儲(chǔ)過(guò)程中使用它們時(shí)，在高并發(fā)條件下，系統(tǒng)表上的爭(zhēng)用較少。簡(jiǎn)而言之，保持事物整潔更容易。

當(dāng)使用相對(duì)較小的數(shù)據(jù)集時(shí)，它們比類似的臨時(shí)表快。但是，隨著行數(shù)的增加（超過(guò)大約15,000行，但根據(jù)上下文而變化），您可能會(huì)遇到困難，這主要是由于它們?nèi)狈?duì)統(tǒng)計(jì)的支持。即使對(duì)表變量強(qiáng)制執(zhí)行PRIMARY KEY和UNIQUE約束的索引也沒(méi)有統(tǒng)計(jì)信息。因此，優(yōu)化器將使用從表變量返回的1行的硬編碼估計(jì)，因此傾向于選擇最適合處理小型數(shù)據(jù)集（例如嵌套循環(huán)）的運(yùn)算符。聯(lián)接運(yùn)算符）。表變量中的行越多，估計(jì)與實(shí)際之間的差異就越大，并且成為優(yōu)化器計(jì)劃選擇的效率越低。最終的計(jì)劃有時(shí)是可怕的。

有經(jīng)驗(yàn)的開(kāi)發(fā)人員或DBA會(huì)尋找此類問(wèn)題，并準(zhǔn)備將OPTION (RECOMPILE)查詢提示添加到使用表變量的語(yǔ)句中。當(dāng)我們提交包含表變量的批處理時(shí)，優(yōu)化器將首先編譯該批處理，此時(shí)表變量為空。當(dāng)批處理開(kāi)始執(zhí)行時(shí)，提示將僅導(dǎo)致重新編譯單個(gè)語(yǔ)句，此時(shí)將填充表變量，優(yōu)化器可以使用實(shí)際行數(shù)為該語(yǔ)句編譯新的計(jì)劃。有時(shí)，但很少，即使這樣也無(wú)濟(jì)于事。同樣，過(guò)度依賴此提示將在某種程度上抵消表變量比臨時(shí)表具有更少的重新編譯優(yōu)勢(shì)。

其次，在處理大型數(shù)據(jù)集時(shí)，表變量的某些索引限制變得更加重要。現(xiàn)在，您可以使用內(nèi)聯(lián)索引創(chuàng)建語(yǔ)法在表變量上創(chuàng)建非聚集索引，但是存在一些限制，并且仍然沒(méi)有關(guān)聯(lián)的統(tǒng)計(jì)信息。

即使行數(shù)相對(duì)較少，但如果您嘗試執(zhí)行作為聯(lián)接的查詢，也可能會(huì)遇到查詢性能問(wèn)題，而忘記了在用于聯(lián)接的列上定義PRIMARY KEY或UNIQUE約束。沒(méi)有它們提供的元數(shù)據(jù)，優(yōu)化器將不知道數(shù)據(jù)的邏輯順序，也不知道聯(lián)接列中的數(shù)據(jù)是否包含重復(fù)值，并且可能會(huì)選擇效率低下的聯(lián)接操作，從而導(dǎo)致查詢緩慢。如果使用表變量堆，則只能使用一個(gè)簡(jiǎn)單列表，該列表很可能在單個(gè)gulp中處理（表掃描）。如果您同時(shí)使用OPTION (RECOMPILE) 提示，準(zhǔn)確的基數(shù)估計(jì)以及連接列上的鍵（可為優(yōu)化器提供有用的元數(shù)據(jù)），然后對(duì)于較小的數(shù)據(jù)集，您通常可以達(dá)到與使用本地臨時(shí)表相似或更好的查詢速度。

一旦行數(shù)增加到表變量的允許范圍之外，或者您需要執(zhí)行更復(fù)雜的數(shù)據(jù)處理，那么最好切換為使用臨時(shí)表。在這里，您可以使用完整的選項(xiàng)來(lái)建立索引，并且優(yōu)化器將可以為每個(gè)索引使用統(tǒng)計(jì)信息。當(dāng)然，缺點(diǎn)是臨時(shí)表的維護(hù)成本較高。您需要確保自己清理一下，以避免tempdb擁塞。如果更改臨時(shí)表或修改臨時(shí)表中的數(shù)據(jù)，則可能會(huì)導(dǎo)致父例程的重新編譯。

當(dāng)需要大量刪除和插入（行集共享）時(shí)，臨時(shí)表會(huì)更好。如果必須從表中完全刪除數(shù)據(jù)，尤其是這樣，因?yàn)橹挥信R時(shí)表支持截?cái)?。如果?shù)據(jù)易變，則表變量設(shè)計(jì)中的折衷辦法（例如缺乏統(tǒng)計(jì)信息和重新編譯）會(huì)不利于它們。

何時(shí)需要使用表變量

我們將從一個(gè)表變量理想的示例開(kāi)始，它可以帶來(lái)更好的性能。我們將列出Adventureworks的員工列表，他們工作的部門以及工作班次。我們正在處理一個(gè)小的數(shù)據(jù)集（291行）。

USE AdventureWorks2016;
--initialise out timer
DECLARE @log TABLE (TheOrder INT IDENTITY(1,1), 
WhatHappened varchar(200), WHENItDid  Datetime2 DEFAULT GETDATE())
 
 
CREATE TABLE #employees
  (Employee NATIONAL CHARACTER VARYING(500) NOT NULL);
----start of timing
INSERT INTO @log(WhatHappened) 
SELECT 'Starting My_Section_of_code'--place at the start
 
--start by using a table variable for workpad
DECLARE @WorkPad TABLE
  (NameOfEmployee NATIONAL CHARACTER VARYING(100) NOT NULL,
BusinessEntityID INT PRIMARY KEY NOT NULL,
NationalIDNumber NATIONAL CHARACTER VARYING(15) NOT NULL);
 
INSERT INTO @WorkPad
  (NameOfEmployee, BusinessEntityID, NationalIDNumber)
  SELECT Coalesce(Person.Title + ' ', '') + Person.FirstName + ' '
         + Coalesce(Person.MiddleName + ' ', '') + Person.LastName
         + ': ' + Coalesce(Person.Suffix, '') + Employee.JobTitle,
    Employee.BusinessEntityID, Employee.NationalIDNumber
    FROM HumanResources.Employee
      INNER JOIN Person.Person
        ON Person.BusinessEntityID = Employee.BusinessEntityID;
 
INSERT INTO #Employees(Employee)
  SELECT TheList.NameOfEmployee + ' - '
         + Coalesce(
             Stuff(
               (SELECT ', ' + Department.Name + ' ('
                       + Department.GroupName + ') '
                       + Convert(CHAR(5), Shift.StartTime) + ' to '
                       + Convert(CHAR(5), Shift.EndTime)
                  FROM HumanResources.EmployeeDepartmentHistory
                    INNER JOIN HumanResources.Department
                      ON Department.DepartmentID = EmployeeDepartmentHistory.DepartmentID
                    INNER JOIN HumanResources.Shift
                      ON Shift.ShiftID = EmployeeDepartmentHistory.ShiftID
                  WHERE EmployeeDepartmentHistory.BusinessEntityID =
                            TheList.BusinessEntityID
               FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),  1,2,''),'?') 
                            AS Department
    FROM @WorkPad TheList;
INSERT INTO @log(WhatHappened) SELECT 'The use of a Table Variable took '--where the routine you want to time ends
 
--now use a temp table for workpad instead
CREATE TABLE #WorkPad
  (NameOfEmployee NATIONAL CHARACTER VARYING(100) NOT NULL,
BusinessEntityID INT PRIMARY KEY NOT NULL,
NationalIDNumber NATIONAL CHARACTER VARYING(15) NOT NULL);
 
INSERT INTO #WorkPad
  (NameOfEmployee, BusinessEntityID, NationalIDNumber)
  SELECT Coalesce(Person.Title + ' ', '') + Person.FirstName + ' '
         + Coalesce(Person.MiddleName + ' ', '') + Person.LastName
         + ': ' + Coalesce(Person.Suffix, '') + Employee.JobTitle,
    Employee.BusinessEntityID, Employee.NationalIDNumber
    FROM HumanResources.Employee
      INNER JOIN Person.Person
        ON Person.BusinessEntityID = Employee.BusinessEntityID;
 
INSERT INTO #Employees(Employee)
  SELECT TheList.NameOfEmployee + ' - '
    + Coalesce(
        Stuff(
           (SELECT ', ' + Department.Name + ' ('
              + Department.GroupName + ') '
              + Convert(CHAR(5), Shift.StartTime) + ' to '
              + Convert(CHAR(5), Shift.EndTime)
            FROM HumanResources.EmployeeDepartmentHistory
              INNER JOIN HumanResources.Department
                ON Department.DepartmentID = EmployeeDepartmentHistory.DepartmentID
              INNER JOIN HumanResources.Shift
                ON Shift.ShiftID = EmployeeDepartmentHistory.ShiftID
            WHERE EmployeeDepartmentHistory.BusinessEntityID =
                       TheList.BusinessEntityID
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),  1,2,''),'?') 
                         AS Department
    FROM #WorkPad TheList;
INSERT INTO @log(WhatHappened) 
  SELECT 'The use of a temporary Table took '--where the routine you want to time ends
DROP TABLE #Employees
DROP  TABLE #WorkPad
 
/* now we see how long each took. */
SELECT ending.WhatHappened, DateDiff(ms, starting.WHENItDid, ending.WHENItDid) AS ms
  FROM @log AS starting
    INNER JOIN @log AS ending
      ON ending.TheOrder = starting.TheOrder + 1;
--list out all the timings

規(guī)模問(wèn)題和忘記提供關(guān)鍵或提示

如果我們聯(lián)接兩個(gè)表變量，性能如何？讓我們嘗試一下。在此示例中，我們需要兩個(gè)簡(jiǎn)單的表，一個(gè)表包含英語(yǔ)中的所有常用單詞（CommonWords），另一個(gè)表包含Bram Stoker的“ Dracula”中的所有單詞的列表（WordsInDracula）。該TestTVsAndTTs下載包括腳本來(lái)創(chuàng)建這兩個(gè)表，并填充和與之相關(guān)的文本文件中每一個(gè)。有60,000個(gè)常用詞，但Bram Stoker僅使用了10,000個(gè)。前者遠(yuǎn)未達(dá)到收支平衡點(diǎn)，在那里人們開(kāi)始偏愛(ài)臨時(shí)表。

我們將使用四個(gè)簡(jiǎn)單的外部聯(lián)接查詢，測(cè)試結(jié)果的NULL值，以查找不存在于德古拉中的常見(jiàn)單詞，不存在于德古拉中的常見(jiàn)單詞，不存在于德古拉中的單詞，最后是另一個(gè)查詢以查找在德古拉語(yǔ)中很常見(jiàn)，但方向相反。當(dāng)我顯示測(cè)試裝備的代碼時(shí)，您很快就會(huì)看到查詢。

以下是初始測(cè)試運(yùn)行的結(jié)果。在第一次運(yùn)行中，兩個(gè)表變量都具有主鍵，而在第二次運(yùn)行中，它們都是堆，只是為了查看我是否在夸大未在表變量中聲明索引的問(wèn)題。最后，我們對(duì)臨時(shí)表運(yùn)行相同的查詢。出于說(shuō)明目的，所有測(cè)試都故意在緩慢的開(kāi)發(fā)服務(wù)器上運(yùn)行；使用生產(chǎn)服務(wù)器，您將獲得截然不同的結(jié)果。

除了對(duì)主要差異進(jìn)行一些廣泛的解釋之外，我不會(huì)深入研究這些績(jī)效指標(biāo)背后的執(zhí)行計(jì)劃的細(xì)節(jié)。對(duì)于臨時(shí)表查詢，優(yōu)化器具有對(duì)基數(shù)和主鍵約束中的元數(shù)據(jù)的全面了解，因此選擇了有效的“合并聯(lián)接”運(yùn)算符來(lái)執(zhí)行聯(lián)接操作。對(duì)于具有主鍵的表變量，優(yōu)化器知道連接列中行的順序，并且它們不包含重復(fù)項(xiàng)，但假定它僅處理一行，因此改為選擇嵌套循環(huán)加入。在這里，它掃描一個(gè)表，然后針對(duì)返回的每一行執(zhí)行另一表的單獨(dú)查找。數(shù)據(jù)集越大，效率越低，并且在掃描CommonWords表變量的情況下尤其不利，因?yàn)檫@會(huì)導(dǎo)致對(duì)表變量的搜索超過(guò)60K Dracula。該嵌套循環(huán)聯(lián)接達(dá)到“峰值效率”使用表變量堆二，十分鐘的查詢，因?yàn)樗婕皵?shù)千表掃描CommonWords。有趣的是，這兩個(gè)“德古拉中的常用單詞”查詢的性能要好得多，這是因?yàn)閷?duì)于這兩個(gè)查詢，優(yōu)化器選擇了哈希匹配聯(lián)接。

總體而言，臨時(shí)表似乎是最佳選擇，但我們還沒(méi)有完成！讓我們OPTION (RECOMPILE)向使用帶有主鍵的表變量的查詢添加提示，然后針對(duì)這些查詢以及使用臨時(shí)表的原始查詢重新運(yùn)行測(cè)試。我們暫時(shí)不去那些可憐的堆。

如果您也給那些可憐的人OPTION (RECOMPILE)暗示，會(huì)發(fā)生什么呢？瞧，故事為他們而改變，所以所有三個(gè)時(shí)機(jī)都更加接近。

有趣的是，即使在堆上也很快速的兩個(gè)“德古拉常用詞”查詢現(xiàn)在要慢得多。擁有正確的行數(shù)后，優(yōu)化器會(huì)更改其策略，但是由于在定義約束和鍵時(shí)它仍然沒(méi)有可用的有用元數(shù)據(jù)，因此，它是一個(gè)錯(cuò)誤的選擇。它掃描CommonWords堆，然后嘗試“部分聚合”，估計(jì)它將從6萬(wàn)行聚合到幾百行。它不知道沒(méi)有重復(fù)項(xiàng)，因此實(shí)際上它根本不會(huì)聚合下來(lái)，并且聚合和隨后的聯(lián)接會(huì)溢出到tempdb。

試驗(yàn)臺(tái)

請(qǐng)注意，這是最終形式的測(cè)試臺(tái)，顯示了三種不同類型表的大致相同的性能。您將需要?jiǎng)h除OPTION (RECOMPILE)提示以恢復(fù)原始狀態(tài)。

USE PhilFactor;
--create the working table with all the words from Dracula in it
DECLARE @WordsInDracula TABLE
  (word VARCHAR(40) NOT NULL PRIMARY KEY CLUSTERED);
 
INSERT INTO @WordsInDracula(word) SELECT WordsInDracula.word FROM dbo.WordsInDracula;
 
--create the other working table with all the common words in it
DECLARE @CommonWords TABLE
  (word VARCHAR(40) NOT NULL PRIMARY KEY CLUSTERED);
 
INSERT INTO @CommonWords(word) SELECT commonwords.word FROM dbo.commonwords;
 
--create a timing log 
DECLARE @log TABLE
  (TheOrder INT IDENTITY(1, 1),
WhatHappened VARCHAR(200),
WhenItDid DATETIME2 DEFAULT GetDate());
 
----start of the timing (never reported)
INSERT INTO @log(WhatHappened) SELECT 'Starting My_Section_of_code';
 
--place at the start
---------------section of code using table variables
--first timed section of code using table variables
SELECT Count(*) AS [common words not in Dracula]
  FROM @CommonWords AS c
    LEFT OUTER JOIN @WordsInDracula AS d
      ON d.word = c.word
  WHERE d.word IS NULL
OPTION(RECOMPILE);
 
INSERT INTO @log(WhatHappened)
  SELECT 'common words not in Dracula: Both table variables with primary keys ';
--where the routine you want to time ends
 
--Second timed section of code using table variables
SELECT Count(*) AS [common words in Dracula]
  FROM @CommonWords AS c
    LEFT OUTER JOIN @WordsInDracula AS d
      ON d.word = c.word
  WHERE d.word IS NOT NULL
OPTION(RECOMPILE);
 
INSERT INTO @log(WhatHappened)
  SELECT 'common words in Dracula: Both table variables with primary keys ';
--where the routine you want to time ends
 
--third timed section of code using table variables
SELECT Count(*) AS [uncommon words  in Dracula ]
  FROM @WordsInDracula AS d
    LEFT OUTER JOIN @CommonWords AS c
      ON d.word = c.word
  WHERE c.word IS NULL
OPTION(RECOMPILE);
 
INSERT INTO @log(WhatHappened)
  SELECT 'uncommon words in Dracula: Both table variables with primary keys ';
--where the routine you want to time ends
 
--last timed section of code using table variables
SELECT Count(*) AS [common words  in Dracula ]
  FROM @WordsInDracula AS d
    LEFT OUTER JOIN @CommonWords AS c
      ON d.word = c.word
  WHERE c.word IS NOT NULL
OPTION(RECOMPILE);
 
INSERT INTO @log(WhatHappened)
  SELECT 'more common words in Dracula: Both table variables with primary keys ';
--where the routine you want to time ends
 
---------------section of code using heap variables
DECLARE @WordsInDraculaHeap TABLE(word VARCHAR(40) NOT NULL);
 
INSERT INTO @WordsInDraculaHeap(word) SELECT WordsInDracula.word FROM dbo.WordsInDracula;
 
DECLARE @CommonWordsHeap TABLE(word VARCHAR(40) NOT NULL);
 
INSERT INTO @CommonWordsHeap(word) SELECT commonwords.word FROM dbo.commonwords;
 
INSERT INTO @log(WhatHappened) SELECT 'Test Rig Setup ';
--where the routine you want to time ends
 
--first timed section of code using heap variables
SELECT Count(*) AS [common words not in Dracula]
  FROM @CommonWordsHeap AS c
    LEFT OUTER JOIN @WordsInDraculaHeap AS d
      ON d.word = c.word
  WHERE d.word IS NULL
OPTION(RECOMPILE);
 
INSERT INTO @log(WhatHappened) SELECT 'common words not in Dracula: Both Heaps ';
--where the routine you want to time ends
 
--second timed section of code using heap variables
SELECT Count(*) AS [common words in Dracula]
  FROM @CommonWordsHeap AS c
    LEFT OUTER JOIN @WordsInDraculaHeap AS d
      ON d.word = c.word
  WHERE d.word IS NOT NULL
OPTION(RECOMPILE);
 
INSERT INTO @log(WhatHappened) SELECT 'common words in Dracula: Both Heaps ';
--where the routine you want to time ends
 
--third timed section of code using heap variables
SELECT Count(*) AS [uncommon words  in Dracula ]
  FROM @WordsInDraculaHeap AS d
    LEFT OUTER JOIN @CommonWordsHeap AS c
      ON d.word = c.word
  WHERE c.word IS NULL
OPTION(RECOMPILE);
 
INSERT INTO @log(WhatHappened) SELECT 'uncommon words in Dracula: Both Heaps ';
--where the routine you want to time ends
 
--last timed section of code using heap variables
SELECT Count(*) AS [common words  in Dracula ]
  FROM @WordsInDraculaHeap AS d
    LEFT OUTER JOIN @CommonWordsHeap AS c
      ON d.word = c.word
  WHERE c.word IS NOT NULL
OPTION(RECOMPILE);
 
INSERT INTO @log(WhatHappened) SELECT 'common words in Dracula: Both Heaps ';
--where the routine you want to time ends
 
---------------section of code using Temporary tables
CREATE TABLE #WordsInDracula (word VARCHAR(40) NOT NULL PRIMARY KEY);
 
INSERT INTO #WordsInDracula(word) SELECT WordsInDracula.word FROM dbo.WordsInDracula;
 
CREATE TABLE #CommonWords (word VARCHAR(40) NOT NULL PRIMARY KEY);
 
INSERT INTO #CommonWords(word) SELECT commonwords.word FROM dbo.commonwords;
 
INSERT INTO @log(WhatHappened) SELECT 'Temp Table Test Rig Setup ';
--where the routine you want to time ends
 
--first timed section of code using Temporary tables
SELECT Count(*) AS [common words not in Dracula]
  FROM #CommonWords AS c
    LEFT OUTER JOIN #WordsInDracula AS d
      ON d.word = c.word
  WHERE d.word IS NULL;
 
INSERT INTO @log(WhatHappened) SELECT 'common words not in Dracula: Both Temp Tables ';
--where the routine you want to time ends
 
--Second timed section of code using Temporary tables
SELECT Count(*) AS [common words in Dracula]
  FROM #CommonWords AS c
    LEFT OUTER JOIN #WordsInDracula AS d
      ON d.word = c.word
  WHERE d.word IS NOT NULL;
 
INSERT INTO @log(WhatHappened) SELECT 'common words in Dracula: Both Temp Tables ';
--where the routine you want to time ends
 
--third timed section of code using Temporary tables
SELECT Count(*) AS [uncommon words  in Dracula ]
  FROM #WordsInDracula AS d
    LEFT OUTER JOIN #CommonWords AS c
      ON d.word = c.word
  WHERE c.word IS NULL;
 
INSERT INTO @log(WhatHappened) SELECT 'uncommon words in Dracula:Both Temp Tables ';
--where the routine you want to time ends
 
--last timed section of code using Temporary tables
SELECT Count(*) AS [common words  in Dracula ]
  FROM #WordsInDracula AS d
    LEFT OUTER JOIN #CommonWords AS c
      ON d.word = c.word
  WHERE c.word IS NOT NULL;
 
INSERT INTO @log(WhatHappened) SELECT 'common words in Dracula: Both Temp Tables '; --where the routine you want to time ends
 
DROP TABLE #WordsInDracula;
DROP TABLE #CommonWords;
 
SELECT ending.WhatHappened AS [The test that was run],
  DateDiff(ms, starting.WhenItDid, ending.WhenItDid) AS [Time Taken (Ms)]
  FROM @log AS starting
    INNER JOIN @log AS ending
      ON ending.TheOrder = starting.TheOrder + 1;
--list out all the timings

結(jié)論

使用表變量沒(méi)有什么魯ck的事情。當(dāng)用于預(yù)期目的時(shí)，它們可以提供更好的性能，并且可以自行清理。在某個(gè)時(shí)候，讓他們獲得更好性能的妥協(xié)（不觸發(fā)重新編譯，不提供統(tǒng)計(jì)信息，不回滾，不并行）成為他們的失敗。

通常，SQL Server專家會(huì)就結(jié)果的大小提供一些明智的建議，這將導(dǎo)致表變量出現(xiàn)問(wèn)題。我在本文中向您顯示的結(jié)果將建議您過(guò)分簡(jiǎn)化問(wèn)題。有兩個(gè)重要因素：如果結(jié)果超過(guò)了，比如說(shuō)1000行（該數(shù)字取決于上下文），那么對(duì)于連接到表變量的任何查詢，都需要具有PRIMARY KEY或UNIQUE鍵。在某個(gè)時(shí)候，您還需要觸發(fā)重新編譯以獲得一個(gè)體面的執(zhí)行計(jì)劃，該計(jì)劃有其自身的開(kāi)銷。

即使這樣，性能也會(huì)受到嚴(yán)重影響，尤其是在執(zhí)行更復(fù)雜的處理時(shí)，因?yàn)閮?yōu)化器仍然無(wú)法訪問(wèn)統(tǒng)計(jì)信息，因此也不了解任何查詢謂詞的選擇性。在這種情況下，您需要切換到使用臨時(shí)表。

SQL Prompt 使用教程>>>