Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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.

share|improve this question

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
GETDATE is allowed from SQL Server 2005 onwards – gbn Jan 28 '11 at 13:41
As @gbn says, I'm able to use getdate() - create function dbo.date() returns table as return select getdate() 'dt' works just fine. – goric Jan 28 '11 at 13:44
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
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 8:11
up vote 1 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.