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.
Add a comment
|
3 Answers
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()
4 Comments
Sorin Comanescu
Thanks @gbn, this seems to be working. A little question though, CHECKSUM is not guaranteed to produce always different results for different arguments but I guess the chances to enter such a case are really small?
gbn
1 in 4 billion (int range) uniqueness, subject to birthday problem. NEWID() would have the same problem because you need to get a number from the GUID.
Riley Major
I still get the error with an inline function: Msg 443, Level 16, State 1, Procedure //InlineFunctionName//, Line //LineNum// [Batch Start Line 0] Invalid use of a side-effecting operator 'Crypt_Gen_Random' within a function.
SQLServerSteve
I had the same problem as Riley. Surrounding the function with the CHECKSUM doesn't prevent the error.
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
1 Comment
SQLServerSteve
This is absolutely brilliant. It is so simple that I was skeptical it would work (especially after trying for years to get around many of Microsoft's stringent & artificial limitations on functions & stored procedures) but it did the trick for me in SQL Server 2014, unlike the marked answer.