Selecting a Function Value Shows nvarchar(4000) on an Index

Question

I have a view that I'm trying to setup an Index for. One of the select columns for the view executes a user-defined function that has a return value of varchar(250). However, when I try to setup an Index on that column, I see a size of nvarchar(4000). Why is that and will that cause a problem if I continue to setup my index?

Answer 1

i think your main suggestion here is going to be to ditch the scalar udf. they're an awful performance drag. is there a legitimate business reason for the udf?

pull the code out of the scalar function and place it inside of the view directly and see if that alleviates your problem.

Answer 2

Does running sp_refreshview 'YourViewName' resolve the issue? (I think you will need to change the view to use WITH SCHEMABINDING as well)