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正式版

SQL提示實(shí)現(xiàn)了兩個(gè)靜態(tài)代碼分析規(guī)則，以檢查代碼是否可能濫用該SET NOCOUNT命令：

• PE008 –SET NOCOUNT OFF使用
• PE009 – SET NOCOUNT ONDML之前沒(méi)有

默認(rèn)情況下，在SQL Server實(shí)例級(jí)別SET NOCOUNT設(shè)置為OFF，這意味著DONE_IN_PROC將針對(duì)存儲(chǔ)過(guò)程中的每個(gè)語(yǔ)句將消息發(fā)送到客戶端。當(dāng)使用提供與Microsoft SQL Server執(zhí)行查詢公用事業(yè)，消息“NN行受到影響”將默認(rèn)，在Transact-SQL語(yǔ)句，如年底顯示SELECT，INSERT，UPDATE，和DELETE。

Microsoft建議在會(huì)話級(jí)別上有選擇地使用SET NOCOUNT ON來(lái)防止發(fā)送這些消息：“對(duì)于包含多個(gè)不返回大量實(shí)際數(shù)據(jù)的語(yǔ)句的存儲(chǔ)過(guò)程，消除這些消息可以顯著提高性能，因?yàn)榫W(wǎng)絡(luò)通信量很大。大大減少”。

通常，最好的方法是阻止發(fā)送行計(jì)數(shù)消息（除非需要發(fā)送它們），但棘手的部分是容納使用并經(jīng)常濫用這些消息的舊版應(yīng)用程序。此外，對(duì)于數(shù)據(jù)庫(kù)應(yīng)用程序（例如ORM）的中間層對(duì)過(guò)程的異步處理，發(fā)送這些消息有時(shí)可能會(huì)成為問(wèn)題。與存儲(chǔ)過(guò)程的結(jié)果相比，行計(jì)數(shù)消息傳輸?shù)娇蛻舳说乃俣纫枚啵@可能會(huì)阻塞線程。

什么是訊息？

與數(shù)據(jù)庫(kù)的連接分別傳遞數(shù)據(jù)和消息。在Management Studio（SSMS）中，當(dāng)查詢結(jié)果呈現(xiàn)為網(wǎng)格時(shí)，它們由查詢窗口中的單獨(dú)窗格表示。進(jìn)行sqlClient連接時(shí)，InfoMessage處理程序可以讀取消息流。為了使客戶端能夠讀取和處理服務(wù)器發(fā)送的警告或消息，客戶端可以通過(guò)可以響應(yīng)這些事件的SqlInfoMessageEventHandler委托來(lái)偵聽(tīng)這些消息。

在本文中，我們僅關(guān)注一種類(lèi)型的消息。行數(shù)。但是，SQL Server也可以發(fā)送消息以響應(yīng)特定的命令。連RAISERROR（10或更低的嚴(yán)重程度），以及PRINT報(bào)表和SET STATISTICS語(yǔ)句的三人組（SET STATISTICS IO ON，SET STATISTICS TIME ON，SET STATISTICS XML ON）。我的文章《不熱情的測(cè)試人員的常規(guī)SQL DML測(cè)試》說(shuō)明了監(jiān)視性能時(shí)使用此消息流的價(jià)值。該SET NOCOUNT命令僅確定是否發(fā)送行計(jì)數(shù)消息。

多個(gè)應(yīng)用程序，組件，小部件（例如網(wǎng)格）和中間件（例如ORM）使用rowcount消息來(lái)獲取當(dāng)前數(shù)據(jù)結(jié)果的計(jì)數(shù)，即使在相同的情況下通常很難將查詢與count消息進(jìn)行匹配會(huì)話會(huì)執(zhí)行很多其他查詢，這也可能導(dǎo)致線程阻塞。最好在過(guò)程的返回碼中或通過(guò)輸出變量使用來(lái)關(guān)閉這些SET NOCOUNT ON行計(jì)數(shù)消息并使用值返回計(jì)數(shù)@@ROWCOUNT。但是，有許多舊代碼需要容納。

SET NOCOUNT設(shè)置的范圍是什么？

