T-SQL aside: replace PRINT with RAISERROR

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 RAISERROR to 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.

Why RAISERROR over PRINT? Nice message formatting, and you can use WITH NOWAIT to print messages immediately.

With PRINT, you may be stuck waiting for enough buffers to fill up to display a message.

Read that last line again. It’s saying that the PRINT command needs to fill some sort of memory buffer before you see any output. Sometimes you’re lucky, and sometimes you’re not. Erland Sommarskog says the same thing in the series Error and Transaction Handling in SQL Server (Part 1: Jumpstart Error Handling | Part 2: Commands and Mechanisms | Part 3: Implementation).

If you want immediate output from your long-running process, use RAISERROR instead of PRINT, and make use of the 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 PRINT buffer?”

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.