I’ve grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It’s a fairly human look at what’s involved in sailing on a Trident missile submarine…
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.