In this blog post, we will explore a more efficient way to display status messages in SQL Server using the RAISERROR statement with the NOWAIT option.

The Problem

When working with batches of code in SQL Server Management Studio (SSMS), it can be challenging to track the progress of the execution. Many developers resort to using the PRINT statement after each line of code to indicate the completion of a particular statement. However, the PRINT statement has limitations. It is not immediately visible in SSMS unless the output buffer is full, which is typically around 8 KB.

Consider the following example:

PRINT 'SQLAuthority'+ REPLICATE(' ',8000)
PRINT 'Pinal'+ REPLICATE(' ',116)
WAITFOR DELAY '00:00:05'
PRINT 'Final Message'
	

When running this code, you will notice that the first PRINT statement is displayed immediately, while the second PRINT statement is only visible after a 5-second delay. This delay occurs because the output can only be seen once the WAITFOR statement is complete.

Now, imagine a scenario where you have a batch of code that includes multiple statements, such as an INSERT operation into tables, and you want to track the progress of each statement. In such cases, relying on the PRINT statement becomes unreliable.

The Solution

One solution to this problem is to use the RAISERROR statement instead of PRINT. The RAISERROR statement allows us to raise an error message with a specified severity level and state. By using the NOWAIT option, we can display the message immediately without waiting for the output buffer to fill up.

Consider the following code:

DECLARE @msg1 varchar(max) = 'SQLAuthority'+ REPLICATE(' ',8000)
DECLARE @msg2 varchar(max) = 'Pinal'+ REPLICATE(' ',116)
DECLARE @msg3 varchar(max) ='Final Message'

RAISERROR (@msg1, 0, 1) WITH NOWAIT
RAISERROR (@msg2, 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:05'
RAISERROR (@msg3, 0, 1) WITH NOWAIT
	

When executing the above code, you will observe that the first two messages are displayed immediately, followed by a 5-second delay, and finally, the last message is printed. The RAISERROR method provides a more sophisticated approach to returning status messages to the client compared to the PRINT statement.

Conclusion

By using the RAISERROR statement with the NOWAIT option, we can improve the display of status messages in SQL Server. This approach allows us to track the progress of code execution more effectively, especially when working with batches of code. The RAISERROR method provides immediate feedback to the client, making it a preferred choice over the PRINT statement.