NOTE This section gives you just a concise description of the Table Variable Deferred Compilation feature. The reason is twofold. First, the tests I have done with different forms of UDFs with a table variable show that only a small percentage of such functions benefit from the existence of the feature. Second, the Database Engine supports (and has for a long time) another feature with the same functionality: the OPTION(RECOMPILE) clause.
Generally, table variables do not have statistics. This means that the query optimizer of the Database Engine will assume that each table variable in a UDF has only a single row. Knowing that correctly estimating the number of input rows is one of the most important factors for the query optimizer to create an optimal execution plan, as discussed earlier in the chapter, you can appreciate that always using the fixed number without any calculation can cause all sorts of problems.
By delaying the compilation of the part of an execution plan that references a table variable, the Database Engine has a chance to capture the correct number of rows within the table variable, thus improving the execution plan for the following operators of that plan. Generally, if the table variable ends up containing a low number of rows, this assumption of a single row is not problematic. However, if the actual row count is higher, this can result in downstream inappropriate plan choices and significant performance disadvantages.
As stated at the beginning of this section, you can use the OPTION(RECOMPILE) clause to achieve the same functionality. (This option is the final part of the WHERE clause of a SELECT statement.) The clause forces the system to recompile the particular query every time it runs, but with correct row counts for table variables.
Generally, the functionality of OPTION(RECOMPILE) and the Table Variable Deferred Compilation feature is the same, but you need to remember that using OPTION(RECOMPILE) forces an execution query plan to be recompiled with every execution. The Table Variable Deferred Compilation feature does not have that problem, meaning that, once the code is compiled, the plan is cached away and can be reused like any other cached plan.
An additional benefit of this feature is that you do not have to change your code, as is the case with OPTION(RECOMPILE).
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.