Do you know what determinism is? It's something that comes up periodically in Books Online as various SQL objects require deterministic functions. Things like indexes views.

I ran across a question on this for our ASK site, and thought it was interesting. I started to reply, then had to double check myself and make sure by looking things up in Books Online. I was right, though I'm not sure I'd have been able to actually classify all functions correctly.

For example, RAND can be either deterministic or non-deterministic, depending on how it's used. If you have a seed value, it's deterministic. CAST and CONVERT are usually deterministic but it depends on the data type. There are some good exceptions spelled out in this BOL entry: Deterministic and Nondeterministic Functions

I knew that deterministic meant you could determine the output given the input, and that these are "predictable" functions. However I didn't realize that somethings, like GetDATE() wouldn't fit. It always returns the current time/date, but it's not based on input, or the database, so it doesn't count. It's a non-deterministic function.

Non-deterministic functions like the @@ ones (@@Connections, @@idle, etc.) are listed, and I'm not sure I'd have thought of them off the top of my head, but it was good to read them in the article. Next time I need to I should remember, or at least be able to easily scan code and decide if it's deterministic or not.