1. Deterministic functions :
Deterministic functions always result in the same output every time they are called with a fixed set of input values and given the same condition of the database. For example, AVG() function always results the same result given the qualifications stated above.
2. Nondeterministic functions :
Nondeterministic functions result in different output each time they are called with a fixed set of input values even if the database state that they access remains the same. For example, GETDATE() function, results the current date and time value, always a different value.
Built-in Function Determinism :
You cannot impact the determinism of any built-in function. The built-in function might be deterministic or nondeterministic based on the property of the function, implemented by SQL Server. For example, using an ORDER BY clause in any query do not change the determinism of the function that is used in the query.
Deterministic built-in functions are :
The below functions are not always deterministic but could be deterministic when they are specified in a deterministic manner:
- CAST is deterministic until used with DateTime, smalldatetime, or sql_variant.
- ISDATE is deterministic only in case used with CONVERT function.
- CONVERT is deterministic until one of these conditions exists.
- Source type may be sql_variant.
- Target type may sql_variant & its source type is nondeterministic.
Nondeterministic built-in functions are :
|@@MAX_CONNECTIONS||NEXT VALUE FOR|
- SQL Server Mathematical functions (SQRT, PI, SQUARE, ROUND, CEILING & FLOOR)
- SQL | Functions (Aggregate and Scalar Functions)
- SQL | Difference between functions and stored procedures in PL/SQL
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- Mean and Mode in SQL Server
- Copy tables between databases in SQL Server using Import-and-Export Wizard
- Difference between MySQL and MS SQL Server
- Difference between Couchbase and MS SQL Server
- Create, Alter and Drop schema in MS SQL Server
- Difference between MS SQL Server and CouchDB
- Difference between MS SQL Server and IBM DB2
- Difference between Redis and MS SQL Server
- Full join and Inner join in MS SQL Server
- Left join and Right join in MS SQL Server
- Difference between Neo4j and MS SQL Server
- Self Join and Cross Join in MS SQL Server
- Union and Union All in MS SQL Server
- SIN() and COS() Function in SQL Server
- varchar, varchar(max) and nvarchar in MS SQL Server
- text, ntext, binary, varbinary and varbinary(max) in MS SQL Server
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.