Inline & Scalar functions

Inline User-Defined Functions

Inline user-defined functions are a subset of user-defined functions that return a table . Inline functions can be used to achieve the functionality of parameterized views.

Consider this view:

You can create a more generalized version, vw_CustomerNamesInRegion , by replacing the WHERE Region = 'WA' with a WHERE Region = @RegionParameter and letting users specify the region they are interested in viewing. Views, however, do not support parameters in the search conditions specified in the WHERE clause.

Inline user-defined functions can be used to support parameters in the search conditions specified in the WHERE clause. This is an example of a function that allows users to specify the region in their select:

Inline user-defined functions follow these rules:

• The RETURNS clause contains only the keyword table . You do not have to define the format of a return variable because it is set by the format of the result set of the SELECT statement in the RETURN clause.
• There is no function_body delimited by BEGIN and END.
• The RETURN clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.

Inline functions can also be used to increase the power of indexed views. The indexed view itself cannot use parameters in its WHERE clause search conditions to tailor the stored result set to specific users. You can, however, define an indexed view that stores the complete set of data that matches the view, and then define an inline function over the indexed view that contains parameterized search conditions that allow users to tailor their results. If the view definition is complex, most of the work performed to build a result set involves operations such as building aggregates or joining several tables when the clustered index is created on the view. If you then create an inline function that references the view, the function can apply the user's parameterized filters to pull specific rows from the result set that was built by the CREATE INDEX statement. The complex aggregations and joins are done once, at CREATE INDEX time, and all subsequent queries referencing the inline function filter rows from the simplified, stored result set.

Scalar functions

Operate on a single value and then return a single value. Scalar functions can be used wherever an expression is valid. This table categorizes the scalar functions.