與SQL Server建立連接并啟動(dòng)會(huì)話后，只需設(shè)置NOCOUNT一次，這將影響您在該會(huì)話中所做的一切。SET您所做的陳述將僅更改當(dāng)前會(huì)話對(duì)特定信息的處理；您在該會(huì)話中執(zhí)行的每個(gè)批處理都將繼承這些設(shè)置。

如果SET在存儲(chǔ)過(guò)程或觸發(fā)器中運(yùn)行語(yǔ)句，SET則從存儲(chǔ)過(guò)程或觸發(fā)器返回控制后，將恢復(fù)該選項(xiàng)的先前值。同樣，如果SET NOCOUNT在動(dòng)態(tài)SQL字符串中有一條通過(guò)使用sp_executesql或來(lái)運(yùn)行的語(yǔ)句EXECUTE，則在SET執(zhí)行動(dòng)態(tài)SQL字符串后將還原該選項(xiàng)的初始值。因此，無(wú)需NOCOUNT在存儲(chǔ)過(guò)程或觸發(fā)器的末尾顯式設(shè)置。

除了過(guò)程，觸發(fā)器和動(dòng)態(tài)執(zhí)行的批處理之外，NOCOUNT會(huì)話中的所有設(shè)置都會(huì)保留在會(huì)話中，直到更改為止。

SET NOCOUNT ON的性能優(yōu)勢(shì)是什么？

使用精心設(shè)計(jì)的存儲(chǔ)過(guò)程，您將只能通過(guò)覆蓋的默認(rèn)服務(wù)器范圍設(shè)置來(lái)獲得微不足道的性能提升NOCOUNT。也就是說(shuō)，在特殊情況下，使用SET NOCOUNT ON將帶來(lái)巨大收益。這完全取決于在過(guò)程中執(zhí)行的查詢的數(shù)量和頻率。例如，如果某個(gè)過(guò)程正在使用游標(biāo)執(zhí)行許多查詢，這些查詢的結(jié)果隨后構(gòu)成返回的查詢的一部分，或者該過(guò)程包含許多未返回大量實(shí)際數(shù)據(jù)的語(yǔ)句，則該過(guò)程可以執(zhí)行與相比，速度提高了十倍NOCOUNT OFF，因?yàn)榫W(wǎng)絡(luò)流量大大減少了。在過(guò)程中僅執(zhí)行一兩個(gè)查詢，收益將不到5％。

為什么不只在數(shù)據(jù)庫(kù)實(shí)例級(jí)別啟用NOCOUNT？

的用戶選項(xiàng)服務(wù)器配置設(shè)置指定“全局缺省”為每個(gè)SET選項(xiàng)，包括NOCOUNT。默認(rèn)情況下，SQL Server實(shí)例將被NOCOUNT禁用，因此針對(duì)該實(shí)例上的數(shù)據(jù)庫(kù)發(fā)出的每條語(yǔ)句將導(dǎo)致最后返回一條消息，指出受影響的行數(shù)。

您可以使用來(lái)修改實(shí)例級(jí)別的行為，從而啟用NOCOUNT并阻止發(fā)送這些消息sp_configure，如清單1所示。這將影響在進(jìn)行設(shè)置之后啟動(dòng)的所有用戶會(huì)話的默認(rèn)設(shè)置。

EXEC sys.sp_configure 'user options', '512'; -- 512 = NOCOUNT

用戶可以通過(guò)發(fā)出SET NOCOUNT僅影響其單個(gè)會(huì)話的語(yǔ)句來(lái)覆蓋服務(wù)器級(jí)別的默認(rèn)值。

觸發(fā)器不應(yīng)發(fā)送行數(shù)消息；這條規(guī)定沒(méi)有例外。實(shí)際上，如果中間應(yīng)用程序?qū)悠谕承┬杏?jì)數(shù)消息，并且將其SET NOCOUNT OFF用于觸發(fā)器，則可能會(huì)導(dǎo)致奇怪的隨機(jī)錯(cuò)誤。甚至SSMS的數(shù)據(jù)網(wǎng)格也可能觸犯觸發(fā)問(wèn)題。

