Quote:A deterministic function will return the same result when it is called with the same set of input parameters. Adding two numbers together is an example of a deterministic function. A nondeterministic function, on the other hand, may return different results every time they are called with the same set of input values. Even if the state of the data in the database is the same, the results of the function might be different. The GETDATE function, for example, is nondeterministic. One caveat of almost all nondeterministic functions is that they are executed once per statement, not once per row. If you query 90,000 rows of data and use the RAND function to attempt to produce a random value for each row you will be disappointed; SQL Server will only generate a single random number for the entire statement. The only exception to this rule is NEWID, which will generate a new GUID for every row in the statement. When we create a function, SQL Server will analyze the code we've created and evaluate whether the function is deterministic. If our function makes calls to any nondeterministic functions, it will, itself, be marked as nondeterministic. SQL Server relies on the author of a SQL CLR function to declare the function as deterministic using an attribute. Deterministic functions can be used in indexed views and computed columns whereas nondeterministic functions cannot.
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)