SQL Server marks a function as deterministic when it returns the same result any time it is called with a spesific set of input values. it also marks a function nondeterministic when it returns different results each time it is called with a specific set of input values. You can create an index on a computed column if a function is deterministic. This means whenever the row is updated, the index will be updated as well, and you could gain a lot of query performance when using the function in a query expression.
SQL Server user-defined functions are deterministic when they're the following:- Schema bound- Defined with only deterministic user-defined or built-in functions. The following are the example of deterministic functions :
- DATEDIFF
- ABS
- CEILING
- DATALENGTH
- ISNUMERIC
- ROUND
- ISNULL
- NULLIF
- FLOOR
The following are the example of nondeterministic functions :
- GETDATE
- NEWID
- RAND
- @@CONNECTIONS
- @@TOTAL_WRITE
- @@TOTAL_READ
- @DBTS
- @@IDLE
Calling Extended Stored Procedures from functions
Functions calling extended stored procedures on SQL Server are nondeterministic, because the extended stored procedures can cause side effects on the database. Side effects are changes to a global state of the database, such as an update to a table, or to an external resource, such as a file or the network. For example, modifying a file or sending an e-mail message. You should not rely on returning a consistent result set when executing an extended stored procedure from a user-defined function. User-defined functions that create side effects on the database are not recommended.
No comments:
Post a Comment