T-SQL: Deterministic RAND(seed) in UDFs

[Eluros]

Greetings,

Okay, for you fellow SQL geeks:

SQL (or, at least, Microsoft/T-SQL) does not allow you to use rand() in a user-defined function, at least as of 2008 R2. It's non-deterministic, I get it, no problem.

Here's the thing, though: if I pass a seed, such as rand(123456), it's no longer non-deterministic! It'll always return the same value!

So, what's the deal? It sounds like a bug to me that, when you pass a seed into a rand() function in a UDF, you still get an "Invalid use of a side-effecting operator 'rand' within a function" error. Am I right that the error is arbitrary and a mistake, or am I missing something?

Thanks, everyone.

 

[Tweak155]

Why not just store the rand result in another variable then pass that?

 

[Eluros]

Why not just store the rand result in another variable then pass that?

Sure; there are several ways around it, I just think it's a silly problem in the first place. Doesn't it seem bizarre that a function that will always return the same value can't be called in a UDF? Just sounds arbitrary.

 

[Tweak155]

Sure; there are several ways around it, I just think it's a silly problem in the first place. Doesn't it seem bizarre that a function that will always return the same value can't be called in a UDF? Just sounds arbitrary.

You know that, I don't see why the compiler would without specifically setting a special case for RAND and having a literal parameter.

 

[Eluros]

You know that, I don't see why the compiler would without specifically setting a special case for RAND and having a literal parameter.

Well, this is certainly getting outside my prior knowledge (and part of why I'm asking-- hoping to learn some good stuff), it seems like it can tell the difference in other occasions.

http://msdn.microsoft.com/en-us/library/ms178091(v=sql.105).aspx

It says, including RAND, "The following functions are not always deterministic, but can be used in indexed views or indexes on computed columns when they are specified in a deterministic manner."

More specifically, "RAND is deterministic only when a seed parameter is specified."

So, MS says themselves that it's deterministic in the context we're talking about, and it can actually be used in situations (indexed views and indexes on computed columns) when you normally couldn't use a non-deterministic function. It seems to me that MS already makes accommodations for seeded RAND functions, and it'd be a logical next step to allow it in UDFs.

I'm sure the compiling process is substantially more complicated than I make it out to be-- I'm not going to pretend that I have any real idea about it-- so, perhaps it's more complicated for a UDF than it'd be for an index on a computed column/etc?

 

[Tweak155]

Well, this is certainly getting outside my prior knowledge (and part of why I'm asking-- hoping to learn some good stuff), it seems like it can tell the difference in other occasions.

http://msdn.microsoft.com/en-us/library/ms178091(v=sql.105).aspx

It says, including RAND, "The following functions are not always deterministic, but can be used in indexed views or indexes on computed columns when they are specified in a deterministic manner."

More specifically, "RAND is deterministic only when a seed parameter is specified."

So, MS says themselves that it's deterministic in the context we're talking about, and it can actually be used in situations (indexed views and indexes on computed columns) when you normally couldn't use a non-deterministic function. It seems to me that MS already makes accommodations for seeded RAND functions, and it'd be a logical next step to allow it in UDFs.

I'm sure the compiling process is substantially more complicated than I make it out to be-- I'm not going to pretend that I have any real idea about it-- so, perhaps it's more complicated for a UDF than it'd be for an index on a computed column/etc?

I don't think it's complicated, but it isn't as simple as checking for a parameter. For example, rand(rand()) would suit that instance - so then you'd have to determine if the parameter itself is deterministic.

I'm no pro on this either, it just makes sense that it assumes the rand function is non-deterministic.