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 (https://blog.sqlauthority.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????