UDFs can be embedded in queries, constraints, and computed columns. The code that defines a UDF may not cause side effects that affect the database state outside the scope of the function–that is, the UDF’s code is not allowed to modify data in tables or to invoke a function that has side effects (for example, RAND). In addition, the UDF’s code can only create table variables and cannot create or access temporary tables. Also, the UDF’s code is not allowed to use dynamic execution.

When creating or altering a UDF, you can specify function options in the header. T-SQL UDFs support the ENCRYPTION and SCHEMABINDING options, which I described in the previous chapter when discussing views. Both T-SQL and CLR UDFs can be created with a new EXECUTE AS clause, which lets you define the security context of the execution of the function. This option is not available to inline table-valued UDFs. An inline table-valued UDF is very similar to a view with the exception that it can accept arguments. It consists of a single query that defines the table returned by the function. With scalar UDFs (both T-SQL and CLR), you can now also specify one of the options: RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT (the default). The former option tells SQL Server not to invoke the function at all if a parameter value is NULL; in this case, the function result will be NULL. The latter option tells SQL Server that you want it to invoke the function even when one of the input parameters is NULL.

It is a good practice to create all your UDFs with both the SCHEMABINDING and RETURNS NULL ON NULL INPUT options when it is the desired behavior. SCHEMABINDING will prevent dropping underlying objects and schema changes to referenced columns. RETURNS NULL ON NULL INPUT can improve the performance of your code by bypassing the function logic and returning NULL when one of the inputs is NULL. Where this is the desired behavior, and the option is used, it may eliminate the need to check for NULL input with explicit code in the body of the function. In the code samples in this chapter, for the sake of brevity, I will not specify these options.