Teaching and Learning.
The NOEXEC Thing.

The year 2015 started in a crazy, but good way. I was engaged to deliver more than 7 courses just in the first half of the year and for those of you who are trainers, you guys know that every single time when you teach a class you always learn something new. Most of the times it’s because your students have some specific case that they want to ask for or it may be because of a weird thought they have and just want to check what’s your opinion of it. This is where the inspiration, let’s call it, for this blog post is also coming from – we have the “teach something new” idea and I was teaching when I learned this and now I again will, in a way, teach it. So here’s the thing…

On the first day of my training on SQL Server Development, one of my students asked:

Can we somehow open a new query window and start writing our queries there, but if we by mistake hit the F5 or the Execute button not execute anything?

My first though was – yeap, just put the whole code into a comment like this:

/*

your code here

*/

However, my students said that’s not an option because what may also happen is selecting a piece of the code and executing it and they didn’t want that. At this point I have no other ideas, but I promised I will check for them during the break. So after we ended the module we were discussing and I said something like: “Coffee time now!”, one of the students said:

“Have you tried this NOEXEC” thing?

I will be honest with all of you in the same way I was with the group – I haven’t. I haven’t even heard of it before. So during the break I take it for a spin and did a quick demo for them. Here’s how this works…

SET NOEXEC ON
-- session level option and enabled till you explicitly disable it
-- or just open a new session

USE AdventureWorks2012
GO
-- some queries...
SELECT * FROM Person.Person

TRUNCATE TABLE Person.Person

SELECT COUNT(*) FROM Person.Person

When you run that whole thing, you will notice that nothing gets executed. Same if you first run only the SET NOEXEC ON command and then try to run any of the below queries – it just says that the command completed successfully, but nothing is returned. That will remain true until you execute SET NOEXEC again and set it to OFF. So if I want to execute any of those 2 queries I will have to first do the following:

-- SET NOEXEC ON
SET NOEXEC OFF
-- execute that and then you are able to execute any of the queries below

USE AdventureWorks2012 GO

SELECT COUNT(*) FROM Person.Person -- see – nothing was deleted…

So once the students saw how this works, they were quite happy because that was, as they said, “a perfect solution for them”(forgot to mention – they are professional developers, not just students that want to learn something new). They have had some problems in the past which were caused by cases like this – writing or opening a sql file with dozens of SQL queries inside and running the whole thing against production instance(of course – by mistake). You know what happened after that, right?

Thanks for hosting this one, Mike! I hope I indeed taught something new!