I was minding my own business innocently reading a blog post by Erik Darling, when this tip smacked me in the proverbial forehead. I even learned something that had always bothered me but had been too lazy to investigate further.
Tip #4: Know Your Place
Don’t be afraid to use
RAISERRORto mark off sections of your code. This also belongs in a Debug mode so you’re not needlessly returning noise, but can be super helpful for you to figure out things like which code path was hit and which section throws an error executing common code.
WITH NOWAITto print messages immediately.
Read that last line again. It’s saying that the
If you want immediate output from your long-running process, use
RAISERROR instead of
WITH NOWAIT parameter.
Naturally I can’t leave you hanging with a 150-word blog post, most of which is quoting Erik, so I decided to do some digging. The question I posed myself was “How big is the
A number of people before me have had a similar question (How do I flush the PRINT buffer in TSQL?), and there’s no definitive answer to how big the buffer is, however the pseudonymous Phil Factor writes in an answer to the question PRINT statement in T-SQL:
The Print statement in TSQL is a misunderstood creature, probably because of its name. It actually sends a message to the error/message-handling mechanism that then transfers it to the calling application.
So the answer — as with everything in this space — is “it depends.” My most educated guess is that the buffer size is dependent on available memory.
Raise your frustration at the spelling of
RAISERROR with me on Twitter at @bornsql.