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
User-defined functions have been expanded in Microsoft SQL Server 2005 to include functions written in any Microsoft .NET Framework supported programming language. In addition, some restrictions on using nondeterministic functions have been removed. These changes impact your decision on the type of user-defined function to design and implement.
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.
See Also
Concepts
Table-valued User-defined Functions
Inline User-defined Functions
Deterministic and Nondeterministic Functions
Rewriting Stored Procedures As Functions