Database Reference
In-Depth Information
■
While table variables can outperform temporary tables in some cases, due to their lower overhead, you
need to be extremely careful with them. especially when you store large amounts of data in the table variable. the single
row cardinality estimation rule and missing statistics can produce highly inefficient plans with a large number of rows
involved. a statement level recompile can help address cardinality estimation issues, although it will not help when the
data distribution needs to be analyzed.
Caution
As a general rule of thumb, it is safer to use temporary tables rather than table variables when you need to join
them with other tables. This is the case except when you are dealing a with very small number of rows, and table
variables limitations would not create suboptimal plans.
Table variables are a good choice when you need to deal with a large number of rows when no joins with other
tables are involved. For example, you can think about the stored procedure where you stage the data, do some
processing, and return the data to the client. If there is no other choice but to scan the entire table, you will have the
same execution plans, which scan the data, regardless of what object types are used. In these cases, table variables can
outperform temporary tables. Finally, table variables are cached in the same way as temporary tables.
User-Defined Table Types and Table-Valued Parameters
You can define table types in the database. When you declare the variable of the table type in the code, it works the
same way as with table variables.
Alternatively, you can pass the variables of the table types, called
table-valued parameters
, to T-SQL modules.
While table-valued parameters are implemented as table variables under the hood, they are actually read-only.
You cannot insert, update, or delete the data from table-valued parameters.
The code in Listing 12-13 shows how you can use table-valued parameters. It creates the table type
dbo.tvpErrors
,
calls the stored procedure with a table-valued parameter, and uses this parameter in dynamic SQL.
Listing 12-13.
Table-valued parameters
create type dbo.tvpErrors as table
(
RecId int not null,
[Error] nvarchar(512) not null,
primary key(RecId)
)
go
create proc dbo.TvpDemo
(
@Errors dbo.tvpErrors readonly
)
as
select RecId, [Error] from @Errors
exec sp_executesql
N'select RecId, [Error] from @Err'
,N'@Err dbo.tvpErrors readonly'
,@Err = @Errors
go
