SQL SERVER – 2005 – Use of Non-deterministic Function in UDF – Find Day Difference Between Any Date and Today

While writing few articles about SQL Server DataTime I accidentally wrote User Defined Function (UDF), which I would have not wrote usually. Once I wrote this function, I did not find it very interesting and decided to discard it. However, I suddenly noticed use of Non-Deterministic function in the UDF.

I always thought that use of Non-Deterministic function is prohibited in UDF. I even wrote about it earlier SQL SERVER – User Defined Functions (UDF) Limitations. It seems like SQL Server 2005 either have removed this restriction or it is bug. I think I will not say this is bug but I will categorized it as feature.

GETDATE() is Non-Deterministic function and it can be used in User Defined Function in SQL Server 2005.

T-SQL Script to create UDF:
CREATE FUNCTION dbo.udf_DateDifference (@dtBeginDate datetime)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE
@rtMessage VARCHAR(100)
SET @rtMessage = 'Different between ' + CAST(@dtBeginDate AS VARCHAR(11)) + ' and ' +
CAST(GETDATE() AS VARCHAR(11)) + 'is' +
CAST(DATEDIFF(DAY,@dtBeginDate,GETDATE()) AS VARCHAR(11))
RETURN @rtMessage
END
GO

Execute above function running following script:
SELECT dbo.udf_DateDifference('8/1/2007') AS DayDifference
ResultSet:
DayDifference
—————————————————————————————————-
Different between Aug 1 2007 and Aug 23 2007 is 23

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

14 thoughts on “SQL SERVER – 2005 – Use of Non-deterministic Function in UDF – Find Day Difference Between Any Date and Today

  1. SQL Server 2005 has allowed some of the non-deterministic functions that were not allowed in 2000. In 2005 you can now use
    CURRENT_TIMESTAMP
    @@MAX_CONNECTIONS
    GET_TRANSMISSION_STATUS
    @@PACK_RECEIVED
    GETDATE
    @@PACK_SENT
    GETUTCDATE
    @@PACKET_ERRORS
    @@CONNECTIONS
    @@TIMETICKS
    @@CPU_BUSY
    @@TOTAL_ERRORS
    @@DBTS
    @@TOTAL_READ
    @@IDLE
    @@TOTAL_WRITE and
    @@IO_BUSY

    You still cannot use
    NEWID
    RAND
    NEWSEQUENTIALID and
    TEXTPTR

    I got that detail from SQL Server Magazine, and may have changed since publication, but the end result is – it is not a bug, you can use GETDATE() in functions,

    Here is more directly from the article

    “SQL Server 2005 Books Online (BOL) doesn’t provide a reason for this change or the distinction between the functions that are and aren’t allowed, but you’ll notice that the functions you can’t use have side effects. That is, the SQL Server 2005 non-deterministic functions that aren’t allowed make internal changes in the database or the system and leave a mark (such as a change to an interval value) that they have been invoked. And UDFs aren’t allowed to have side effects. For example, when SQL Server invokes the RAND function it changes an internal value that’s used to calculate the seed for the next invocation of the function. This means that had SQL Server allowed you to invoke RAND within a UDF, the invocation of the UDF would have changed the result of subsequent invocations of RAND outside the UDF as well. For this reason, you can’t use RAND in a UDF, even in SQL Server 2005.”

  2. So in other words, we’re not able to do things we should logically be able to do because of poor design in SQL Server’s internals? Yes, heaven forbid a UDF use Rand(), and make subsequent calls to Rand() non-deterministic…

    Someone as M$ is taking the whole “no side-effects” thing a bit too literally. At least this is a step in the right direction.

  3. Pingback: SQL SERVER - Function Property - Deterministic or Non-Deterministic Journey to SQL Authority with Pinal Dave

  4. how to change only day part with the day what we want in the date?
    for example,i need to compute the date(6-dec-08) which is having the day(’6′ should be changed) part from a table.

  5. This is a real pain. I wanted to use RAND() in a UDF I was putting together to generate random URL keys and hit a brick wall with this.

    I know there’s a few workarounds, such as having the RAND in a view, or just using a stored proc instead, but it’s a real pain. Just can’t understand the logic of banning the RAND function from within a UDF!

  6. Hi,
    Hi i want the result ‘Hai Buddy’. But the following query only returns Buddy… And where to use TextPtr and whats its use………

    Create Table summa (Samp Text)

    insert into summa values(‘Hai’)
    Insert into summa values(‘Buddy’)

    select TextPtr(Samp)from summa

    declare @txtpt Binary(16)
    Select @txtpt=TextPtr(Samp)from summa
    ReadText summa.Samp @txtpt 0 5

  7. I have created a UDF that is certainly deterministic. Yet I cannot use that UDF in the definition of a persisted computed column because SQL Server says my function is nondeterministic.

  8. CREATE TABLE ProductOrders
    (
    OrderId int NOT NULL PRIMARY KEY IDENTITY,
    ProductName nvarchar(50) NOT NULL,
    OrderDate datetime NOT NULL DEFAULT GETDATE()
    )

    beginers need this kind of examples only.

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s