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
You just need to create it with schemabinding
.
SQL Server will then verify whether or not it meets the criteria to be considered as deterministic (which it does as it doesn't access any external tables or use non deterministic functions such as getdate()
).
You can verify that it worked with
SELECT OBJECTPROPERTY(OBJECT_ID('[dbo].[FullNameLastFirst]'), 'IsDeterministic')
Adding the schemabinding option to your original code works fine but a slightly simpler version would be.
CREATE FUNCTION [dbo].[FullNameLastFirst] (@IsPerson BIT,
@LastName NVARCHAR(100),
@FirstName NVARCHAR(100))
RETURNS NVARCHAR(201)
WITH SCHEMABINDING
AS
BEGIN
RETURN CASE
WHEN @IsPerson = 0
OR @FirstName = '' THEN @LastName
ELSE @LastName + ' ' + @FirstName
END
END