Why are table valued parameters to SQL Server stored procedures required to be input READONLY?

In the presentation on Optimizing Microsoft SQL Server 2008 Applications Using Table Valued Parameters, XML, and MERGE by Michael Rys he says. (at 32:52)

Note that in SQL Server 2008 table valued parameters are read only. But as you notice we actually require you to write READONLY. So that actually then means that at some point in the future maybe if you say please, please please often enough we might be able to actually make them writable as well at some point. But at the moment they are read only.

Here is the connect item you should use to add your "please". Relax restriction that table parameters must be readonly when SPs call each other.

Srini Acharya made a comment on the connect item.

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 priorirites, 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.


Table-valued parameters have the following restrictions(source MSDN):

  • SQL Server does not maintain statistics on columns of table-valued parameters.
  • Table-valued parameters must be passed as input READONLY
    parameters to Transact-SQL routines. You cannot perform DML
    operations such as UPDATE, DELETE, or INSERT on a table-valued
    parameter in the body of a routine.
  • You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored procedure.

there are few options to over come this restriction one is 

CREATE TYPE RTableType AS TABLE(id INT, NAME VARCHAR )

go

CREATE PROCEDURE Rproc @Rtable RTABLETYPE READONLY,
                       @id     INT
AS
  BEGIN
      SELECT *
      FROM   @Rtable
      WHERE  ID = @id
  END

go

DECLARE @Rtable RTABLETYPE
DECLARE @Otable RTABLETYPE

INSERT INTO @Rtable
VALUES      (1,'a'),
            (2,'b')

INSERT @Otable
EXEC Rproc
  @Rtable,
  2

SELECT *
FROM   @Otable

through this you can get the table values out


With respect to (emphasis added):

So someone please provide me a good reason why TVPs were designed to be readonly input parameters.

I just posted a more detailed answer to this on DBA.StackExchange here:

READONLY parameters and TVP restrictions

But the summary of it goes like this:

According to this blog post ( TSQL Basics II - Parameter Passing Semantics ), a design goal of Stored Procedure OUTPUT parameters is that they merely mimic "by reference" behavior when the Stored Procedure completes successfully! But when there is an error that causes the Stored Procedure to abort, then any changes made to any OUTPUT parameters would not be reflected in the current value of those variables upon control returning to the calling process.

But when TVPs were introduced, they implemented them as truly passing by reference since continuing the "by value" model -- in which a copy of it is made to ensure that changes are lost if the Stored Procedure does not complete successfully -- would not be efficient / scalable, especially if a lot of data is being passed in through TVP.

So there is only one instance of the Table Variable that is the TVP, and any changes made to it within any Stored Procedure (if they were not restricted to being READONLY) would be immediately persisted and would remain, even if the Stored Procedure encountered an error. This violates the design goal stated at the beginning of this summary. And, there is no option for somehow tying changes made to a TVP to a transaction (even something handled automatically, behind the scenes) since table variables are not bound by transactions.

Hence, marking them as READONLY is the only way (at the moment) to maintain the design goal of Stored Procedure parameters such that they do not reflect changes made within the Stored Procedure unless: the parameter is declared as OUTPUT and the Stored Procedure complete successfully.

Tags:

Sql Server

Recent Posts