Can a computed field be set to anything other that VARCHAR(MAX)?-CodePudding

I have a table with a field District which is VARCHAR(5)

When I create a computed field:

ALTER TABLE
  Postcode
ADD
 DistrictSort1
AS
  (dbo.fn_StripCharacters(District, '^A-Z'))
PERSISTED;

The computed field DistrictSort1 is added as NVARCHAR(MAX)

Is it possible to change the NVARCHAR to anything other than (MAX)?

Are there any performance issues?

CodePudding user response：

The obvious answer would be to CAST/CONVERT the value explicitly in your computed column:

ALTER TABLE dbo.Postcode
ADD DistrictSort1 AS CONVERT(varchar(5),(dbo.fn_StripCharacters(District, '^A-Z')) PERSISTED;

I would, however, suggest looking at your function fn_StripCharacters, which is currently set up to return an nvarchar(MAX). User defined functions, unlike those built into SQL Server, cannot return different data types based on their input parameter(s). As a result, whenever you reference your function, you will get an nvarchar(MAX) back.

As a result, sometimes it's best to have multiple similar versions of the same function. For one like this, form example, you might want 4, that return varchar and nvarchar values in non-MAX and MAX lengths.