October 21st, 2016 by
A deterministic or determinism system in physics and mathematics is a system that does not involve randomness in the development of the system’s future state. This implies that a deterministic model produces similar output from the initial state or starting condition.
Ideally, deterministic functions return similar results whenever they are called with particular input values set and same database state. They are different from non-deterministic functions which can return varying results every time with specific input values set even when they access a database in the same state. For instance, AVG function returns similar output as long as the above qualifications are maintained. However, GETDATE function returns the current value of date-time with different results all the time.
There are various properties of the user-defined functions on which SQL server database engine ability is based when it comes to indexing the function results. This can be through computed columns indexes that call a function or via indexed views that are the reference of a function. A function’s determinism is an example of such a property. For instance, clustered index is impossible to create on the basis of the view that a view references a non-deterministic function.
Determinism in built-in Functions
Determinism of a built-in function cannot be influenced. Every built-in function is either non deterministic or deterministic on the basis of its implementation by a SQL server. For instance, when an ORDER is specified by a clause in query, the function’s determinism is not changed from the way it is used in a query to a different way. The entire string in a built-in function is deterministic.
Deterministic or Determinism Examples
Physical laws that differential equations describe represent deterministic functions or systems although the system state at any given time can be hard to describe explicitly. Schrodinger equation in quantum mechanics, which describes time evolution continuation of the wave function of a system, is also deterministic. Nevertheless, the relationship between observable properties of a system and the wave function’s system seems non-deterministic.
In chaos theory, the studied systems are deterministic. Initially, their initial states are exact or known. This makes predicting their future state possible theoretically. Nevertheless, future state knowledge in practice has precision limit with the initial state being possible to measure. Strong dependence on their initial condition is a characteristic of a chaotic system. Other examples of deterministic or determinism systems include random walks and Markov chains.