5

I recently found out that SQL Server 2005 doesn't allow the use of newid() within user-defined functions. Why is this?

I have an alternate solution that suits my needs, so I'm not looking for ways to get around this. I'm curious why the designers would make this decision.

goric
  • 1,626
  • 3
  • 18
  • 25

3 Answers3

5

SQL Server only allows deterministic functions to be used within user-defined functions. Since the value of NEWID() is not deterministic, it cannot be used. You will find the same thing is true with GETDATE() and any other non-deterministic function.

I'm not qualified to answer why they would make that decision. It annoys me as well but I'm sure the SQL team at MS are not slouches.

EDIT: It turns out that my knowledge about GETDATE() is out-of-date. As the commenter says, you can use GETDATE() within functions from SQL Server 2005 onward. However, you still cannot use NEWID(), which I believe has to do with the same non-determinism constraint.

John Bledsoe
  • 181
  • 5
  • 3
    I believe it is down to the query planner needing some things to be reliably repeatable. Some optimisations that the QP uses are not "safe" (i.e. may result in inconsistent or just plain incorrect output) in some cases in the presence of non-deterministic functions. Presumably it is seen that denying the QP this optimisation options (or having it check each function before use) would be more of a problem (in terms of efficiency hits) than the determinism limitation. GETDATE is allowed now, but is only run once per query (so in the context of that one query, it returns a fixed value). – David Spillett Jan 28 '11 at 13:45
  • 1
    NEWID() is a non Deterministic function, which after 2005 is allowed in UDF but, as NEWID changes some of the internal counters and values inside the database structure it is considered to have a 'side effect' and as UDF's are not allowed to have side effects it is not allowed in the UDF, the same goes for RAND() as each call to RAND changes the seed value in the inner workings of MSSQL and this again is considered a side effect. GETDATE()/GETUTCDATE() and the allowed lists of functions have no side effects, so are allowed. HTH – Payload Aug 16 '12 at 08:11
  • @DavidSpillett: FYI you can get around the ban of newid() by reading from a view : CREATE VIEW vw_NewGuid AS SELECT NEWID() AS new_guid . Then in your UDF, you can do : SELECT new_guid FROM vw_NewGuid. – Dio Phung Jun 15 '16 at 18:09
  • @Dio: I would be very wary of workarounds that involve conning the engine that something is deterministic when it in fact isn't. You are either using a bug that may be fixed later (perhaps it should detect that the column in the view is not deterministic and they'll fix that in SQL Sever {Nextversion}) or relying upon "undefined behaviour" that similarly isn't guaranteed to work the same in later versions (or even in your current version if other circumstances change in the query). – David Spillett Jun 15 '16 at 23:23
  • This answer's first sentence is inaccurate. There is no prohibition against non-deterministic functions in user-defined functions. Test: CREATE FUNCTION TestNonDeterministic () RETURNS datetime AS BEGIN RETURN getdate(); END – Riley Major Jul 10 '17 at 17:59
1

Microsoft's NEWID() documentation does not explain, but I agree that the likely reason is related @Payload's comment on another answer:

NEWID() is a non Deterministic function, which after 2005 is allowed in UDF but, as NEWID changes some of the internal counters and values inside the database structure it is considered to have a 'side effect' and as UDF's are not allowed to have side effects it is not allowed in the UDF, the same goes for RAND() as each call to RAND changes the seed value in the inner workings of MSSQL and this again is considered a side effect. GETDATE()/GETUTCDATE() and the allowed lists of functions have no side effects, so are allowed.

For those willing to risk it, see @dio-phung's workaround suggested in another comment on that same answer:

@DavidSpillett: FYI you can get around the ban of newid() by reading from a view : CREATE VIEW vw_NewGuid AS SELECT NEWID() AS new_guid . Then in your UDF, you can do : SELECT new_guid FROM vw_NewGuid

Note @david-spillett's caution:

@Dio: I would be very wary of workarounds that involve conning the engine that something is deterministic when it in fact isn't. You are either using a bug that may be fixed later (perhaps it should detect that the column in the view is not deterministic and they'll fix that in SQL Sever {Nextversion}) or relying upon "undefined behaviour" that similarly isn't guaranteed to work the same in later versions (or even in your current version if other circumstances change in the query).

That workaround/trick is offered in several other answers.

Riley Major
  • 1,893
  • 1
  • 19
  • 33
0

I believe the answer is that NEWID() is a non-deterministic function - i.e. it has side effects. User defined functions are not allowed to have side effects.

Miles D
  • 965
  • 6
  • 7
  • 1
    Non-deterministic functions don't necessarily have side-effects. GETDATE() is a non-deterministic function (the same inputs don't result in the same outputs every execution) which has no side-effects. – Riley Major Jul 10 '17 at 18:00