RAISERROR WITH NOWAIT not so immediate?

Question

I previously asked a question on how to do a PRINT that gives output immediately while the rest of the script is still running (See: How to see progress of running SQL stored procedures?). The simple answer is to use:

RAISERROR ('My message', 0, 1) WITH NOWAIT

However, I noticed that the returned output is not always immediate, especially when it returns a lot of results. As a simple experiment, consider the following script:

DECLARE @count INT
SET @count = 1

WHILE @count <= 5000
BEGIN
    RAISERROR ('Message %d', 0, 1, @count) WITH NOWAIT
    WAITFOR DELAY '00:00:00.01'
    SET @count = @count + 1
END

The above script will spits out 5000 lines of text. If you run the script, you will notice:

• For the first 500 lines (1 - 500 lines), it returns each output line immediately.
• For the next 500 lines (501 - 1000 lines), it returns the output once every 50 lines. (All 50 lines will be batched together and returned only at the end of each 50th line.)
• For every line after that (1001 - * lines), it returns the output once every 100 lines. (All 100 lines will be batched together and returned only at the end of each 100th line.)

So that means after the first 500 lines, the RAISERROR WITH NOWAIT no longer work as expected, and cause problems to me because I want to see the progress of my very long running script.

So my question: Is there any way to disable this 'batched' behaviour and make it always return immediately?

---

EDIT: I'm using SSMS (SQL Server Management Studio) to run the above script . It seems to affect all versions (both SSMS and SQL Server), and whether the output is set to "Results to Text" or "Results to Grid" makes no difference.

EDIT: Apparently, this batched behaviour happens after 500 lines, regardless of the number of bytes. So, I've updated the question above accordingly.

EDIT: Thanks to Fredou for pointing out that this is an issue with SSMS and third party tools like LinqPad will not have this issue.

However, I found out that LinqPad doesn't output immediately either when you have table results in the output. For example, consider the following code:

RAISERROR ('You should see this immediately', 0, 1) WITH NOWAIT
SELECT * FROM master.sys.databases
RAISERROR ('You should see this immediately too, along with a table above.', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:05'
RAISERROR ('You should see this 5 seconds later...', 0, 1) WITH NOWAIT

When you run the above script in LinqPad, only the first line is output immediately. The rest will only output after 5 seconds...

So, anyone know of a good light-weight alternative to SSMS that is free, does not require installation and will work with immediate outputs of RAISERROR WITH NOWAIT mixed with table results?

Answer 1

It would appear this works in 2008R2 if you set results to text.