Sql-server – PWDENCRYPT in user-defined function

functionsrandomsql servert-sql

In writing a random number function, I have learned that you can’t use NEWID() or RAND() inside a user function because they are non-deterministic.

I have also discovered that I can use PWDENCRYPT and get away with it. I would have thought that that was also non-deterministic. It certainly gives me a random-enough value for testing purposes (I’m not trying to simulate quantum physics here).

My questions:

  • is PWDENCRYPT really acceptable inside a user function (i.e., it works now, but can I expect it to keep working)?
  • is it random enough (for a reasonable definition of random)?

Best Answer

PWDENCRYPT is kind of deprecated, so no, you shouldn't be using it now, and you certainly shouldn't expect it to work indefinitely in any fashion - deterministic or not. From the documentation:

PWDENCRYPT is an older function and might not be supported in a future release of SQL Server. Use HASHBYTES instead. HASHBYTES provides more hashing algorithms.

I don't know that anybody can answer your second question. How do you define "random enough"?

Also, just to be clear, NEWID() and RAND() are disallowed in functions not because they are non-deterministic, but rather because they are considered "side effecting" - even though there is nothing you or I could assume about how these functions affect anything, other than incrementing some internal random number sequence generator.

And there are easy workarounds to that, anyway - e.g. create a view and have the function reference the view, pass NEWID() into the function, etc.

In addition to PWDENCRYPT(), other functions that are also obviously not deterministic (such as GETDATE(), SYSUTCDATETIME(), CURRENT_TIMESTAMP, and others) are allowed. Example:

CREATE OR ALTER FUNCTION dbo.what()
RETURNS int
AS
BEGIN
  RETURN (SELECT @@DBTS + @@CPU_BUSY + @@CONNECTIONS
    + DATEDIFF(SECOND, GETDATE(), SYSUTCDATETIME()));
END
GO

SELECT dbo.what();

The function will be marked as non-deterministic (which you can confirm with OBJECTPROPERTY()), which can limit how the UDF is in turn used, but it isn't the case that "non-deterministic functions are not allowed inside user-defined functions." Even though that's what a lot of people have written, it isn't strictly true.

In fact, RAND() is documented to be deterministic when a seed is provided; RAND(5); is demonstrably deterministic. But it still isn't allowed in a function (this returns the same side-effecting error):

CREATE FUNCTION dbo.who()
RETURNS decimal(20,19)
AS
BEGIN
  RETURN (SELECT RAND(5));
END
GO
Related Question