Hey Steve. This is not really about streaming data back to the application; it is really about streaming the results of a TVF back to the calling query, which is typically a Stored Proc or maybe a Job Step or maybe even a SSRS query.

I really can't think of any reason to not always do this since it keeps memory usage low and performs the same as passing back the entire collection all at once. I am not sure what SSMS does, but I think the delay you are seeing is just part of SQL Server as I have also seen that delay. I think this is where the query hint of "FAST number_rows" comes in as well as the WITH NOWAIT option of RAISERROR in terms of trying to get around that delay.

Take care,

Solomon...