Douglas Duncan (4/11/2009)
The correct answer is no, and I would imagine that the people in power will correct the issue soon. In addition to the explanation given in the answer, the following which is taken directly from the MSDN page used as a reference states you cannot use RAND (in addition to several other built-in functions) in UDFs:Built-in functions that can return different data on each call are not allowed in user-defined functions. The built-in functions not allowed in user-defined functions are:
@@CONNECTIONS
@@CPU_BUSY
@@IDLE
@@IO_BUSY
@@MAX_CONNECTIONS
@@PACK_RECEIVED
@@PACK_SENT
@@PACKET_ERRORS
@@TIMETICKS
@@TOTAL_ERRORS
@@TOTAL_READ
@@TOTAL_WRITE
GETDATE
GetUTCDate
NEWID
RAND
TEXTPTR
Douglas, you are quite correct when you say that MSDN lists all of the above as built-in functions that cannot be used in a UDF because they are non-deterministic. However, you should note that in the heading of the article it also says Creating and Maintaining Databases (SQL Server 2000). Under SQL 2K8 (and presumably SQL2K5 as well, didn't try that) only two of those can not be used, namely RAND() and NEWID(). So, the limitation these days is really that one cannot use built-in functions that potentially may have side-effects.