User-Defined Function Design Guidelines
A user-defined function is stored as a database object providing reusable code that can be used in these ways:
In Transact-SQL statements such as SELECT
In applications calling the function
In the definition of another user-defined function
To parameterize a view or improve the functionality of an indexed view
To define a column in a table
To define a CHECK constraint on a column
To replace a stored procedure
Choosing the Type of Function
When designing a user-defined function, first determine the type of function that is best suited to your needs. Will the function:
Return a scalar (single value)
Return a table (multiple rows)
Perform a complex calculation
Primarily access SQL Server data
User-defined functions written in either Transact-SQL or .NET Framework can return both scalar and table values.
Properties of Functions
There are several properties of user-defined functions that determine the ability of the SQL Server Database Engine to index the results of the function, either through indexes on computed columns that call the function, or through indexed views that reference the function. These properties are also applicable to methods of CLR User-Defined Types.
Determinism
Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.
The Database Engine automatically analyzes the body of Transact-SQL functions and evaluates whether the function is deterministic. For example, if the function calls other functions that are non-deterministic, or if the function calls extended stored procedures, then the Database Engine marks the function as non-deterministic. For common language runtime (CLR) functions, the Database Engine relies on the author of the function to mark the function as deterministic or not using the SqlFunction custom attribute.
Precision
A user-defined function is said to be precise if it does not involve any floating point operations.
The Database Engine automatically analyzes the body of Transact-SQL functions and evaluates whether the function is precise. For CLR functions, the Database Engine relies on the author of the function to mark the function as precise or not using the SqlFunction custom attribute.
Data Access
This property indicates whether the function accesses the local database server using the SQL Server in-process managed provider. For more information, see Data Access from CLR Database Objects.
The Database Engine automatically analyzes the body of Transact-SQL functions and evaluates whether the function performs data access. For CLR functions, the Database Engine relies on the author of the function to indicate the data access characteristics using the SqlFunction custom attribute. This property is enforced at execution time by the Database Engine. If the function indicates that DataAccess = None, but performs data access, the function fails at execution time.
System Data Access
This property indicates whether the function accesses system metadata in the local database server using the SQL Server in-process managed provider.
The Database Engine automatically analyzes the body of Transact-SQL functions and evaluates whether the function performs system data access. For CLR functions, the Database Engine relies on the author of the function to indicate the system data access characteristics using the SqlFunction custom attribute. This property is enforced at execution time by the Database Engine. If the function indicates that SystemDataAccess = None, but performs system data access, the function fails at execution time.
IsSystemVerified
This property indicates whether the determinism and precision properties of the function are verifiable by the Database Engine. This property is true for Transact-SQL functions as long as they do not call any functions marked IsSystemVerified = false. The property is false for CLR functions.
The Database Engine automatically derives the IsSystemVerified property for functions. For Transact-SQL functions, if they access any function that is marked IsSystemVerified = false, then the function itself is marked IsSystemVerified = false.
For more about functions in indexed computed columns and indexed views, see Creating Indexes on Computed Columns, and Creating Indexed Views.