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 = 'Difference 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
—————————————————————————————————-
Difference between Aug 1 2007 and Aug 23 2007 is 22
What Changed for the Non-deterministic Function in a UDF
What changed in SQL Server 2005 is that built in functions like GETDATE() became allowed inside a user defined function. In SQL Server 2000 they were blocked, which is why the older rule was so well known.
Not every such function is allowed, though. RAND() and NEWID() still fail inside a UDF with an error about a side-effecting operator. A common workaround is a small view that selects NEWID() or RAND(), and the function reads from that view.
Using GETDATE() in a UDF has a cost you should know about:
- The UDF itself becomes non-deterministic, so you cannot use it in an indexed view or in a computed column that you want to index or persist.
- You can confirm this with
OBJECTPROPERTY(OBJECT_ID('dbo.udf_DateDifference'), 'IsDeterministic'), which returns 0. - Results change from day to day, which makes testing harder. Passing today’s date in as a parameter makes the function easier to test and to reuse.
If you need a function that gives the same answer every time for the same input, keep GETDATE() out of it and pass the date in instead. Then create it WITH SCHEMABINDING, because SQL Server treats a user defined function as deterministic only when it is schema bound. After that, the IsDeterministic check should return 1.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
12 Comments. Leave new
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.”
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.
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.
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!
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
we can be work around
create VIEW vRand
AS
SELECT RAND() AS ‘number’
GO
This can be work around
create VIEW vRand
AS
SELECT RAND() AS ‘number’
GO
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.
Hi Pinal ,
How can lock my database if any user stole my mdf and ldf file,User not able to attach the file.
Thanks
Mayank…………….
It is not possible to copy those files when the database is live.
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.
Why we can not perform DML operation inside the funtion????