Why must TVPs be READONLY, and why can't parameters of other types be READONLY

The explanation seems to be tied to a combination of: a) a detail from the linked blog that was not mentioned in this question, b) the pragmatics of TVPs fitting within how parameters have always been passed in and out, c) and the nature of table variables.

1. The missing detail contained in the linked blog post is exactly how variables are passed in and out of Stored Procedures and Functions (which relates to the phrasing in the Question of "a safer version of pass-by-reference if they are OUTPUT parameters"):

   TSQL uses a copy-in/copy-out semantics to pass parameters to stored procedures and functions....

   ...when the stored proc finishes executing (without hitting an error) a copy-out is made which updates the parameter passed in with any changes that were made to it in the stored proc.

   The real benefit of this approach is in the error case. If an error occurs in the middle of a stored procedure's execution, any changes made to parameters will not propagate back to the caller.

   If the OUTPUT keyword is not present no copy-out is made.

   The bottom line:
   Parameters to stored procs never reflect the partial execution of the stored proc if it encountered an error.

   Part 1 of this puzzle is that parameters are always passed "by value". And, it is only when the parameter is marked as OUTPUT and the Stored Procedure completes successfully that the current value is actually sent back. If OUTPUT values were truly passed "by reference", then the pointer to the location in memory of that variable would be the thing that was passed, not the value itself. And if you do pass in the pointer (i.e. memory address) then any changes made are immediately reflected, even if the next line of the Stored Procedure causes an error and it aborts execution.

   To sum up Part 1: variable values are always copied; they are not referenced by their memory address.

2. With Part 1 in mind, a policy of always copying variable values can lead to resource issues when the variable being passed in is quite large. I have not tested to see how blob types are handled (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), XML, and those that should not be used anymore: TEXT, NTEXT, and IMAGE), but it is safe to say that any table of data being passed in could be quite large. It would make sense for those developing the TVP feature to desire a true "pass-by-reference" ability to prevent their cool new feature from destroying a healthy number of systems (i.e. to want a more scalable approach). As you can see in the documentation that is what they did:

   Transact-SQL passes table-valued parameters to routines by reference to avoid making a copy of the input data.

   Also, this memory management concern was not a new concept since it can be found in the SQLCLR API that was introduced in SQL Server 2005 (TVPs were introduced in SQL Server 2008). When passing NVARCHAR and VARBINARY data into SQLCLR code (i.e. input parameters on the .NET methods within a SQLCLR Assembly), you have the option to go with the "by value" approach by using either SqlString or SqlBinary respectively, or you can go with the "by reference" approach by using either SqlChars or SqlBytes respectively. The SqlChars and SqlBytes types allow for full streaming of the data into the .NET CLR such that you can pull small chunks of large values as opposed to copying an entire 200 MB (up to 2 GB, right) value.

   To sum up Part 2: TVPs, by their very nature, would have a propensity to consume a lot of memory (and hence deteriorate performance) if staying within the "always copy the value" model. Hence TVPs do a true "pass by reference".

3. The final piece is why Part 2 matters: why would passing in a TVP truly "by reference" instead of making a copy of it change anything. And that is answered by the design goal that is the basis for Part 1: Stored Procedures that do not complete successfully should not alter, in any way, any of the input parameters, whether they are marked as OUTPUT or not. Allowing DML operations would have an immediate affect on the value of the TVP as it exists in the calling context (since passing by reference means you are changing the thing that was passed in, not a copy of what was passed in).

   Now, someone, somewhere, is at this point probably talking to their monitor saying, "Well, just build in an automagic facility for rolling back any changes made to TVP parameters if any were passed into the Stored Procedure. Duh. Problem solved." Not so fast. This is where the nature of Table Variables comes in: changes made to Table Variables are not bound by Transactions! So there is no way to roll the changes back. And in fact, this is a trick used to save info generated within a transaction if there needs to be a rollback :-).

   To sum up Part 3: Table-Variables do not allow for "undoing" changes made to them in the case of an error that causes the Stored Procedure to abort. And this violates the design goal of having parameters never reflecting the partial execution (Part 1).

Ergo: the READONLY keyword is needed to prevent DML operations on TVPs since they are Table Variables that are actually passed "by reference", and hence any modifications to them would be immediately reflected, even if the Stored Procedure encounters an error, and there is no other way to prevent that.

Additionally, parameters of other datatypes can't use READONLY because they are already copies of what was passed in, and so it wouldn't protect anything that isn't already protected. That, and the way that parameters of the other datatypes work was intended to be read-write, so it would probably be even more work to alter that API to now include a read-only concept.

_{Community Wiki answer generated from a comment on the question by Martin Smith}

There is an active Connect item (submitted by Erland Sommarskog) for this:

Relax restriction that table parameters must be readonly when SPs call each other

The only response by Microsoft so far says (emphasis added):

Thanks for the feedback on this. We have received similar feedback from a large number of customers. Allowing table valued parameters to be read/write involves quite a bit of work on the SQL Engine side as well as client protocols. Due to time/resource constraints as well as other priorities, we will not be able to take up this work as part of SQL Server 2008 release. However, we have investigated this issue and have this firmly in our radar to address as part of the next release of SQL Server. We appreciate and welcome the feedback here.

Srini Acharya
Senior Program Manager
SQL Server Relational Engine