Note: Updated 10/28 based on conversation in the comments, and 12/10 with Denali info.
TSQL Tuesday #11: Misconceptions in SQL Server
This month’s #tsql2sDay is hosted by Sankar Reddy, and the topic is roughly, “Things you thought you knew, but didn’t.”
For my entry, I’m picking a misconception that I periodically fall victim to myself. The misconception is basically, “It’s best to use the PRINT command for all informational messages, and RAISERROR is just for errors.”
The Ubiquitous Print Statement
We all learn the print command as one of our earliest commands with TSQL in SQL Server. It works like this:
print 'The enraged badger bit the angry stoat on its prickly posterior.'
The Poorly Named RAISERROR()
Most people also learn early on that print is not the preferred item for returning errors to calling applications. We learn to use RAISERROR for that.
BEGIN TRY --cause an error forcing us to go to the catch block exec sp_executesql N'select top 1 * from dbo.thisdoesnotexist' END TRY BEGIN CATCH SELECT @errorMessage= 'Welcome to the catch block: ' + ERROR_MESSAGE() RAISERROR ( @errormessage, 16, 1 ) END CATCH
I think RAISERROR() is pretty poorly named. I would rather it was named something along the lines of MESSAGEHANDLER() or HOWMAYIHELPYOUMASTER(). It’s really just a helper function for delivering messages, which may or may not be errors.
The Misconception: It’s Appropriate to Use Print for Informational Messages
Although many of us understand how to use RAISERROR, its name is pretty misleading. It says it’s to be used for ERRORS.
So we are still inclined to use PRINT for informational messages that we just want to display when running a script or procedure within SQL Server, and we’re also inclined to use it for debugging dynamic sql or anything with contatenated strings.
The Books Online page for ‘Using Print’ shows samples of this type of usage.
However, if you check out the Books Online Page for the Print statement itself, it warns you off a little in the ‘Remarks’ section:
A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated. The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000).
This character limit can be a pain.
What isn’t mentioned in Books Online is that it can take quite a while for PRINT messages to be printed. If you’re running a script which takes some time, the message isn’t necessarily buffered to the client when something occurs: it can be minutes, or even an hour or more before the message is buffered out. This leaves the person running the script without any helpful log of what has occurred, and they need to rely on DMVs and other commands to determine what the script is doing at the present time.
The truth is that it just feels easy to use the print statement for returning messages, because we learned it first. And the syntax is dead simple. But it’s not like the more appropriate solution is a lot of work.
The Right Approach: Use RAISERROR for All Message Handling and Errors
The right thing to do is to use RAISERROR for all our message handling, as well as error handling. For informational messages where you don’t want to transfer control to the catch block, just use a severity of 0-10.
Main benefits:
- Consistent approach to handling messages in your application (error and non error);
- Messages are always given an explicit severity, to define whether or not they are an error, and how that can be acted on;
- You have the option to use the NOWAIT option to stream output to the client. Note to developers: please use this when designing scripts for operations teams to run, which may not run quickly in production! Making sure the output is flushed promptly greatly simplifies checking the current status of a script.
Why You Can’t Do Away With PRINT: Debugging Long Dynamic SQL Strings
There’s one case where PRINT remains desirable: debugging long dynamic SQL strings.
Remember the note above regarding truncation of strings returned by PRINT? The same issue exists with RAISERROR, which returns only 2048 characters.
When building dynamic SQL strings, it’s often desirable to set a parameter so the string is printed instead of being run (or in addition to being run). For this, neither PRINT nor RAISERROR work well by themselves. In this case, you want to build a specialized procedure to print the entire message. A quick search shows quite a few of these have been posted for review and download.
Denali Update!
There’s a new kid coming to town: THROW. Aaron Bertrand gives a great comparison of THROW and RAISERROR here. Sadly, THROW does not have the NOWAIT option.
I thought NoWait was for when you change your mind about raising an error. Like so:
RaisError (‘Throw this error.’, 16, 1) With NoWait Don’tThrowthisError;
8-P
Robert, I think there’s special facilities for people who make geek jokes about RAISERROR.
But for some reason your comment did remind me of one thing about NOWAIT which can look odd– it flushes the message buffer, so any other messages in the buffer which are hanging out there will come through also.
Please feel free to take this opportunity to make any jokes on flushing as needed.
RAISERROR will also cause resultsets to be flushed in SSMS. So if you have two SELECTs and one is quick and small and the other is slow, then the first will not start displaying unless the seconds output starts coming down. A RAISERROR between the SELECTs will get you the first SELECTs results.
Yep! To guarantee that RAISERRORS flushes the buffers, you do need to specify the NOWAIT option, though. It’s *great* to use.
That’s spot-on.,.. I’ve been so much in the habit of using the print statement because RAISERROR()’s name has always led me to believe that it’s only for errors. Now I know “the Rest of the Story”. Changing print statements to RAISERROR()s
I love this post. I am lazy and use print when debugging. Now I will think of this post every time I consider using it
And I learned something new about NOWAIT and Robert’s taste for really geeky jokes!
Erin
Nice one! I love me some RAISERROR … But the real misconception is the pronunciation of the word.
It’s not “raise error.” Rather, it’s “raise roar.”
(At least, that’s how the members of the T-SQL programmability team refer to it
)
But… RaisError only displays 2044 characters. This is a more severe limitation than PRINT.
Oh, look at that– it’s 2047. You get 2044 if you go over it because it swaps in an ellipsis.
Thanks for the comment– this is why blogging is awesome.
So I’m going to stick to the claim that raiserror is better for message handling– because of the specific typing of severity level and also because of the ability to flush the log. I’ve seen waaaay too many longrunning loops where the messages just take forever to flush out.
But for the case of debugging long dynamic sql strings, methinks a helper function is going to be needed which moves through the string and outputs it in chunks. And because of the RAISERROR message headers, methinks print is actually going to be more useful for that.
Clammy seduction, indeed.
I’ll update the body of the post tomorrow after I do some testing.
Nice post Kendra,
I think that the real strength of raiserror is that it can format the string a bit ANSI” C like – quite useful in CATCH block.
Also NOWAIT with all that flushing gives you much more insight into progress of long running batches.