question

RussellNCook avatar image
RussellNCook asked

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 go set quoted_identifiers on go create function [dbo].[RemoveNonAlphanumeric] ( @input nvarchar(100) ) returns nvarchar(100) AS BEGIN 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 begin set @result = STUFF( @result, @i, 1, '' ); set @i = patindex( @pattern, @result ); end return @result; END 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.
udf
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
You need to add the `with schemabinding` option to the function create function [dbo].[RemoveNonAlphanumeric] ( @input nvarchar(100) ) returns nvarchar(100) with schemabinding AS .......
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.