Seeder question: I have read in BOL that some functions are deterministic, and some functions are non-deterministic? What does this mean and what is an example of each? Are there any functions that are both?
A deterministic function is one where you can be sure of the output based on input (in a particular database). So if you call the DAY function on a particular field, it will return you the numerical day for the date parameter passed in. DAY( '1-1-2001') always returns 1.
A non-deterministic function is one that you can't predict the output. So RAND (with no seed) is a good example, but GETDATE(), @@CPU_BUSY, and similar functions that can't be predicted based on the input.
Some functions can be either, depending on how they are used. Here's a good reference from Books Online: Deterministic and Nondeterministic Functions
A deterministic function will always return the same result given the same arguments, non-deterministic functions will vary each time they are called. For example a function that adds 1 to the argument passed in would always return 8, if the argument was 7 - a deterministic function. On the other hand, the GETDATE() Function returns a different value each time called, so it's non-deterministic.
A function can't be both, but GETDATE() is a function that will be treated as ok to use in user-defined functions because the system gets what GETDATE() does. It doesn't change with system settings, session options, etc.
