Interesting behavior in "NOEXEC ON"

Question

While I was writing some T-SQL queries with NOEXEC ON, I experienced interesting behavior of SQL Server and I am curious about why it happened. Sometimes I got only

Command(s) successfully.

message as I expected, but sometimes I got one or more

(0 row(s) affected)

messages.

I know that SET NOEXEC ON command compiles query but does not execute it, so I think I should not gotten any

(0 row(s) affected)

messages.

In the first example, everything looks normal.

SET NOEXEC ON
INSERT INTO Test (column1) VALUES ('etc')

Result:

Command(s) successfully.

But in the second example, I think something goes wrong...

SET NOEXEC ON
DELETE FROM Test

Result:

(0 row(s) affected)

In the third example I used temp table:

CREATE TABLE #tmp (id INT IDENTITY(1, 1), idX INT)

SET NOEXEC ON
INSERT INTO #tmp (idX) VALUES (1)
DELETE FROM Test

SET NOEXEC OFF
DROP TABLE #tmp

Result:

(0 row(s) affected)

And finally I added only GO to my query, I think result is interesting

CREATE TABLE #tmp (id INT IDENTITY(1, 1), idX INT)
SET NOEXEC ON
GO

INSERT INTO #tmp (idX) VALUES (1)
DELETE FROM Test

SET NOEXEC OFF
DROP TABLE #tmp

Result:

(0 row(s) affected)

(0 row(s) affected)

Answer 1

Although this might not be the answer to your question:

But when you delete

SET NOEXEC ON 
DELETE FROM Test 

If you add a where condition to the DELETE STATEMENT like DELETE FROM Test WHERE COLUMN1='etc'

You will get the desired results...This behavior may be because of DDL and DML statements we executed.

I have also analyzed the third situation where in if you insert in temporary table it gives you (0 rows Affected) but if the same insert is on some database or permanent table is done it gives (Command(s) completed successfully.)

Here it might be because of temp table and permanent table.

For the 4th one you have added a GO :

GO will execute the related sql commands n times.

So if you individually execute the insert statement and the delete statement both has some return value and GO is adding them in batch plan.

Answer 2

I reproduce the behaviour on SQl Server 2005 and 2008 so it isnt exclusivelly to R2 and the same thing that happens with the insert, happens with the update statment, so delete seems to be the exception. Even Truncate (which is pretty much a delete gets the standard message)

I also thought it could be a SQL Server Management Studio thing but no, I tested on another tool and even ran it on SQLCMD and could see the same behavior:

Except that the "Command(s) successfully." message doesn't appear (this must be exclusively of SSMS)

Anyway, I cant explain, but I can guess. I imagine it happens because the delete statement does something else (or less) that Insert and Update don't. The compilation process is divided into four parts: parsing, normalization, compilation and optimization. I assume that something inside these steps is done differently by the delete statement, that's why we get a different result