但是，在其他地方，也有很多例外。如果您有任何舊組件使用返回的rowcount消息訪問(wèn)數(shù)據(jù)庫(kù)，則在實(shí)例級(jí)別阻止這些消息可能會(huì)導(dǎo)致問(wèn)題。通常，可以通過(guò)NOCOUNT針對(duì)這些組件正在使用的存儲(chǔ)過(guò)程進(jìn)行適當(dāng)設(shè)置來(lái)輕松地容納這些組件。但是，如果他們直接訪問(wèn)表，并且您無(wú)法SET NOCOUNT OFF為這些會(huì)話添加，則更改數(shù)據(jù)庫(kù)實(shí)例級(jí)別的設(shè)置將是不明智的。

同樣，如果應(yīng)用程序中的某個(gè)組件（例如ORM或LINQ）正在濫用此消息來(lái)確定結(jié)果的行數(shù)，那么如果您關(guān)閉消息，則可能會(huì)發(fā)生一些不良情況。

如果您開(kāi)啟NOCOUNT，會(huì)有什么壞處？

如果使用DataAdaptor調(diào)用SQL Server存儲(chǔ)過(guò)程來(lái)編輯或刪除數(shù)據(jù)，請(qǐng)不要SET NOCOUNT ON在存儲(chǔ)過(guò)程定義中使用。這將導(dǎo)致受影響的行計(jì)數(shù)返回為零，并且DataAdapter拋出DBConcurrencyException。實(shí)際上，明智的防御性編程將意味著SET NOCOUNT OFF在這些情況下發(fā)布明確的建議。

該sqlclient.sqlcommand類(lèi)還可以遇到與問(wèn)題SET NOCOUNT ON，可能是由客戶端使用ODBC的方式造成。當(dāng)應(yīng)用程序調(diào)用SQLRowCount時(shí)，行計(jì)數(shù)消息在ODBC中可用。這不是可靠的信息，因?yàn)槟承?shù)據(jù)源無(wú)法在獲取結(jié)果之前返回結(jié)果集中的行數(shù)。

即使在SQL Server中，僅當(dāng)您NOCOUNT在讀取SQLRowCount之后隨后測(cè)試“狀態(tài)”時(shí)，此值才可靠。當(dāng)NOCOUNT選項(xiàng)設(shè)置為on，然后SQLROWCOUNT返回0，即使有結(jié)果。如果SQLRowCount返回0，則應(yīng)用程序應(yīng)通過(guò)測(cè)試特定于SQL Server的屬性的值來(lái)確定是否NOCOUNT為。如果返回該值，則SQLRowCount的值為0僅表示SQL Server未返回行數(shù)。如果返回，則表示已關(guān)閉，SQLRowCount中的值為0ONSQL_SOPT_SS_NOCOUNT_STATUSSQL_NC_ONSQL_NC_OFFNOCOUNT 指示該語(yǔ)句不影響任何行，因此不需要處理結(jié)果。

那么，什么是“最佳實(shí)踐”建議？

可行的簡(jiǎn)單建議是保持?jǐn)?shù)據(jù)庫(kù)實(shí)例級(jí)別的默認(rèn)狀態(tài)不變，并SET NOCOUNT ON在每個(gè)存儲(chǔ)過(guò)程，觸發(fā)器和動(dòng)態(tài)執(zhí)行的批處理的開(kāi)始處添加一個(gè)。此規(guī)則將毫無(wú)例外地適用于所有觸發(fā)器。存儲(chǔ)過(guò)程也將不需要這些消息，除非被試圖使用它們來(lái)獲取結(jié)果行數(shù)的應(yīng)用程序從數(shù)據(jù)庫(kù)外部調(diào)用它們。通常，最好使用@@ RowCount中的值將計(jì)數(shù)發(fā)送到輸出變量中，但這無(wú)助于預(yù)先存在的應(yīng)用程序組件。如果需要確保查詢返回行計(jì)數(shù)消息，則應(yīng)指定它而不是采用當(dāng)前設(shè)置。

試用下載>>>

SQL Prompt 使用教程>>>