User Defined Functions (UDF)

Today I'll be working with User Defined Functions (UDF) in T-SQL. Microsoft defines User Defined Functions as "routines that perform calculations/computations and return a value - scalar (singular) or a table". That sounds simple enough. UDF's can be developed using T-SQL or .NET languages in the CLR. CLR UDFs are good for tasks that T-SQL cannot perform efficiently like procedural logic, complex calculations, string manipulation, etc... Though .NET should be avoided when the task is mainly set-based.

I'm specifically looking at nondeterministic functions. They are functions that are not guaranteed to return the same output when invoked multiple times with the same input. Built in nondeterministic functions like RAND and GETDATE are invoked once for the whole query and not once per row. The NEWID function is different. It generates a globally unique identifier (GUID).

I wrote a script to test this behavior:

The Random and Date columns were both only invoked once for the whole query - the data repeats for each row. The guid column was populated by the NEWID function and was invoked on each row. A new value was generated for each record.