--Check Version of the server
SELECT @@VERSION
/*
Microsoft SQL Server 2019 (RTM-GDR) (KB4517790) - 15.0.2070.41 (X64)
Oct 28 2019 19:56:59 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit)
on Windows 10 Pro 10.0 X64 (Build 18363: )
*/

--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.

--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

--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

--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;

-- Look at estimated rows, speed, join algorithm
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

--PROBLEM!!! :)
--Parameter Sniffing, when parameters have large variations in output, resulting in poor plan choices.

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, where lots of table variables were used,
--but if you are designing new code, better to use Temp Tables for varying data outputs/loads,
--and not rely on this feature, as it resolves one issue but brings up another with paramter sniffing
--and poor estimations.

--Yasub