• Not very scientific, but I found that the first time I ran, the result from the @tableparams was considerably faster when selecting approx 17,000 rows from 682,000, where there is no useful index.

    (17234 row(s) affected)

    SQL Server Execution Times:

    CPU time = 2250 ms, elapsed time = 8095 ms.

    -- this one was parameterised

    (17234 row(s) affected)

    SQL Server Execution Times:

    CPU time = 515 ms, elapsed time = 1784 ms.

    -- this using the table variable

    Running exactly the same thing a second time the difference was greatly reduced.

    (17234 row(s) affected)

    SQL Server Execution Times:

    CPU time = 343 ms, elapsed time = 958 ms.

    (17234 row(s) affected)

    SQL Server Execution Times:

    CPU time = 314 ms, elapsed time = 264 ms.

    Using a different table, the paraemterised query was quicker on the second run.