Commit 476f169

ms.date: "10/11/2018"

Table variable deferred compilation can be disabled at the database or statement scope while still maintaining database compatibility level 150 and higher. To disable table variable deferred compilation for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;

To re-enable table variable deferred compilation for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON;

You can also disable table variable deferred compilation for a specific query by designating DISABLE_DEFERRED_COMPILATION_TV as a USE HINT query hint. For example:

DECLARE @LINEITEMS TABLE

(L_OrderKey INT NOT NULL,

L_Quantity INT NOT NULL

);

INSERT @LINEITEMS

SELECT L_OrderKey, L_Quantity

FROM dbo.lineitem

WHERE L_Quantity = 5;

SELECT O_OrderKey,

O_CustKey,

O_OrderStatus,

L_QUANTITY

FROM

ORDERS,

@LINEITEMS

WHERE O_ORDERKEY = L_ORDERKEY

AND O_OrderStatus = 'O'

OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));