How to generate unique numbers inside a UDF (SQL Server)

Is there a way to generate unique numbers (int for instance) inside a开发者_如何学运维 user defined function?

rand() and newid() do not work due to side-effecting.

Thanks.

CRYPT_GEN_RANDOM gets around the NEWID() limitation

SELECT CHECKSUM(CRYPT_GEN_RANDOM(8000))

So you can use it like this. No "side effecting" error

CREATE FUNCTION dbo.test ()
RETURNS int
AS
BEGIN
   RETURN CHECKSUM(CRYPT_GEN_RANDOM(8000))
END
GO
SELECT dbo.test()

In DB2 I use the microsecond portion of the timestamp but SQL server only goes to milliseconds. I suspect that wouldn't be good enough?

You also could define a view using the function that causes the side-effect (in this case RAND())

CREATE VIEW randomView
AS
SELECT RAND() randomResult
GO

Then use the view in your UDF

DECLARE @random DECIMAL(18,18) 
SELECT @random = randomResult FROM randomView
RETURN @random