July 28, 2008 at 9:47 am
Hi - I've been searching everywhere to find a solution for this problem -- not even sure if its really doable or not, but if anyone has any solutions it'd be appreciated.
I have a UDF, code provided below:
ALTER FUNCTION [dbo].[getRefererDomain]
(
@Referer varchar(8000)
)
RETURNS varchar(8000)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @RegExExtracted2 varchar(8000)
DECLARE @RetVal varchar(8000)
DECLARE @pos1 smallint
SET @RetVal = ''
IF (PATINDEX('http://%', @Referer) = 1) OR (PATINDEX('https://%', @Referer)) = 1
BEGIN
SET @RetVal= (dbo.RegExExtract(@Referer, 'mail\..*?(?=/)'))
IF @RetVal =''
BEGIN
SET @RegExExtracted2 = dbo.RegExExtract(@Referer, '[^http://].*[/]')
SET @pos1=charindex('/',@RegExExtracted2)
SET @RetVal = substring(@RegExExtracted2,0,@pos1)
END
END
ELSE
SET @RetVal = @Referer
return(@RetVal)
END
The intention behind the function is to analyze the referer variable and extract the domain name from it. Now, I realize that the function is using PATINDEX and CHARINDEX, which are non-deterministic built-in functions, and I've read up in several places that if a function is non-deterministic, then a computed column which uses the function cannot be made persisted.
My question is twofold - first, is there any way I can retain the essence of the function and still be able to persist the column? And second, if there isn't, then is there any way I can accomplish what I am trying to do in a manner that will be persistable?
I know that one solution would be to simply make the column an actual column, and add a trigger or change the stored procedure which updates the column to contain the code for adding the referer value; partly this is more of a theoretical question than a practical one, since it wouldn't be too difficult to do what I just mentioned. I do however want to know if it is doable, since it would be good to know.
April 19, 2011 at 8:57 am
I am having the same problem. Did you find a solution for this?
April 19, 2011 at 9:07 am
All built-in string functions are deterministic, including PATINDEX and CHARINDEX.
String Functions (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms181984(v=SQL.90).aspx
April 19, 2011 at 9:39 am
Instead of a computed column based on the UDF, can you use a trigger to simply capture changes to the source-column and store them in a non-computed column? Use the UDF in a trigger, in other words.
I'd have to see the sub-UDF's code to begin to suggest solutions that keep the UDF in a computed column.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 19, 2011 at 10:14 am
I had an Instead of trigger that has worked for years but we are in the process of moving to SQL 2008 and our Hibernate was not picking up the @@Identity after the insert. My attempt here was to avoid the trigger since that was apparently causing scoping issues with the @@Identity. It appears as though this may actually be an issue with the JDBC driver and SQL 2008 R2. Apparently there is a bug that is fixed in the latest jdbc driver that will fix our problem. There are other work arounds we can do if the trigger doesn't work. There is no way to make the function deterministic so I can't use it in a persisted computed column.
Like the Beatles said 'We can work it out' 🙂
April 19, 2011 at 10:47 am
Scope_Identity works better than @@Identity specifically because of triggers. Can you use that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 19, 2011 at 2:22 pm
Well... I think we already tried the scope_identity route (I know we talked about it), but we have determined that for some reason the 'SET NOCOUNT ON' was causing a rowcount issue in 2008 that it did not have in 2000. Anyway, I removed the nocount and now it works, but I am still having to do the 'select @@IDENTITY as id' before the return on the trigger. Maybe we can play with scope_identity and see if I can remove that.
Thanks for your help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply