How do I specify the width of a computed varchar column?

Question

I want to create a persisted computed column in a table so that I can use it as part of a foreign-key relationship to another table.

I have these tables:

Events (EventID uniqueidentifier, EventCode varchar(8)) -- EventCode is a discriminator column

Parties (EventID uniqueidentifier)

... and I want to add an EventCode column to "Parties" so that the foreign key constraint can include both columns.

I've tried this:

ALTER TABLE Parties ADD EventCode AS 'PARTY' PERSISTED

But that adds the column as, I dunno, char(5) or something (since the column's type is inferred from the expression). So I can't relate it back to Events.EventCode because the two columns have different widths.

Is there a way to specify the width of a computed column, or will I have to do this (which seems ugly but I think will work)?

ALTER TABLE Parties ADD EventCode AS CAST('PARTY' AS varchar(8)) PERSISTED

Answer 1

You will need to cast the value to the size and datatype that you want. Putting a static string in will make SQL Server use CHAR as that is more cost effective than VARCHAR.

If you wanted to do it without the CAST in the defination then you would need to pad the value out to 8 characters 'PARTY ' and then cast it to VARCHAR when doing your joins (which would kill database performance).