Sniffing parameter on table parameters

Question

I'm sure adding sniffing parameter to table parameters is small or not important at all, but I was wondering if anyone could confirm this?

(INT_LIST is a user-defined table type, which is the only INT column)

CREATE PROCEDURE [dbo].[TVPSniffTest](
    @param1 varchar(50),
    @idList INT_LIST readonly
)
AS
BEGIN 
   DECLARE @param1_sniff VARCHAR(50) = @param1 --this is worth doing

   DECLARE @idList_sniff INT_LIST
   INSERT INTO @idList_sniff SELECT value FROM @idList --will this help?

   --query code here
END

Answer 1

This has no effect - it is actually bad for performance because you are copying the whole table first.

, . ; . sniffing - .

, , OPTIMIZE FOR RECOMPILE ( WITH RECOMPILE , ). .

Answer 2

, TVP . , , TVP ( ).

, , , , (.. TVP). , , , SELECT.

:

DECLARE @TableVariable TABLE (Col1 INT NOT NULL);

INSERT INTO @TableVariable (Col1)
  SELECT so.[object_id]
  FROM   [master].[sys].[objects] so;

-- Control-M to turn on "Include Actual Execution Plan".
-- For each of the 3 following queries, hover over the "Table Scan"
--   operator to see the "Estimated Number of Rows".

SELECT * FROM @TableVariable; -- Estimated Number of Rows = 1 (incorrect)

SELECT * FROM @TableVariable
OPTION (RECOMPILE); -- Estimated Number of Rows = 91 (correct)

SELECT * FROM @TableVariable; -- Estimated Number of Rows = 1 (back to incorrect)