Inner Join (vs Inline TVF)
Let’s consider a query that combines data from multiple tables to produce the query plan.
The Hash Match (Inner Join) operator combines the results of the IndexScan (NonClustered) and the Clustered Index Scan operators that gets SupplierID and SupplierName by scanning a non-clustered index on the Suppliers table and StockItemName, SupplierID, and ColorID by scanning the clustered index on the StockItems table. This result set becomes input for the second usage of the operation and is combined with ColorName set from an index scan on the Colors table.
It’s important to note that the Hash Match (Inner Join) operator is a blocking operator as it requires SQL Server to gather data from each data set before it can perform the join.
Query plan after adding usable indexes to eliminate Hash Match (Inner Join) operators. In the new query plan, SQL Server replaces the Hash Match (Inner Join) operators with Nested Loops operators and replaces two of the index scan operations with Index Seek (NonClustered) operators which should significantly improve performance even when large tables are queried.
The Nested Loops operator is an efficient operation that compares two data sets row by row. For each row output by the top operator in the query plan (known as the inner data set), which is the index scan on the small Colors table, SQL Server scans the rows output by the bottom operator (known as the outer data set), which is the index seek on the StockItems table. Then the output of this operation becomes the inner data set for the second Nested Loops operator in the query plan. For each row in this new inner data set, SQL Server scans the output from the index seek on the Suppliers table.
Notice that the cost of the Nested Loops operators in the new query plan is significantly lower than the cost of the Hash Match (Inner Join) operators .
It is worth noting that you can also natively compile scalar user-defined functions (UDFs) and inline table-valued functions (TVFs) in SQL Server 2016 for more efficient data access. But there is difference.
An inline table-valued function (iTVF) is a table expression that can accept parameters, perform an action and provides as its return value, a table. The definition of an iTVF is permanently stored as a database object, similar to a view would. => https://www.sqlservercentral.com/articles/creating-and-using-inline-table-valued-functions.
-- Transact-SQL Inline Table-Valued Function Syntax CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] [ READONLY ] }[ ,...n ]] )
RETURNS TABLE [ WITH <function_option> [ ,...n ] ] [ AS ] RETURN [ ( ] select_stmt [ ) ] [ ; ]
-- Transact-SQL Function Clauses <function_option>::= { [ ENCRYPTION ] | [ SCHEMABINDING ] | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ] | [ EXECUTE_AS_Clause ] | [ INLINE = { ON | OFF }] } <table_type_definition>:: = ( { <column_definition> <column_constraint> | <computed_column_definition> } [ <table_constraint> ] [ ,...n ] ) <column_definition>::= { { column_name data_type } [ [ DEFAULT constant_expression ] [ COLLATE collation_name ] | [ ROWGUIDCOL ] ] | [ IDENTITY [ (seed , increment ) ] ] [ <column_constraint> [ ...n ] ] } <column_constraint>::= { [ NULL | NOT NULL ] { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor | WITH ( < index_option > [ , ...n ] ) [ ON { filegroup | "default" } ] | [ CHECK ( logical_expression ) ] [ ,...n ] } <computed_column_definition>::= column_name AS computed_column_expression <table_constraint>::= { { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] ( column_name [ ASC | DESC ] [ ,...n ] [ WITH FILLFACTOR = fillfactor | WITH ( <index_option> [ , ...n ] ) | [ CHECK ( logical_expression ) ] [ ,...n ] } <index_option>::= { PAD_INDEX = { ON | OFF } | FILLFACTOR = fillfactor | IGNORE_DUP_KEY = { ON | OFF } | STATISTICS_NORECOMPUTE = { ON | OFF } | ALLOW_ROW_LOCKS = { ON | OFF } | ALLOW_PAGE_LOCKS ={ ON | OFF } }
In inline TVFs, the TABLE return value is defined through a single SELECT statement. Inline functions do not have associated return variables. select_stmt Is the single SELECT statement that defines the return value of an inline table-valued function (TVF). EXECUTE AS cannot be specified for inline table-valued functions.
INLINE = { ON | OFF } APPLIES TO: SQL Server 2019 and later. Specifies whether this scalar UDF should be inlined or not. This clause applies only to scalar user-defined functions. The INLINE clause is not mandatory. If INLINE clause is not specified, it is automatically set to ON/OFF based on whether the UDF is inlineable. If INLINE=ON is specified but the UDF is found to be non-inlineable, an error will be thrown.
The input from Kevin- trianglessug: Inline table-valued functions are called that because the execution plan can treat them as essentially a derived table, rather than, for example, scalar functions (which happen one per row) and multi-statement table-valued functions (which have potentially several independent calls).
Example:
Answer:
