Piyush,

I beg to differ. Try this:

CREATE FUNCTION [dbo].[usfn_GetDateTime] ( )

RETURNS datetime

AS

BEGIN

RETURN GETDATE ( )

END

GO

SELECT [dbo].[usfn_GetDateTime]()

Although GETDATE() is clearly non-deterministic this compiles properly and the SELECT statement returns the expected result. All that really happens is that your own UDF will be flagged as non-deterministic if it contains calls to other non-deterministic functions (be they built-in or another UDF) or extended stored procedures. This in turn puts some restrictions on where such a UDF can be used.

Regards,

Jan