Table-Value and Temp Tables

Table-Value Parameters
Table-value parameters offer more flexibility and better performance than temporary tables to pass a list of parameters. Table-value parameters do not acquire locks for the initial population of data from a client and do not cause a statement to recompile.

Table-value parameters offer the following benefits:
·Provide a simple programming model.
·Enable inclusion of complex business logic in a single routine.
·Reduce round trips to the server.
·Include a table structure of different cardinality.
·Enable strongly typed and set-oriented queries.
·Enable the client to specify sort order and unique keys.

Table-value parameters have the following restrictions:
·Statistics are not maintained on columns of table-value parameters.
·Table-value parameters must be passed as input READONLY parameters to Transact-SQL routines.
·DML operations such as UPDATE, DELETE, or INSERT cannot be performed on a table-value parameter in the body of a routine.
·Table-value parameters cannot be used as the target of a SELECT INTO or INSERT EXEC statement. Table-value parameters can be in the FROM clause of SELECT INTO, or in the INSERT EXEC string or stored procedure.

Source: http://bit.ly/3rmMLK