Deterministic and Nondeterministic Function in SQL Server

When working with functions, it’s important to know whether the function you are using is deterministic or nondeterministic. Deterministic function return, for the same set of input values, the same value every time you call them. The SQL Server built-in functions COS, which returns he trigonometric cosine of the specified angle in an example of a deterministic function. In contrast a nondeterministic function can return a different result every time you call it. An example of a nondeterministic function is the GETDATE (), which returns the current system time and date. SQL Server also considers a function nondeterministic if the function calls a nondeterministic function or if the function calls an extended stored procedure.

Whether a function is deterministic or not also determines whether you can build an index on the result the function returns and whether you can define a clustered index on a view that references the function. If the function is nondeterministic, you cannot index the results of the function, either through indexes on computed columns that call the function or through indexed views that reference the function.

Keywords: function in SQL Server , Deterministic function ,Non Deterministic function ,SQL Server 2008 ,SQL server 2005