views:

60

answers:

2

I have the following user-defined function:

create function [dbo].[FullNameLastFirst]
(
    @IsPerson bit,
    @LastName nvarchar(100),
    @FirstName nvarchar(100)
)
returns nvarchar(201)
as
begin
    declare @Result nvarchar(201)
    set @Result = (case when @IsPerson = 0 then @LastName else case when @FirstName = '' then @LastName else (@LastName + ' ' + @FirstName) end end)
    return @Result
end

I can't create an Index on a computed column using this function cause it's not deterministic. Someone could explain why is it not deterministic and eventually how to modify to make it deterministic? Thanks

+2  A: 

Create it with schemabinding

create function [dbo].[FullNameLastFirst]
(
    @IsPerson bit,
    @LastName nvarchar(100),
    @FirstName nvarchar(100)
)
returns nvarchar(201)
with schemabinding
as
begin
    declare @Result nvarchar(201)
    set @Result = (case when @IsPerson = 0 then @LastName else case when @FirstName = '' then @LastName else (@LastName + ' ' + @FirstName) end end)
    return @Result
end

go

select objectproperty(object_id('[dbo].[FullNameLastFirst]'), 'IsDeterministic')
Martin Smith
I created my Index pointing to the computed column. I have also a View that refers to my table. I think I have to specify SchemaBinding on the View too to create an Index on the same column. About this, if my base Table as an Index on the computed column, is redundant create another Index on the View ?
opaera
@opaera - Yes - You wouldn't need to index that column in the view as well.
Martin Smith
A final question, if you can. I have a sp that makes a query on the View (the View referencing my indexed table). I can/should specify the Index name into the query from clause? e.g. Select * From MyView (With MyTableIndex) ... Maybe I have some benefits doing that?
opaera
@opaera - No it should use the persisted column values automatically as long as either `ARITHABORT` or `ANSI_WARNINGS` are on. You can check the execution plan to determine this.
Martin Smith
Thanks very much.
opaera
+1  A: 

You need to declare the User Defined Function with SchemaBinding

create function [dbo].[FullNameLastFirst] 
( 
    @IsPerson bit, 
    @LastName nvarchar(100), 
    @FirstName nvarchar(100) 
) 
returns nvarchar(201) 
with schemabinding
as 
begin 
    declare @Result nvarchar(201) 
    set @Result = (case when @IsPerson = 0 then @LastName else case when @FirstName = '' then @LastName else (@LastName + ' ' + @FirstName) end end) 
    return @Result 
end 


create table Person
(
isperson bit,
lastname nvarchar(100),
firstname nvarchar(100),
fullname as [dbo].[FullNameLastFirst] (isperson, lastname, firstname)
)
go
insert into person(isperson, lastname, firstname) values (1,'Firstname', 'Surname')
go

create index ix1_person on person(fullname)
go

select fullname from Person with (index=ix1_person) where fullname = 'Firstname Surname'
go
nonnb