Random selection of a row within a function I am trying to write a function so it can be used as a default value for a column in a table. It exists as a procedure at the moment (which cannot be used as a column default) and uses ORDER BY newid() several times. Apparently this cannot be used in a function.

Is there anyway of randomly selecting a row in a user defined function?
A Computed Column is a bad (probably even very bad) place to have a random value.

First, if you want this "default" value to remain consistent upon the row being inserted (as opposed to changing every time the row and field are selected), you would need to use the PERSISTED keyword so that the computed column is physical stored and not recomputed each time.

Second, while you can usually do either of the following in a computed column, they cannot be used in a PERSISTED computed column due to being non-deterministic:


directly use NEWID() and CRYPT_GEN_RANDOM()
pass NEWID() or CRYPT_GEN_RANDOM() as a parameter to a User-Defined Function


Third, you could potentially create a SQLCLR function that would be able to call .NET randomization methods, but due to the requirement of a PERSISTED computed column needing to reference only deterministic functions, you would have to mark it incorrectly on purpose as IsDeterministic = true, and the effect(s) of this could be bad. At the very least it would allow SQL Server to cache the function output, in which case multiple rows being inserted in the same query (either via an INSERT...SELECT construct or an INSERT INTO VALUES (), (), ()... construct) could easily have the same "default" value and hence not be random at all.

Fourth, you could attempt the "hiding NEWID() in a View so it can be referenced in a UDF" trick as shown in @Julien's answer, but you would need to test that to see if it actually does what you are wanting. You need to particularly test scenarios that insert multiple rows in a single statement since SQL Server does not guarantee that it will run a UDF more than 1 time.

In the end, I think you are FAR better off having this "default" field being a regular field (i.e. not computed) and either determining its value in the Stored Procedure that does the INSERT, or determined and set in an AFTER INSERT Trigger that updates this "default" field for all new rows.