Something I've never quite understood is why RAND() is like it is - that is, contrary to what perhaps most people would want and expect: creating a (pseudo) random number each time or row it is called, analagous to NEWID().
If you need a random number to be generated once and then reused for every row there are explicit ways of doing this, say, with variables. But to get a random number generated on each call requires what look like hacks - creating seeds based on time or other table columns.
I expect the behaviour is a hangover from older language convention; Help says it mimics C. And it can't be changed; but would it be worth asking for RAND2(), with a sort of 'does what it says on the tin' promise?
Surely there are some developers at microsoft that could correct it or at least add an alternative as you say. It must be too low on the "fix" list.
Asking for a function to return a different value in each row seems a little strange. Do you really want to have something like
INSERT Pig (InsertTime, Principal, PigID, PigValue) SELECT GETDATE(), Principal, PigID, Value from PendingPig to call GETDATE() once for each of the potentionally 1000s of rows inserted? If not, why should RAND() be any different?
Adding an alternative function that behaves like NEWID() would make some things easier, perhaps, but how often is it needed? Far more useful (although it wouldn;t address that issue at all) would be a source of genuine random numbers to use when a pseudo-random sequence isn't good enough, but I guess that one has to wait for hardware to become standard.
That's interesting. I hadn't considered whether GETDATE() was evaluated per row or once for the whole statement. I think I assumed it was evaluated per row - like NEWID() and unlike RAND() (although I might have felt that RAND() was evaluated but returned a static value).
To turn your example round, I wouldn't feel comfortable writing
SELECT PigId, PigName, RAND() FROM ...
even though my intention was, "Generate one random number then use that for every row". The very name of the function is apt to mislead. And I don't know whether I'd trust it to keep the same value - there might be another rule I wasn't aware of. For clarity I'd probably rephrase it using a variable; it would make my intention clearer.
It's not a big deal of course, but the confusion it causes (hence this QOTD) seems as if it could have been easily averted at the (long past) design stage.
One use I had was to assign a random sort order value to a set of rows. I had to go through the usual hoops to make RAND() random.