{"id":7271,"date":"2026-07-16T10:47:28","date_gmt":"2026-07-16T17:47:28","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=7271"},"modified":"2026-07-16T10:48:00","modified_gmt":"2026-07-16T17:48:00","slug":"tsql-nocount","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/tsql-nocount\/","title":{"rendered":"T-SQL Hygiene: What’s the Big Deal with SET NOCOUNT ON?"},"content":{"rendered":"

Perhaps you have seen SET NOCOUNT<\/a> ON;<\/code> at the beginning of a stored procedure and wondered why it is there. By default, SQL Server uses SET NOCOUNT OFF;<\/code>, which sends messages such as \u201c(10 rows affected)\u201d after each statement. Using SET NOCOUNT ON;<\/code> suppresses those messages.<\/p>\n

For example<\/h3>\n

If an update changes 10 rows, with row-count messages enabled:<\/p>\n

SET NOCOUNT OFF;<\/code><\/pre>\n
SQL Server sends:<\/em><\/p>\n
(10 rows affected)<\/code><\/pre>\n
And with row-count messages suppressed:<\/p>\n
SET NOCOUNT ON;<\/code><\/pre>\n
SQL Server sends no row-count message.<\/em><\/p>\n
A developer’s perspective<\/h3>\n
In a .NET or Entity Framework application, the affected-row messages are usually ignored. Your code typically cares about returned data, output parameters, return values, or an explicitly returned row count. Suppressing unused messages can reduce unnecessary work for SQL Server, the network, and your application.<\/p>\n
<\/i>One exception to watch for<\/strong><\/p>Code using SqlCommand.ExecuteNonQuery() may receive -1 instead of an affected-row count when NOCOUNT is enabled. Return @@ROWCOUNT explicitly when the application requires that value.<\/div><\/p>\n
A simple walk-through<\/h2>\n
Consider this stored procedure, which closes old orders and returns the number updated:<\/p>\n
CREATE PROCEDURE dbo.CloseOldOrders AS\r\nBEGIN\r\n    SET NOCOUNT ON;\r\n\r\n    UPDATE dbo.Orders\r\n    SET Status = 'Closed'\r\n    WHERE ShipDate < GETDATE();\r\n\r\n    SELECT @@ROWCOUNT AS RowsUpdated;\r\nEND;<\/code><\/pre>\n
SET NOCOUNT ON;<\/code> does not prevent the update, suppress the SELECT<\/code>, disable @@ROWCOUNT<\/code>, or change the result of the procedure. It only suppresses the extra affected-row message.<\/p>\n
Better performance at scale<\/h2>\n
The savings from one statement are small, but they add up. A procedure with 20 statements called 100,000 times could avoid about 2 million<\/em> unnecessary row-count protocol tokens. These tokens may share network packets, but they still add processing overhead. Loops, triggers, multiple updates, and nested procedures can generate even more. Removing them can reduce network traffic, client processing, latency, and compute usage.<\/p>\n
<\/i>Nested Procedures<\/strong><\/p>When one stored procedure calls another, the current NOCOUNT setting applies during the nested call unless the nested procedure changes it. When the nested procedure returns, SQL Server restores the caller\u2019s previous setting. Even so, each procedure should include SET NOCOUNT ON; so its behavior is clear and independent.<\/div><\/p>\n
Safe and easy<\/h3>\n
Inside a transaction, SET NOCOUNT ON;<\/code> does not affect locking, commits, rollbacks, or error handling. It only controls whether affected-row messages are sent.<\/p>\n
CREATE PROCEDURE dbo.CloseOrder\r\n    @OrderId int\r\nAS\r\nBEGIN\r\nSET NOCOUNT ON;\r\n\r\n    BEGIN TRANSACTION;\r\n\r\n        UPDATE dbo.Orders\r\n        SET Status = 'Closed'\r\n        WHERE OrderId = @OrderId;\r\n\r\n        INSERT INTO dbo.OrderHistory (OrderId, Status)\r\n        VALUES (@OrderId, 'Closed');\r\n\r\n    COMMIT;\r\n\r\n    SELECT @OrderId AS OrderId, 'Closed' AS Status;\r\n\r\nEND;<\/code><\/pre>\n
With SET NOCOUNT OFF;<\/code>, the procedure sends two affected-row messages before returning the final order status. With SET NOCOUNT ON;<\/code>, it returns only the final order status.<\/p>\n
The transaction behaves exactly the same either way. NOCOUNT<\/code> simply prevents the intermediate messages that your application probably ignores.<\/p>\n
<\/i>Messages can still be sent.<\/strong><\/p>SET NOCOUNT ON;<\/code> suppresses only affected-row messages. It does not suppress messages produced by PRINT<\/code> or RAISERROR<\/code>, and it does not suppress result sets returned by SELECT<\/code><\/div><\/p>\n
Conclusion<\/h2>\n
SET NOCOUNT ON;<\/code> is a small change with a real benefit. It removes messages your .NET application usually does not need, reduces unnecessary work, and keeps stored procedure behavior more predictable. For most stored procedures, it should be the default unless you specifically need those messages.<\/p>\n","protected":false},"excerpt":{"rendered":"
Learn why SET NOCOUNT ON improves stored procedure efficiency, reduces unnecessary messages, and matters to .NET developers.<\/p>\n","protected":false},"author":96788,"featured_media":7301,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1,672,619],"tags":[487],"class_list":["post-7271","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql","category-sql-server-2025","category-t-sql","tag-query-processing"],"acf":[],"blog_post_summary":"
Learn why SET NOCOUNT ON improves stored procedure efficiency, reduces unnecessary messages, and matters to .NET developers.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/7271","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/96788"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=7271"}],"version-history":[{"count":1,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/7271\/revisions"}],"predecessor-version":[{"id":7302,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/7271\/revisions\/7302"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/7301"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=7271"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=7271"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=7271"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}