Table Variable Deferred Compilation (SQL 2019)

--Check Version of the server
SELECT @@VERSION
/*
This feature is available only from SQL Server 2019 onward.
Microsoft SQL Server 2019 (RTM-GDR) (KB4517790) - 15.0.2070.41 (X64)   ...
*/
--Check Compatability of the Microsoft Demo Database - WideWorldImportersDW
SELECT Compatibility_Level
FROM sys.databases
WHERE name = 'WideWorldImportersDW'
GO
--By Default when you restore WideWorldImportersDW, Compat level is 130 as it was created from SQL 2016 onwards.
--WideWorldImportersDW is the Microsoft Demo DB used these days instead of AdventureWorks and is available for free download.
--Also available is the enlarge db script required to be run on the installed WideWorldImportersDW, for certain large data demos.
/*https://github.com/microsoft/sql-server-samples/blob/master/samples/features/intelligent-query-processing/IQP%20Demo%20Setup%20-%20Enlarging%20WideWorldImportersDW.sql*/
--MICROSOFT DEMO

-- ******************************************************** --
-- Table variable deferred compilation
-- ******************************************************** --

--Bring DB Compatability to SQL 2017 and clear the db plan cache.
USE [WideWorldImportersDW]
GO
ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

--Enable Exec Plan
DECLARE @Order TABLE 
([Order Key] BIGINT NOT NULL,
     [Quantity] INT NOT NULL
);

INSERT @Order
SELECT [Order Key], [Quantity]
FROM [Fact].[Order]
WHERE  [Quantity] > 99;

-- Look at estimated rows, speed, join algorithm
SET STATISTICS TIME ON 
SET STATISTICS IO ON
SELECT oh.[Order Key], oh.[Order Date Key],
   oh.[Unit Price], o.Quantity
FROM Fact.[Order] AS oh
INNER JOIN @Order AS o
ON o.[Order Key] = oh.[Order Key]
WHERE oh.[Unit Price] > 0.10
ORDER BY oh.[Unit Price] DESC;
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
GO
--You will see that the estimated rows from the table variable is only 1, because of which all downstream operations go in the wrong direction, using the nested loop join instead of hash match, etc.

--Now See the 2019 feature in action.
ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

DECLARE @Order TABLE 
([Order Key] BIGINT NOT NULL,
 [Quantity] INT NOT NULL
);

INSERT @Order
SELECT [Order Key], [Quantity]
FROM [Fact].[Order]
WHERE [Quantity] > 99;

-- Look at estimated rows, speed, join algorithm
SET STATISTICS TIME ON 
SET STATISTICS IO ON
SELECT oh.[Order Key], oh.[Order Date Key],
oh.[Unit Price], o.Quantity
FROM Fact.[Order] AS oh
INNER JOIN @Order AS o
ON o.[Order Key] = oh.[Order Key]
WHERE oh.[Unit Price] > 0.10
ORDER BY oh.[Unit Price] DESC;
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
GO
--Immediate improvement with actual rows from Table Variable being used in Plan. Join operation and Index operation also changed accordingly.

--We can keep the comptability 150 for the Server and disable this feature on specific db's if needed.

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON;

SELECT * FROM sys.database_scoped_configurations
GO

--OR Disable for specific Queries.
DECLARE @Order TABLE 
([Order Key] BIGINT NOT NULL,
 [Quantity] INT NOT NULL
);

INSERT @Order
SELECT [Order Key], [Quantity]
FROM [Fact].[Order]
WHERE [Quantity] > 99;

SET STATISTICS TIME ON 
SELECT oh.[Order Key], oh.[Order Date Key],
oh.[Unit Price], o.Quantity
FROM Fact.[Order] AS oh
INNER JOIN @Order AS o
ON o.[Order Key] = oh.[Order Key]
WHERE oh.[Unit Price] > 0.10 
ORDER BY oh.[Unit Price] DESC
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));
SET STATISTICS TIME OFF
GO

--Now, like highlighted at the very top, this is not all easy breeezy as it seems. For varying data workloads, since cached plans are used, Parameter Sniffing issues are introduced.

--Lets see.
CREATE OR ALTER PROC dbo.usp_TableVariableTest @Quantity INT AS
BEGIN

DECLARE @Order TABLE 
([Order Key] BIGINT NOT NULL,
 [Quantity] INT NOT NULL
)

INSERT @Order
SELECT [Order Key], [Quantity]
FROM [Fact].[Order]
WHERE [Quantity] > @Quantity

SELECT oh.[Order Key], oh.[Order Date Key],
oh.[Unit Price], o.Quantity
FROM Fact.[Order] AS oh
INNER JOIN @Order AS o
ON o.[Order Key] = oh.[Order Key]
WHERE oh.[Unit Price] > 0.10 
ORDER BY oh.[Unit Price] DESC
END
GO

SET STATISTICS TIME ON 
SET STATISTICS IO ON
EXEC dbo.usp_TableVariableTest @Quantity = 99
SET STATISTICS IO OFF
SET STATISTICS TIME OFF 
GO

--Uses cached plan and  estimates poorly.
SET STATISTICS TIME ON 
SET STATISTICS IO ON
EXEC dbo.usp_TableVariableTest @Quantity = 360
SET STATISTICS IO OFF
SET STATISTICS TIME OFF 
GO

--Bring DB Compatability to SQL 2017 and clear the db plan cache.
USE [WideWorldImportersDW]
GO
ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

--Create Proc with same logic as earlier, but with temp table.
CREATE OR ALTER PROC dbo.usp_TempTableTest @Quantity INT AS
BEGIN

CREATE TABLE #Order 
([Order Key] BIGINT NOT NULL,
 [Quantity] INT NOT NULL
)

INSERT #Order
SELECT [Order Key], [Quantity]
FROM [Fact].[Order]
WHERE [Quantity] > @Quantity

SELECT oh.[Order Key], oh.[Order Date Key],
oh.[Unit Price], o.Quantity
FROM Fact.[Order] AS oh
INNER JOIN #Order AS o
ON o.[Order Key] = oh.[Order Key]
WHERE oh.[Unit Price] > 0.10 
ORDER BY oh.[Unit Price] DESC
END
GO

SET STATISTICS TIME ON 
SET STATISTICS IO ON
EXEC dbo.usp_TempTableTest @Quantity = 99
SET STATISTICS IO OFF
SET STATISTICS TIME OFF 
GO

--With Temp Table, with varying data output and with 2019 feature off,
--this SP, makes the right estimations.
SET STATISTICS TIME ON 
SET STATISTICS IO ON
EXEC dbo.usp_TempTableTest @Quantity = 360
SET STATISTICS IO OFF
SET STATISTICS TIME OFF 
GO

/*Final Verdict, Feature is good for legacy code, if lots of table variables were used and we want improvement without making any code changes. If you are anyway designing new code, its more reliable to use Temporary Tables for varying data outputs/loads, and not rely on this feature, as feature resolves one issue but brings up another with parameter sniffing.*/
--Yasub