Edit Question - KnowledgeHub

Title Body

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)

Tags (comma-separated) Cancel