SQL 2016 Table Types.sql

-- v2 with table type
USE tempdb
GO

-- DROP SCHEMA IF EXISTS agg
IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = 'agg' )
	EXEC('CREATE SCHEMA agg')
GO

DROP PROC IF EXISTS agg.usp_test1
DROP PROC IF EXISTS agg.usp_test2
DROP PROC IF EXISTS agg.usp_test3
DROP PROC IF EXISTS agg.usp_test4

DROP TABLE IF EXISTS agg.DataMin
DROP TABLE IF EXISTS agg.DataMedian
DROP TABLE IF EXISTS agg.DataWeightedAverage
GO

CREATE TABLE agg.DataMin ( x INT PRIMARY KEY )
CREATE TABLE agg.DataMedian ( x INT PRIMARY KEY )
CREATE TABLE agg.DataWeightedAverage ( x INT PRIMARY KEY )
GO

DROP TYPE IF EXISTS agg.resultset
CREATE TYPE agg.resultset AS TABLE ( x INT PRIMARY KEY )
GO


INSERT INTO agg.DataMin ( x )
SELECT object_id FROM sys.all_objects

INSERT INTO agg.DataMedian ( x )
SELECT object_id FROM sys.all_objects WHERE type = 'P'

INSERT INTO agg.DataWeightedAverage ( x )
SELECT object_id FROM sys.all_objects WHERE type = 'X'
GO

/*
-- Are there some situations when it wouldn't...
DECLARE @AggFunction INT = 1

;WITH AggData AS
(
   SELECT * FROM agg.DataMin WHERE @AggFunction = 1 
   UNION ALL
   SELECT * FROM agg.DataMedian WHERE @AggFunction = 2 
   UNION ALL
   SELECT * FROM agg.DataWeightedAverage WHERE @AggFunction = 3
)
SELECT *
FROM AggData
OPTION ( RECOMPILE )
GO
*/

-- Test 1; CTE no recompile
CREATE PROC agg.usp_test1

	@AggFunction INT

AS

	DECLARE @resultset AS agg.resultset

	;WITH AggData AS
	(
	   SELECT x FROM agg.DataMin WHERE @AggFunction = 1 
	   UNION ALL
	   SELECT x FROM agg.DataMedian WHERE @AggFunction = 2 
	   UNION ALL
	   SELECT x FROM agg.DataWeightedAverage WHERE @AggFunction = 3
	)
	INSERT INTO @resultset ( x )
	SELECT x
	FROM AggData


	SELECT *
	FROM @resultset

RETURN
GO


-- Test 2; CTE WITH recompile
CREATE PROC agg.usp_test2

	@AggFunction INT

AS

	DECLARE @resultset AS agg.resultset

	;WITH AggData AS
	(
	   SELECT x FROM agg.DataMin WHERE @AggFunction = 1 
	   UNION ALL
	   SELECT x FROM agg.DataMedian WHERE @AggFunction = 2 
	   UNION ALL
	   SELECT x FROM agg.DataWeightedAverage WHERE @AggFunction = 3
	)
	INSERT INTO @resultset ( x )
	SELECT x
	FROM AggData
	OPTION ( RECOMPILE )


	SELECT *
	FROM @resultset

RETURN
GO



-- Test 3; IF THEN statement no recompile
CREATE PROC agg.usp_test3

	@AggFunction INT

AS

	DECLARE @resultset AS agg.resultset

	IF @AggFunction = 1
	   INSERT INTO @resultset ( x ) SELECT x FROM agg.DataMin
	ELSE IF @AggFunction = 2
	   INSERT INTO @resultset ( x ) SELECT x FROM agg.DataMedian
	ELSE IF @AggFunction = 3
	   INSERT INTO @resultset ( x ) SELECT x FROM agg.DataWeightedAverage
	ELSE
		RAISERROR( 'Unknown value for parameter @AggFunction (%i).', 16, 1, @AggFunction )


	SELECT *
	FROM @resultset

RETURN
GO


-- Test 3; IF THEN statement WITH statement-level recompile; could be proc-level recompile
--	in this simple example
CREATE PROC agg.usp_test4

	@AggFunction INT

AS

	DECLARE @resultset AS agg.resultset

	IF @AggFunction = 1
	   INSERT INTO @resultset ( x ) SELECT x FROM agg.DataMin OPTION ( RECOMPILE )
	ELSE IF @AggFunction = 2
	   INSERT INTO @resultset ( x ) SELECT x FROM agg.DataMedian OPTION ( RECOMPILE )
	ELSE IF @AggFunction = 3
	   INSERT INTO @resultset ( x ) SELECT x FROM agg.DataWeightedAverage OPTION ( RECOMPILE )
	ELSE
		RAISERROR( 'Unknown value for parameter @AggFunction (%i).', 16, 1, @AggFunction )

	
	SELECT *
	FROM @resultset
	 OPTION ( RECOMPILE )

RETURN
GO



-- Test 1; CTE no recompile
-- RESULT: startup expressions in Filter operators allow only 1 table to be scanned but other two are present in the plan.
-- Slightly more complex plan.
EXEC agg.usp_test1 3

-- Test 2; CTE WITH recompile
-- RESULT: Nice, simple plan where only one index scan is present.
EXEC agg.usp_test2 3

-- Test 1.1; proc does not have recompile; proc execute WITH RECOMPILE
-- RESULT: as per test 1
EXEC agg.usp_test1 3
WITH RECOMPILE


-- Test 3; IF THEN statement no recompile
-- RESULT:  Nice, simple plan where only one index scan is present; estimated rowcount for resultset is 1.
EXEC agg.usp_test3 3


-- Test 4; IF THEN statement with statement recompile
-- RESULT: Estimated rowcount for resultset is 160.
EXEC agg.usp_test4 3


-- Test 3.1; IF THEN statement; proc execute WITH RECOMPILE
-- RESULT: Estimated rowcount for resultset is 1.
EXEC agg.usp_test3 3
WITH RECOMPILE