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)