Sql-server – Why can’t I use newid() in a user-defined table-valued function

sql-server-2005

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.

Best Answer

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.

Related Solutions

Sql-server – sp_help_jobsteplog fails when run as a @query of sp_send_dbmail

Did you try making the query 'EXEC msdb.dbo.sp_help_jobsteplog ''...'';' ? Chances are that internally there are other commands injected before the stored procedure is called, and if the procedure call is not the first in the batch, it must use EXEC or EXECUTE. I wish they would banish the ability to just say sp_anything without a preceding EXEC, it would make our lives far easier than the current savings of 5 keystrokes does.

SQL Server Job Permissions – How to Grant Permissions to Run a Job

You could create a stored procedure that runs the job. You can use with execute as owner to run the stored procedure as the database owner. That way, the users themselves don't need permissons on sp_start_job.

create procedure dbo.DoYourJob
with execute as owner
as
exec sp_start_job @job_name = 'YourJob'

Grant execute rights on DoYourJob to allow people to start the job.

Of course, you can also put the contents of the job in a procedure, and grant rights to run that. That would allow more interaction, like displaying a result text.

Best Answer

Related Solutions

Sql-server – sp_help_jobsteplog fails when run as a @query of sp_send_dbmail

SQL Server Job Permissions – How to Grant Permissions to Run a Job

Related Question