Inside Microsoft® SQL Server™ 2005

Per-Row UDFs

Nondeterministic functions are functions that are not guaranteed to return the same output when invoked multiple times with the same input. When you invoke nondeterministic built-in functions in a query (such as RAND and GETDATE), those functions are invoked once for the whole query and not once per row. The only exception to this rule is the NEWID function, which generates a globally unique identifier (GUID). NEWID is the only nondeterministic built-in function that will be invoked once per row.

To demonstrate this behavior of nondeterministic functions, run the following code, which queries the Orders table in the Northwind database; invokes the functions RAND, GETDATE, and NEWID; and generates the output shown in abbreviated form ...