In my previous post, we have gone through the Table Valued Parameter, so now this is the time to come up with its limitation. Below are the limitation of Table Valued Parameter :-
· SQL Server does not maintain statistics on the Table Valued Parameter Columns.
· Table-valued parameters are read-only in Transact-SQL code. We cannot update the column values in the rows of a table-valued parameter and we cannot insert or delete rows. To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, we must insert the data into a temporary table or into a table variable.
· Table type variable cannot perform
“INSERT INTO @TVP EXEC <procedure>” AND
“SELECT * INTO @TVP <Table Name>” statement.
· Table Valued Parameter feature is only available on SQL Server 2008 and above.
· We cannot pass table-valued parameters to CLR user-defined functions.
· Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints.
· We cannot use ALTER TABLE statements to modify the design of table-valued parameters. The only way to change the table definition is to drop the type and recreate it.
· A user-defined table type cannot be used as a column in a table or a field in a structured user-defined type.
· A DEFAULT value cannot be specified in the definition of a user-defined table type.· User-defined functions cannot be called within the definition of computed columns of a user-defined table type.
· We cannot use a Table valued parameters as OUTPUT parameters, for example,
“CREATE PROCEDURE proc_TableValueParam (@p_TVP userDefinedTableType OUTPUT)”.
· We cannot perform DML operations like UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
No comments:
Post a Comment