I ran across this post on SQL Performance about the ‘RETURNS NULL ON NULL INPUT’ option for creating a user-defined function.
With this test function, that adds one to the input value:
CREATE OR ALTER FUNCTION dbo.AddOne( @InputValue int ) RETURNS int AS BEGIN RETURN @InputValue + 1; END GO
Calling with 1 as an input value returns 2, but using NULL as an input returns NULL. Since we know NULL input returns NULL, we can add an option to the function that will allow us to short circuit in these instances.
CREATE OR ALTER FUNCTION dbo.AddOne( @InputValue int ) RETURNS int WITH RETURNS NULL ON NULL INPUT AS BEGIN RETURN @InputValue + 1; END GO
This is a simple function, but the short circuit could save a bit of time if our function were more complex.
‘CALLED ON NULL INPUT’ would be the default option, where the function code will execute with a NULL input value.
There are some other options that can be used in the WITH section as well as ‘RETURNS NULL ON NULL INPUT’.
ENCRYPTION: This will prevent the function definition from being viewed in object explorer or from object queries (unless the user has heightened permissions). This is useful if one wants to protect intellectual property or wants to prevent some sort of reverse engineering.
SCHEMABINDING: This will prevent any objects referenced by the query from changing, without first dropping or altering the function. This will prevent changes being made that could affect the logic of the function, without first checking the function.
EXECUTE AS: We can provide a user account to be used for database object permissions on execution.
INLINE (ON or OFF): For scalar functions (That return a single value per call) there is an option to inline the function. Inlining allows the function call to be made in a set based way, instead of the usual row-by-row execution that functions have used.
Links:
Microsoft – Create Function