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?

| improve this question | | | | |
  • I've tried using newid() as a parameter to the function, but apart from the fact that it didn't like using a parameter for the order by clause, it wasn't a great solution as newid() is used many times not just once. – Hande Aug 11 '15 at 13:31
  • Can you share your procedure with us and what you have tried so far? Tips: it works if you hide it. – Julien Vavasseur Aug 11 '15 at 13:32
  • Here is the procedure. There's probably a much better way of doing this. – Hande Aug 11 '15 at 13:36
  • Do you want the "default" value to change every time you select that row from the table? – Solomon Rutzky Aug 11 '15 at 13:44
  • Sorry seems the procedure is too long to paste in the box! – Hande Aug 11 '15 at 13:48

You can use this code bellow and adapt it to your needs.

  • Sample table:

    Create Table list(id int identity(0, 1), name varchar(10))
    Insert into list(name) values('A'), ('B'),('C')
  • Create a view which only return a newid():

    Create View viewNewID As Select NEWID() as id
  • Then use it in you function and do whatever you want:

    Create function randomList()
    Returns int As
        Return (Select top 1 id From list Order By (Select id From viewNewId))
  • Sample query returning a random row:

    select * From List Where id = dbo.randomList() 
| improve this answer | | | | |
  • 1
    @Hande Please do not forget to test scenarios that insert multiple records as UDFs can have their return values cached, regardless of having a non-deterministic function buried somewhere inside of its definition. But if this answer does get you what you want, then please mark it as "accepted" (the check-mark next to the answer). – Solomon Rutzky Aug 11 '15 at 14:36

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.

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.