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

RussellNCook gravatar 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

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 1176 times

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