Table variable deferred compilation

Similar to multistatement table-valued functions, table variables have the limitation that they do not support optimizer statistics and just provide one-row guess estimates. Table variable deferred compilation provides a solution to this problem. As its name suggests, this feature defers the optimization of a statement that references a table variable. By using table variable deferred compilation, the query processor can use the actual cardinality instead of the original guess of one.

Let’s translate our multistatement table-valued function example to a table variable using the following code. Since the scope of a table variable is a batch, you will need to run all three next statements, DECLARE, INSERT and SELECT, at the same time:

DECLARE @Sales TABLE (

 SalesOrderID int,

 SalesOrderDetailID int,

 CarrierTrackingNumber nvarchar(25),

 OrderQty smallint,

 ProductID int,

 SpecialOfferID int,

 UnitPrice money,

...