Why is this not deterministic?

I'm using SQL 2008 (not R2). When I try to define the following UDF and try to use it in a computed column, I get the ever popular "cannot be persisted because the column is non-deterministic".

 set ansi_nulls on
 set quoted_identifiers on
 create function [dbo].[RemoveNonAlphanumeric]
 ( @input nvarchar(100)
 returns nvarchar(100)
     declare @i int, @result nvarchar(100), @pattern nvarchar(100);
     set @pattern = '%[^a-zA-Z0-9]%';
     set @result = @input;
     set @i = patindex( @pattern, @result);
     while @i > 0
         set @result = STUFF( @result, @i, 1, '' );
         set @i = patindex( @pattern, @result );
     return @result;

using SELECT OBJECTPROPERTYEX( object_id('dbo.RemoveNonAlphanumeric'), 'IsDeterministic' ) gives back zero. I've tried changing both ansi_nulls and quoted_identifier to OFF with no effect on the determinism of the function.

I've even replaced the body of the UDF with a simple return N''; and SQL Server STILL says it is non-deterministic.

I am simply not seeing what is non-deterministic. Any help would be apprectiated.

more ▼

asked Sep 20, 2011 at 07:31 AM in Default

avatar image

11 1 1 1

A bit of follow-up. A shortened version of the table this will be used with is the following.

create table t ( id int identity(1,1), firstname nvarchar(100) )

I'm then adding the computed column as:

alter table t add firstnamesearch as dbo.RemoveNonAlphanumeric( firstname ) persisted;

I get that the computed column fails with the "non-deterministic" error message since the UDF itself says it's not deterministic. Hence the focus on figuring out the issue with the UDF especially when it's hard-coded to return the same value regardless of what it's inputs are.

Sep 20, 2011 at 08:11 AM RussellNCook
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You need to add the with schemabinding option to the function

 create function [dbo].[RemoveNonAlphanumeric]
 ( @input nvarchar(100)
 returns nvarchar(100)
 with schemabinding

more ▼

answered Sep 20, 2011 at 08:26 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

That has solved the issue. I'd seen it in other posts but discounted it since the UDF access no tables. I suppose an algorithm to truly determine determinism wouldn't be the easiest to implement.

Sep 20, 2011 at 08:54 AM RussellNCook
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 20, 2011 at 07:31 AM

Seen: 2125 times

Last Updated: Sep 20, 2011 at 08:10 AM

Copyright 2018 Redgate Software. Privacy Policy