DETERMINISM IN SQL SERVER 2000

SQL Server 2000 has brought the concept of determinism to the 
forefront.
Now that SQL supports user-defined functions (UDFs), this concept is
important to SQL developers who develop UDFs.

All functions are either deterministic or nondeterministic.
Deterministic functions always return the same result from a specific 
set of input
values; nondeterministic functions, however, do not return the same 
result
from a specific set of input values. The DATALENGTH function is
deterministic. You can make a call to the SQL Server requesting the 
length of a
certain character string; regardless of the number of times you execute 
it,
the query always returns the same result.

SELECT DATALENGTH('This is a test')

GO

Whether you run it once or 1,000 times, the length is always 14
characters.

Conversely, the GETDATE() function is nondeterministic. Each time you
call this function, the result set changes.

SELECT GETDATE()

GO

-------------------------------------------------------

2001-12-12 22:13:17.080

SELECT GETDATE()

GO

-------------------------------------------------------

2001-12-12 22:13:17.100

----------------------------------------